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!