Forum Discussion

Fraser73's avatar
Fraser73
Level 3
13 years ago
Solved

MS SQL Server move restore automation

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!

Agents
Backup and Recovery
NetBackup
Recovering
Restore
  • Fraser73's avatar
    Fraser73
    13 years ago

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

     

     

4 Replies

  • rookie11's avatar
    rookie11
    Moderator
    13 years ago

    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 avatar
    Fraser73
    Level 3
    13 years ago

    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 avatar
    rookie11
    Moderator
    13 years ago

    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 avatar
    Fraser73
    Level 3
    13 years ago

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