Wednesday, 22 September 2010

Setting up Transaction log shipping

We have a high performance physical sql server that we want to backup via netApp shares. To get this to work we setup a second virtual sql server that uses netapp shares which are backup using the Snap manager tool.

Data is sent to the virtual sql server using Transaction log shipping. Here is how to setup transaction log shipping

1. Create a Backup of the database making shure that:
   - Recovery model = Full
   - Backup type = Full
2. Restore this backup onto the virtual sql server choosing the second option:
   - Leave the database non-operational, and do not roll back transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)
3. After the backup is complete there is a green upward pointing arrow on the database on the virtual server and the database has (restoring..) after it.
4. Goto the physical server, right mouse click the database and select properties
5. In the select page choose "Transaction Log Shipping"
6. tick the check box "Enable this as primary database in a log shipping configuration"
7. Click the button Backup Settings
   This will open "Transaction Log Backup Settings" dialog
8. In the Network path to backup folder give the path on the physical server where the tranaction logs are to be found eg
   In the field "If the backup folder is located on the primary server, type the path to the folder"
9. In the Secondary server instances and databases press the Add button
10. Press the connect button and select the secondary database
11. In "Destination folder for copied files"
12. OK
13. Check that the backup and restore jobs created on both servers run successfully