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
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.