question

ecomma avatar image
ecomma asked

Restoring a database to a new location in sqlserver 2008

I do a monthly full backup and daily backup of transactional log for my central db to the hard disk of my central server. Due bandwith constraint.I then copy the backup and the logs from the C to a network drive and take it the headquarters.There i have another server where i restore the db to.I first copy the db from network drive to the C, Then start the restoration using management studio.But sometime due to the work load, i often skip some log,this makes the task very irritating.Now i want to write a script to do the process. I Change the default backup location on the instance by rightclicking on the instance name,then facet and change the location to the new one:C:\SQL_BACKUP_FULL. I The create my script as follow: RESTORE DATABASE IDKit_TEST FROM DISK = 'C:\SQL_BACKUP_FULL\IDKit_backup_2012_09_14_140003_7416079.bak' WITH NORECOVERY; RESTORE LOG IDKit FROM DISK= 'C:\SQL_BACKUP_FULL\IDKit_backup_2012_09_17_163003_8120943.trn' WITH NORECOVERY; RESTORE LOG IDKit From DISK='C:\SQL_BACKUP_FULL\IDKit_backup_2012_09_18_163008_0343345.trn' WITH RECOVERY; But i receive the follwing error: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\IDKit.mdf" failed with the operating system error 3(The system cannot find the path specified.). Msg 3156, Level 16, State 3, Line 2 File 'IDKit' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\IDKit.mdf'. Use WITH MOVE to identify a valid location for the file. Please help, what am i missing?
sql-server-2008restore
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tim avatar image
Tim answered
The path it is saying it can't find is most likely the original path on your centralized server. You can modify your script for the restore of the full database to specify WITH MOVE and list the location to place your data and log file. Example RESTORE DATABASE Test FROM DISK = 'C:\\Test_db.BAK' WITH MOVE 'Test_dat' TO 'C:\\MSSQL\\DATA\\Test_Data.MDF', MOVE 'Test_log' TO 'C:\\MSSQL\\DATA\\Test_Log.LDF', NORECOVERY, REPLACE
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@johnm, I was updating my answer when your comment came in. I wanted to include an example but had to take a call midway into my script.
0 Likes 0 ·
@TimRadney no worries. I saw that you added an example so didn't need mine mucking up the waters. ;-)
0 Likes 0 ·
@TimRadney good sample.Thanks
0 Likes 0 ·
@ecomma - please mark @Tim's answer as correct - you should see a tick next to his response. Click on that. That'll show that your problem has been addressed, give Tim the credit for it, and give you a little boost too. It'll also help anyone else who has the problem in the future who comes across this question...
0 Likes 0 ·
@ThomasRuston, Thanks. I was just trying to make it work for me and then give mark his answer.Thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.