Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

MS SQL Server move restore automation

Created: 16 Aug 2012 • Updated: 06 Sep 2012 | 4 comments
This issue has been solved. See solution.

I am trying to do some scripting to automate move restores of old SQL server agent backups. Where I work we need to have the abillity to move old SQL server backups to a separate environment for compliance testing reasons.

Currently I've got a script which can list all the backups of a SQL Server and pull out their NBIMAGE names. I can also move individual databases with the GUI created bch scripts. The issue that I have is that even with the NBIMAGE name I can't work out the required MOVE statements for the bch script. Here is what I currently have:

OPERATION RESTORE

OBJECTTYPE DATABASE

RESTORETYPE MOVE

DATABASE "testdatabase"

MOVE "testdatabase_data"

TO "z:\moved\testdatabase.mdf"

MOVE "testdatabase_log"

TO "z:\moved\testdatabase.ldf"

NBIMAGE "imagename"

SQLHOST "hostname"

SQLINSTANCE "instancename"

NBSERVER "servername"

MAXTRANSFERSIZE 6

BLOCKSIZE 7

RESTOREOPTION REPLACE

RECOVEREDSTATE RECOVERED

NUMBUFS 2

ENDOPER TRUE

So the question is: How can I identify the data required for the "MOVE" statements? If it can be done by the GUI, when the GUI is creating a move template, it surely must be doable by the command line?

Please note: I'm a backup specialist, who knows a little bit about SQL Server, I should not be confused with a DBA!

Discussion Filed Under:

Comments 4 CommentsJump to latest comment

rookie11's picture

person \SQL dba will tell u where he wants db to place during the restore.

If it is the same a path its fine

as u mentioned DB goes to different then ask SQL DBA 

e.g if database restore on same pth then :

MOVE "testdatabase_data"

TO "z:\moved\testdatabase.mdf"

MOVE "testdatabase_log"

TO "z:\moved\testdatabase.ldf"

If different then :

MOVE "testdatabase_data"

TO "change whole path like D:\SQL DB\apps\testdatabase.mdf"

MOVE "testdatabase_log"

TO "D:\SQL DB\apps\testdatabase.ldf"
Fraser73's picture

Thanks for getting back to me... What I'm really after is how to identify the names of the database/logs and identify any filegroups which also need to be recovered.

I currently guess the database and log names from the database name obtained from the NBIMAGE. This also goes for the type of backup, but this doesn't mean that I know if there are more than one file containing the database or logs, I also don't know if the database name is "database_data" or just "database" etc. How do I find this out?

rookie11's picture

fraser73 ,, plz contact SQL DBA or person who raise request for restore to know DB name n details

u can also check DB names which were backedup in the policy, check GUI job logs, it contain DB names.

log on to server then to go C:\program files\veritas\netbackup\logs\userops\SQL\logs\ 

logs will contain DB names in a simple manner.

Fraser73's picture

Ok, what I've found out is that if you attempt a standard restore, and it fails the logs have the specific filenames (or database components, such as files, logs etc) in the log, these can be extracted and used to automate the restore, with a fair bit of effort mind...

SOLUTION