locked
Does WITH MOVE during Restore effect the source database? RRS feed

  • Question

  • I am a bit new to this, so I'm not sure what's going on under the hood.

    I can no longer use SMO wizard to copy a database. Not sure if its the size/locks or what but its timingout. I've done some research on that, and it seems the best solution is just use the Backup/Restore to create a copy of my database.

    I have this file: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\VeteransNewsNetwork.bak

    I want to use it to restore a copy. Here is my Restore script:
    USE master
    RESTORE DATABASE [VeteransNewsNetwork_Dev001] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\VeteransNewsNetwork.bak'
    WITH FILE = 2,
    MOVE N'HTHProject' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VeteransNewsNetwork_Dev001.mdf',
    MOVE N'HTHProject_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VeteransNewsNetwork_Dev001_log.ldf',
    NOUNLOAD,
    STATS = 5

    Does WITH MOVE actually redirect the main database to point to these new files? Or are the MOVEs really just copies used with [VeteransNewsNetwork_Dev001]?

    The original file names are:
    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VNN.mdf'
    and
    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VNN_log.ldf'

    What exactly are these .mdf and .ldf files in the MSSQL\DATA folder? Is this the actual data in the online database? And if I fire this script, am I really "MOVE"ing the file and directing the online database to point to these new files, or am I doing what I hope I am doing and just creating new files for my restored copy of the database?

    Thanks for the insight!
    Monday, December 14, 2015 7:06 PM

Answers

All replies

  • Hi,

    Question - Why restore? -Hope you have a valid reason

    NOTE- Always take a backup before you perform a restore if you are un-sure of what you are doing as you can always go back to the original copy as long as you have backup.

    Your script looks fine to me and this replaces the existing database with the copy of the database using the backup that was taken at that time. Move option is actually used when you are trying to move mdf & ldf files to a different location or re-naming them.

    As the existing DB files path(C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA) looks similar to the restore location & presuming that you don't want to re-name your mdf & ldf files, You can use the script below to perform a restore

    RESTORE DATABASE [VeteransNewsNetwork_Dev001] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\VeteransNewsNetwork.bak' 
     WITH FILE = 2, 
     REPLACE, 
     NOUNLOAD, 
     STATS = 5
    Does WITH MOVE actually redirect the main database to point to these new files? Or are the MOVEs really just copies used with [VeteransNewsNetwork_Dev001]?

    Yes, The restored database will point to the new mdf & ldf files although these are just the copies of the database that were captured in the backup at that time the backup was performed.

    https://www.google.co.nz/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&sqi=2&ved=0ahUKEwip7rTXl9zJAhWIHpQKHbCPB9cQFggaMAA&url=https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms190447(v%3Dsql.105).aspx&usg=AFQjCNEKKQ9t9CPOw9nqBeVjL_IzHQeIfA&sig2=fQsDd3VTgb_Ovyln_8AQDQ&bvm=bv.109910813,d.dGo&cad=rja

    What exactly are these .mdf and .ldf files in the MSSQL\DATA folder? Is this the actual data in the online database?

    These are database files and are accessed by database for various operations performed on it. To get more information and to get better understanding, do some reading online.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f8333dd1-7162-45fb-afcd-f4b82f4f6ef1/mdf-and-ldf-files?forum=sqldatabaseengine

    https://www.google.co.nz/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0ahUKEwifjNe9mNzJAhVJj5QKHRJvA0QQFgghMAE&url=https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms189563.aspx&usg=AFQjCNEi3RcH27yYkofIs7EozVhdOU3-Ug&sig2=ZJsXj22zH5ig8gVmjK69ZA&bvm=bv.109910813,d.dGo&cad=rja

    And if I fire this script, am I really "MOVE"ing the file and directing the online database to point to these new files, or am I doing what I hope I am doing and just creating new files for my restored copy of the database?

    If you fire the script above - with the move option, You are telling SQL Server to replace your existing database using the backup file provided in the script to the location provided in the script with the file names specified. All this will be captured and saved in the metadata  tables of the master database which will then be used by the SQL Server to initialize the databases and bring them on-line upon SQL Server re-starts etc.

    As mentioned earlier SQL Server replaces your existing database with the copy of the database using the backup that was taken at that time.

    Also consider backing up the database security before your restore and apply back after the restore

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e718478-a43a-4831-b219-a30c5e002379/script-to-migrate-database-level-security-in-sql-server?forum=sqldatabaseengine

    Hope this helps

    Thanks

    Bhanu




    • Edited by bhanu_nz Monday, December 14, 2015 8:42 PM
    • Marked as answer by HTHP Tuesday, December 15, 2015 1:39 PM
    Monday, December 14, 2015 8:38 PM
  • You wrote: "As mentioned earlier SQL Server replaces your existing database with the copy of the database using the backup that was taken at that time."

    I DO NOT wish to replace the existing database. My wish is to make a COPY of it. The actual name of this database is [VeteransNewsNetwork]. I want to make a copy of [VeteransNewsNetwork] and call it [VeteransNewsNetwork_Dev001]. There is no actual [[VeteransNewsNetwork_Dev001] yet.

    Why RESTORE you ask? Because using SMO wizard to copy the database is give me a Timeout error. I've looked around, and see that many people suggest using Backup/Restore to make a copy of the database.

    So, if my script "replaces your existing database", how can I instead use this restore method to create a COPY of my database?
    Monday, December 14, 2015 8:58 PM
  • Hi,

    If you just want a copy of your database(VeteransNewsNetwork) on the same instance of SQL Server with a different name([VeteransNewsNetwork_Dev001]).

    Take a backup of your existing database

    Use the script that you have provided in your question to create a new database [VeteransNewsNetwork_Dev001]

    RESTORE DATABASE [VeteransNewsNetwork_Dev001] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\VeteransNewsNetwork.bak' 
     WITH FILE = 2, 
     MOVE N'HTHProject' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VeteransNewsNetwork_Dev001.mdf', 
     MOVE N'HTHProject_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VeteransNewsNetwork_Dev001_log.ldf', 
     NOUNLOAD, 
     STATS = 5
    

    This process doesn't interfere with the existing database VeteransNewsNetwork and its files

    Hope this helps

    Thanks

    Bhanu

    Monday, December 14, 2015 10:27 PM