February 5, 2020 at 4:15 pm
Hello,
Sorry to ask you this noob question but I can not figure out by myself how to restore my DB to as a new DB.
I usually use Studio with no issue. I click on restore, select device and my BAK file and I give it a new name and the only option I deselect is the "Tail-log backup before restore". It works fine and do what I need.
After doing a bit of reading/research I wanted to run a script to do it :
I would like the script to take the file called Training_01.BAK and created a new DB called Training_99 with it... But it does not work and i get an eeror saying it cannot be overwritten.
Thanks for your help
RESTORE DATABASE Training_99 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Training_01.bak'
February 5, 2020 at 4:39 pm
That's because you haven't told it what to do with the file locations. If you don't specify new file locations, it will attempt to use the existing ones, and that's why you get the message about overwriting files. You need to put a MOVE clause for each file in your database into your RESTORE statement.
John
February 5, 2020 at 6:59 pm
Just to add...in case you needed to know what to move and wanted to keep doing this with scripts, you can do restore filelistonly to figure out what to put in the move clauses on the restore. So in your example:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\Training_01.bak'
So in the restore command, you just add the moves for the files you see in that command -
WITH MOVE 'LogicalFileName' to 'C:\WhereEverYourDataFileShouldGo.mdf',
MOVE 'LogicalFileName' to ....
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply