Commandline Alternate SQL Server legacy Restore

Answer

It is possible to restore an SQL Server database to an alternate SQL server that has a different physical setup than the SQL Server which that database was backed up from. This type of alternate restore that has a different location on the hard drive will use the RELOCATE/TO parameters. It is also possible to use the restore INTO parameter if the name of the database needs to be changed.

• The RELOCATE is used to determine what file on the hard drive will be used to hold the data. This will need to be changed to have a different name and/or location to be something different (test) than the production database.

• The RESTORE INTO is used by the SQL Server to identify the database. This may need to be changed to a new name so that the SQL Server will be able to differentiate between the production database and this second (test) copy of the database.

The following steps can be utilized to perform such a restore.

ENVIRONMENT
SQL SERVER 1 (backup server)
Servername: TSMCRASH
SQL Data Location: d:\MSSQL\DATA\
Tivoli Storage Manager Nodename: crash
Tivoli Storage Manager Node's password: pwd

SQL SERVER 2 (alternate restore server)
Servername: TSMBURN
SQL Data Location: g:\MSSQL\DATA\
Tivoli Storage Manager Nodename: burn
Tivoli Storage Manager Node's password: xyz

1. Query the Tivoli Storage Manager Server from the alternate restore server for the logical file information. For example, using the default Database of Northwind, issue the following command to query the backup server (fromsqlserver) based on the environment described above:

tdpsqlc query TSM Northwind FULL /fileinfo /fromsqlserver=TSMCRASH /tsmnode=crash /tsmpassword=pwd /mountwait=yes

Backup Object Information:

SQL Server Name ........................ TSMCRASH
SQL Database Name ........................ Northwind
Backup Object Type ....................... Full
Backup Object State ...................... Active
Backup Creation Date / Time .............. 11/20/2007 14:08:00
Backup Size .............................. 3,039,744
Database Object Name ..................... 20071120140800\00000340
Number of stripes in backup object ....... 1

SQL Group Logical Name ................... PRIMARY
SQL Group Space Allocated ................ 3,080,192
SQL Group Space Used ..................... 3,080,192
SQL File Logical Name ................... Northwind <--- LOGICAL NAME
SQL File Physical Name .................. d:\MSSQL\DATA\northwnd.mdf
SQL File Space Allocated ................ 3,080,192
SQL File Space Used ..................... 3,080,192

SQL Group Logical Name ............... TRANSACTION LOG
SQL Group Space Allocated ............ 1,048,576
SQL Group Space Used ................. 403,456
SQL File Logical Name ............... Northwind_log <--- LOGICAL NAME
SQL File Physical Name .............. d:\MSSQL\DATA\northwnd.ldf
SQL File Space Allocated ............ 1,048,576

2. After the logical file information has been determined, then proceed with a restore to the alternate server and new file location. To do so, it is necessary to use the /fromsqlserver, /sqlserver, /relocate, and /to parameters:

tdpsqlc restore Northwind FULL /relocate=Northwind,Northwind_log /to=g:\MSSQL\DATA\northwnd.mdf,g:\MSSQL\DATA\northwnd.ldf /fromsqlserver=TSMCRASH /sqlserver=TSMBURN /tsmnode=crash /tsmpassword=pwd /mountwait=yes

Note that the Data Protection for SQL also provides the /RELOCATEDIR parameter which can alternatively be used in place of the /Relocate and /To options. For the example above, the restore command to relocate the restored database/files to the g: drive would be:

tdpsqlc restore Northwind FULL /RELOCATEDIR=g:\MSSQL\DATA\ /fromsqlserver=TSMCRASH /sqlserver=TSMBURN /tsmnode=crash /tsmpassword=pwd /mountwait=yes