cancel
Showing results for 
Search instead for 
Did you mean: 

MS SQL Server move restore automation

Fraser73
Level 3

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!

1 ACCEPTED SOLUTION

Accepted Solutions

Fraser73
Level 3

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

 

 

View solution in original post

4 REPLIES 4

rookie11
Moderator
Moderator
   VIP   

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
Level 3

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
Moderator
Moderator
   VIP   

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
Level 3

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