July 3, 2012 at 3:07 am
hi all,
we need to set up Transaction Log Shipping on a production server. The issue we are facing upfront is that it will be virtually impossible to make a full backup of the database and then restore this backup over the network to the slave server. The backup is big, the slave server is in a different physical location and we will never get the backup copied over the network in a reasonable time.
Our idea is the following:
- we make a full backup of the production database
- we keep the production database active with transaction log backup running
- the backup is physically transported to the site where the slave server is installed
- the backup is restored on the slave server
- any transactions by the users on the production database are stopped and a final transaction log backup is made
- the transaction logs created after the full backup are restored on the slave server
- the 2 databases should then be "in synch" and transaction log shipping is enabled.
Is the above scenario working? Do any of you have experience with this or have other suggestions? We cannot leave the production database off-line for too long so we try to come up with an alternative.
thanks for any feedback
Ronald
July 4, 2012 at 4:17 pm
You could try the following:
- Take a full backup of the database close to the time you want to setup log shipping
- Move the backup to the secondary site
- Restore the backup to the secondary site leaving the database in recovery mode
On the day you want to setup log shipping:
- Take a differential backup of the primary database
- Copy the primary database to the secondary site
- Restore the differential backup to the secondary database leaving the database in recovery mode
- Setup jobs for log shipping LSBackup/LSCopy/LSRestore/LSAlert jobs on primary, secondary and monitor (if monitor server is going to be used) servers
- Review the log shipping jobs/stored procedures/built in reports to verify that backup/restores are occurring
Joie Andrew
"Since 1982"
July 5, 2012 at 4:21 pm
ronald.dirkx (7/3/2012)
hi all,we need to set up Transaction Log Shipping on a production server. The issue we are facing upfront is that it will be virtually impossible to make a full backup of the database and then restore this backup over the network to the slave server. The backup is big, the slave server is in a different physical location and we will never get the backup copied over the network in a reasonable time.
Our idea is the following:
- we make a full backup of the production database
- we keep the production database active with transaction log backup running
- the backup is physically transported to the site where the slave server is installed
- the backup is restored on the slave server
- any transactions by the users on the production database are stopped and a final transaction log backup is made
- the transaction logs created after the full backup are restored on the slave server
- the 2 databases should then be "in synch" and transaction log shipping is enabled.
Is the above scenario working? Do any of you have experience with this or have other suggestions? We cannot leave the production database off-line for too long so we try to come up with an alternative.
thanks for any feedback
Ronald
This is known as the "sneaker-net" approach, and is not uncommon for VLDB or companies with limited/expensive network bandwidth.
One correction though - you don't have to stop activity on the primary server to complete things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 6, 2012 at 1:40 am
thanks to both of you for the reply.
I need to check the possibility of the incremental backup with the infrastructure team but it is certainly an easier way.
We do indeed need to "sneak" because we have a VLDB and a limited timeframe. Bandwidth is ok but the DB is simply to big to copy over the WAN.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply