SQL Server Log Shipping For Large Databases

Enable the check box Enable this as a primary database in a log shipping configuration  as shown in the screen capture below:

Click on Backup Settings and a new window appears as shown in the screen capture below:

In the text box named Network path to backup folder as well as the text box  named If the backup folder is located on the primary server, type a local path to the folder, type the location where the transactional log file will reside on the server. It should be in the form \\servername\foldername. In our case, it will be \\INTEL\LogShippingLogs where INTEL is the server name whereas LogShippingLogs is the name of the shared folder on the F drive on the source machine which will hold the copies of Transactional Log backups.

You can specify the retention period of the Transactional Log files as 24 hours, I normally prefer keeping it for 24 hours. Append the name of the Backup Job with _DR which means we can clearly distinguish that this job will take the Transactional Log backup of the database named test which is involved in Log Shipping. You can change the Backup schedule as per your wish, the default is 15 minutes and I myself kept the frequency as 15 minutes, please refer the screen capture below:

Click the Add button to add the Secondary server instance and databases please refer the screen capture below:

In the Secondary Server Instance text box, type the name of the Secondary Server Instance, it will be INTELabc; the name of the Secondary database will be test_dr.

Under the Initialize Secondary Database tab, Select No, the secondary database is initialized tab. Under the Copy Files tab, In the Destination Folder for copied files text box, enter INTELabc\LogShippingLogs.
Specify the retention period of copied files as 24 hours and Append the name of the copy Job with _DR which means we can clearly distinguish that this job will copy the Transactional Log backup of the database named test which is involved in Log Shipping from the server named INTEL to the server named INTELabc, please refer the screen capture below:

Under Restore Transaction Log tab, Select the No Recovery Mode radio button and also Append the name of the restore Job with _DR .

You can specify the source server itself to act as a Monitor Server instance, please refer to the screen capture below:

Click on the OK button as shown in the screen capture below:

A screen appears as shown  below which indicates that everything has been completed successfully.

That’s it, we have successfully implemented performing a Log Shipping on a database of size  125 GB. Please send reply with any suggestions or enhancements you may have in the comments.

Pages: 1 2 3




Related Articles :

9 Responses to “SQL Server Log Shipping For Large Databases”

  1. Nice article

  2. Very good article. Can you please elaborate on how to monitor log shipping, especially the way the synchronization works. How can one knows its synchronize/ up to date with the primary server

    Thank you.

  3. hi,

    Its a very nice article. But why while adding secondary server screenshots are not given for ‘Initialize Secondary Database’ and adding secondary server by using ‘Add’ in detail.

  4. In the start of your article you mention that using the GUI is not a good option. Yet throughout the procedure you use the GUI.
    Can you clarify this a bit further, where did you NOT use the GUI?

  5. You might also want to ensure that Backup compression is being used, since the first backup and logs will be shipped across the network

  6. Hi,
    Thanks for providing a comprehensive guide. I would like to mention that in SQL Server 2008 R2 Standard and Enterprise, you can use compressed backups as the native SQL Server backup option, thus eliminating the extra RAR step. Log Shipping under these versions can also utilize backup compression, so your time to backup and restore and time to transfer the backup is shorter. It is well worth investigating and is a great time, disk space, and bandwidth saver. Thanks again.

    Jan S./SEAQL

  7. This goes back to the point I made. I can’t quite see how I would treat Log Shipping for a ‘small’ database any differently.
    Nice article but seems to have missed the point.

  8. Gud article you given step by step process could you please provide log shipping errors and solutions .information

  9. Nice .you given step by step process could you please provide log shipping errors and solutions .information

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |