March 19, 2012 at 7:00 am
We are running SQL Server 2005 SP3 on Windows 2003 Server. I noticed in the Restore Database Dialog under the Options Tab there is a box which displays the "Restore the database files as" Label and has an "Original File Name" Column and "Restore As" Column.
I have a database (TestDB) which was created on a production server with different directory names (for example, d:\sqldata\testfile.mdf, d:\sqldata\testlog.ldf). I want to restore this database (using same db name) on another server (test server). My question is, in the "Restore the database files as" box, do I need to change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf). Or, would this be necesary, if I select "Overwrite the existing database" from the "Restore Options" on the Options Tab.
I just want to be sure that this restore operation does not revert back to the production database (because the "Restore As" directories are pointing to the production server directory structure) and overwrite it. I don't think this would happen since I am performing the restore on a different server (test server). Plus, I think if I select "Overwrite the existing database" on the test server that the restore operation will use the directory structure (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf) of the selected database (TestDB) on the Test Server.
Please answer the above question and let me know if I am correct with the latter assumption. Just want to be sure.
Lastly, using the Restore Command would my concern be taken care of by (again) restoring to or overwritting an existing database:
--On Test Server but backup was create on Prod Server.
RESTORE DATABASE TestDB
FROM DISK = 'D:\SQL Backups\Full\TestDB.bak'
WITH FILE = 1, RECOVERY, REPLACE;
Would the REPLACE Option overwrite the existing TestDB database files (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf) on the test server and not the production server?
Thanks in advance, Kevin
March 19, 2012 at 7:10 am
You will need to use both the MOVE and REPLACE if you want the database files in the same location on the test server.
March 19, 2012 at 7:51 am
SSCOACH,
"You will need to use both the MOVE and REPLACE if you want the database files in the same location on the test server. "
My concern is I do not want the restore (on the test server) to overwrite the production database files because I do not change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf). I do not think this will happen (I am restoring to a different server). Please read my post again.
I just want to be sure that this restore operation does not revert back to the production database (because the "Restore As" directories are pointing to the production server directory structure) and overwrite it.
I am not concern about having the database files in the same location on the test server; I just do not want to restore a production database backup over top of the live production database (while trying to restore it to test server) because I do not change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf).
Thanks, Kevin
March 19, 2012 at 8:15 am
kevinsql7 (3/19/2012)
SSCOACH,"You will need to use both the MOVE and REPLACE if you want the database files in the same location on the test server. "
My concern is I do not want the restore (on the test server) to overwrite the production database files because I do not change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf). I do not think this will happen (I am restoring to a different server). Please read my post again.
I just want to be sure that this restore operation does not revert back to the production database (because the "Restore As" directories are pointing to the production server directory structure) and overwrite it.
I am not concern about having the database files in the same location on the test server; I just do not want to restore a production database backup over top of the live production database (while trying to restore it to test server) because I do not change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf).
Thanks, Kevin
I'm confused. Your test server and production server are the same?
If d:\sqldata is where your production data lives and f:\testsqldata is where your test data lives, then you need to use the MOVE clause if you are restoring data that originally lived d:\sqldata and you want it in d:\testsqldata. If the databasse you are restoring also already exists in d:\testsqldata, then you also need touse rhe REPLACE clause in the RESTORE statement.
March 19, 2012 at 9:04 am
SSCOACH<
I have two servers a test and production server. The backup was taken on the production server. The backup is being restored on the test server. (I really don't think the MOVE command is required for my question/situation.)
My question is, in the "Restore the database files as" box, do I need to change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf). Or, would this even be necesary, if I select "Overwrite the existing database" from the "Restore Options" on the Options Tab.
I just want to be sure that this restore operation (on test server) does not revert back to the production database on production server (because the "Restore As" directories are pointing to the production server directory structure) and overwrite it. I don't think this would happen since I am performing the restore on a different server (test server) and I select "Overwrite the existing database" which is equivalent to using the REPLACE option in a Restore Statement.
Thanks, Kevin
March 19, 2012 at 9:08 am
On your test server, do you have a directory f:\sqldata or is it only F:\testsqldata? If you don't have the f:\sqldata and you don't include the MOVE clause, the restore will look for the original directory from where the backup was taken.
This is one reason I ensured that my production, test, and development servers had identical file system structures. I didn't have to worry about including a MOVE clause when restoring a production backup to a test or development server.
March 19, 2012 at 9:10 am
Also, if you have separate servers for production and test, how does this even come into play:
I just do not want to restore a production database backup over top of the live production database (while trying to restore it to test server) ?
March 19, 2012 at 9:19 am
A database backup never looks at the instance. It doesn't know that it was taken on the "SalesDBServer" instance. It just knows that it was the "Sales" database, and it was located in d:\sqldata.
When you restore this on another instance, another physical server (say "MyTestServer") the restore by default goes to the same paths. So d:\sqldata. It doesn't matter if you overwrite the database, it doesn't matter what the default paths are on that server. If you don't have the same paths, you need to use the WITH MOVE command and specify the new paths.
March 19, 2012 at 10:15 am
Thanks for the responses. I still do not think I need the MOVE option for what I am trying to verify. Please read below to see if I am correct.
Steve, "When you restore this on another instance, another physical server (say "MyTestServer") the restore by default goes to the same paths."
It appears the restore goes to the physical path of the selected database to be restored on the test server. In my case, the TestDB. This is what I hope would happen. (Note: The physical paths are different on my test and production servers.)
SSCOACH, I want the production database to be restored to the directory paths specified in the TestDB on the test server. I do not want to change the path names. I just do not want the data to be accidently restored to the paths of the production database. Yes, this is probably impossible since I am working with two servers.
My question is, in the "Restore the database files as" box, do I need to change the "Restore As" directories (from the production server directory structure) to the directory structure on my test server (for example, f:\testsqldata\testfile.mdf, f:\testsqldata\testlog.ldf). Or, would this even be necesary, if I select "Overwrite the existing database" from the "Restore Options" on the Options Tab or use the REPLACE option in the Restore Command.
I think, based on my test below, the "Overwrite the existing database" from the "Restore Options" on the Options Tab or use of the REPLACE option in the Restore Command ensures that the directory paths (files) of the selected database are used. In my, case that would be the test database on the test server.
--Restore of a backup (taken on production server) to the test server.
RESTORE DATABASE cms
FROM DISK = 'D:\BBDB2 Prod Backups\Full\cms\cms_backup_201203160427.bak'
WITH FILE = 1,
REPLACE,
RECOVERY,
STATS=10;
GO
Here are the Results:
--On Production Server:
--Database Name: CMS
--Physical Files:
--R:\data\cms.mdf
--R:\log\cms_1.ldf
--On Test Server (after Restore):
--Database Name: CMS
--Physical Files:
--D:\mssql2005data\MSSQL\MSSQL.1\MSSQL\Data\cms_data.mdf
--D:\mssql2005data\MSSQL\MSSQL.1\MSSQL\Data\cms_log.ldf
So, it appeared to restore the data to the physical file names (D:\mssql2005data\MSSQL\MSSQL.1\MSSQL\Data) associated with the CMS Database on the Test Server. This is what I was hoping would happen. I did not want the restore operation to try to point to the physical files (R:\data and R:\log) of the CMS Database on the Production Server (this would have been almost impossible since I am working with two separate servers.
Thanks, Kevin
March 19, 2012 at 10:24 am
If the production database, MyDB, resides in F:\SQLData on the production server and you restore the backup of this database to a test server without using the MOVE clause, it will look for F:\SQLData on your test server.
If this directory on your test server is called F:\TESTSQLData and F:\SQLData does NOT exist on the test server, then to restore the production backup of MyDB to the test server you will need to include the MOVE clause. If you don't use the MOVE clause in this case the restore will fail.
March 19, 2012 at 10:28 am
I have not had a restore with replace put the data files in the original location of the database being restored. I have seen it delete the files from the original location and put the files where it thought I wanted them based on what was in the backup file.
Luckily the directory existed and was empty in this case.
Again, in my production, test, and dev environments I made sure they used the file system structures. This kept me from having to deal with different file system structures.
March 19, 2012 at 10:38 am
I don't have a 2005 instance handy, but usually the paths come out of the backup file, not the database you're restoring over. This usually means that you need to specify the WITH MOVE option.
However if you're concerned, why is it a big deal to specify this? It seems like you are somehow concerned about providing the paths in the restore command. Do it once, save it, put it in a job, and then be sure. Don't try to minimize the effort for something like a restore that could potentially cause problems.
If these are two separate servers, there is no way the restore on the test server affects the production server, unless you have the same paths mapped (same remote storage/LUNs) AND the production instance is not running (files are not open).
March 19, 2012 at 11:41 am
Lynn Pettis (3/19/2012)
If the production database, MyDB, resides in F:\SQLData on the production server and you restore the backup of this database to a test server without using the MOVE clause, it will look for F:\SQLData on your test server.If this directory on your test server is called F:\TESTSQLData and F:\SQLData does NOT exist on the test server, then to restore the production backup of MyDB to the test server you will need to include the MOVE clause. If you don't use the MOVE clause in this case the restore will fail.
SSCOACH,
Based on my testing (and previous example with the CMS Database), if the MYDB Database already exists on the Test Server and it's files are currenlty pointing to F:\TESTSQLData and in the Restore Statement you specify MyDB Database with the REPLACE option (on test server) then it would write overtop of the data and log files located in F:\TESTSQLData. (I don't believe it tries to create or write to F:\SQLData directories which were used on the production system.) I think the same wholes true for the Restore GUI using "Overwrite the existing database"; again with the already existing MyDB Database selected on the test server in the GUI.
Thanks, Kevin
March 19, 2012 at 1:12 pm
kevinsql7 (3/19/2012)
Lynn Pettis (3/19/2012)
If the production database, MyDB, resides in F:\SQLData on the production server and you restore the backup of this database to a test server without using the MOVE clause, it will look for F:\SQLData on your test server.If this directory on your test server is called F:\TESTSQLData and F:\SQLData does NOT exist on the test server, then to restore the production backup of MyDB to the test server you will need to include the MOVE clause. If you don't use the MOVE clause in this case the restore will fail.
SSCOACH,
Based on my testing (and previous example with the CMS Database), if the MYDB Database already exists on the Test Server and it's files are currenlty pointing to F:\TESTSQLData and in the Restore Statement you specify MyDB Database with the REPLACE option (on test server) then it would write overtop of the data and log files located in F:\TESTSQLData. (I don't believe it tries to create or write to F:\SQLData directories which were used on the production system.) I think the same wholes true for the Restore GUI using "Overwrite the existing database"; again with the already existing MyDB Database selected on the test server in the GUI.
Thanks, Kevin
You are correct and I am wrong. I guess all my experience with SQL Server doesn't matter. All I can go by is my experience. If what you see happening is different, good, go with it.
As far as I am concerned, if the directory structure between Production and Test are different I will continue to use the MOVE and REPLACE clauses together to ensure that when I restore a production database to a test server that it goes where I want it.
Edit: Just for the record, my name isn't SSCOACH.
March 20, 2012 at 9:41 am
Lynn,
Sorry for misrepresenting your name. My mistake. Thanks for your support. And I hope these examples work the same for you and they did me. It appears if the test database already exists and is selected (GUI) or specified (Restore Statement), then the test database's physical files (mdf and ldf) are overwritten unless you point to new physical files (location) using the MOVE Statement.
Kevin
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply