MS SQL Tivoli Migrations

I have used Tivoli Storage Manager mainly to backup Oracle databases running on AIX machines, but recently got involved in using it to help with the migration of SQL databases from Windows 2003 to Windows 2008 O/S. The standard Tivoli Data protection is setup as follows:

1. There is a desktop icon which launches the GUI:

2. The properties of the GUI is as follows:

3. The "Target" text string is shown below:

"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe" /tsmoptfile=F:\TSM\TDPSQL\tdp_server2.opt /sqlserver=Server2\Server2
4. Double clicking the icon launches the GUI:

5. The version we are running is:

To be able to migrate/clone the SQL databases to another instance/cluster, the following must be done:

1. Find the TDP .opt file on the source instance/cluster and copy it to the target instance/cluster i.e
Source:Server1 in Location E:\TSM\TDPSQL\tdp_server1.opt Target:Server2 copy to F:\TSM\TDPSQL\tdp_server1.opt
2. On the Target (Server2), make a copy of the desktop icon "TDPSQL" and rename it "TDPSQL-Mig" 3. Right click on the new icon "TDPSQL-Mig" and select properties 4. Highlight everything in the "Target" section and copy it to notepad 5. Edit this to reflect Source/Target settings i.e
"C:\Program Files\Tivoli\TSM\TDPSql\tdpsql.exe" /tsmoptfile=F:\TSM\TDPSQL\tdp_server1.opt /sqlserver=Server2\Server2
6. Save this and the double-click it to open it. Click the "Restore Databases" tab (it may prompt you for a password), expand the "SQL Servers" on the left and you should see all the Source:Server1 databases. 7. From here you can select a database from the list on the left, right-click it in the right hand pane and "Restore Into" or "Relocate" to change it's properties.

However, if you want it scripted so you don't have to do all this manual work, then follow these instructions:

1. On the Source:Server1 create a batch script (.bat) to backup all the databases:
@echo off set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql C: cd C:\Progra~1\Tivoli\TSM\TDPSql date < NUL >> E:\TSM\TDPSql\Logs\sqlsched.log time < NUL >> E:\TSM\TDPSql\Logs\sqlsched.log %sql_dir%\tdpsqlc backup * full /tsmoptfile=E:\TSM\TDPSQL\tdp_server1.opt /sqlserver=Server1\Server1 /logfile=E:\TSM\TDPSql\Logs\Server1_full_bat.log @echo on
2. Wait for the backups to complete, check the logfile for any errors. 3. Login into SSMS on the Source:Server1 and run the following SQL (referencing the files on Target:Server2 on F: drive):
select '%sql_dir%\tdpsqlc.exe restore '+name+' FULL /fromsqlserver=Server1\Server1 /mountwait=yes /into='+name+' /relocatedir=F:\Server2\mssql_datafiles\data,F:\Server2\mssql_logs\logs /tsmoptfile=F:\TSM\TDPSQL\tdp_server1.opt /tsmpassword=password /sqlserver=Server2\Server2 /configfile=F:\TSM\TDPSQL\tdpsql.cfg /replace >> F:\TSM\LOGS\server1_restore.log' from SYS.databases where database_id > 4 and state_desc = 'ONLINE' and is_in_standby=0 order by name asc
4. Copy the output from the above command and create a windows command script (.cmd) in folder F:\TSM\TDPSQL (or equivalent) on the Target:Server2 and add the following to the top:
@ECHO OFF set sql_dir=C:\Progra~1\Tivoli\TSM\TDPSql C: cd C:\Progra~1\Tivoli\TSM\TDPSql date < NUL >> F:\TSM\Logs\server1_restore.log time < NUL >> F:\TSM\Logs\server1_restore.log
5. Double-click the script and check the logs to check if the restore process is running. 6. You can also launch SSMS on the Target:Server2 and see the databases being added with the "..Restoring.." caption next to it.