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?
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