SQL Server Log Shipping For Large Databases

Once the file gets copied onto the secondary server in the folder named LogShippingLogs, we will restore it with the name Test_DR, which means that it is a copy of the database named test.  Also, keep in mind that when the Full Backup of the database is restored, it should be kept in NORECOVERY mode, which means that additional copies of backups can be restored. In order to restore the database, we will use the below T-SQL.

RESTORE FILELISTONLY
FROM DISK='F:\LogShippingLogs\test.bak'

The above command will return the logical name of the data and log files of the database named test:

As shown in the above screen capture, the logical name of the data and log file of the database named test is test and test_log respectively.

RESTORE DATABASE TEST_DR
FROM DISK='F:\LogShippingLogs\Test.bak'
WITH
MOVE 'test' TO 'E:\Microsoft SQL Server\MSSQL\Data\test.mdf',
MOVE 'test_log' TO 'E:\Microsoft SQL Server\MSSQL\Log\test_log.ldf',
NORECOVERY,
STATS=10

Where E:\Microsoft SQL Server\MSSQL\Data and E:\Microsoft SQL Server\MSSQL\Log is the location where the data and log files of the database will reside.

Once the FULL backup of the database is restored, significant changes may have been committed to the database named test on the source server. We will therefore take the Differential Backup of the database named test on the source server using T-SQL as shown below:

BACKUP DATABASE test TO DISK=’ F:\LogShippingLogs\test_diff.bak’
WITH DIFFERENTIAL

After taking the differential backup, we will again RAR the copy of the differential backup and then transfer it to the folder named LogShippingLogs which is present on the F drive of the secondary server.

Once the differential backup gets copied onto the secondary server, we will restore it using the set of T-SQL commands as shown in the screen capture below:

RESTORE FILELISTONLY
FROM DISK='F:\LogShippingLogs\test_diff.bak'

This gives the logical name of the data and log file of the database named test.

RESTORE DATABASE TEST_DR
FROM DISK='F:\LogShippingLogs\Test_Diff.bak'
WITH
MOVE 'test' TO 'E:\Microsoft SQL Server\MSSQL\Data\test.mdf',
MOVE 'test_log' TO 'E:\Microsoft SQL Server\MSSQL\Log\test_log.ldf',
NORECOVERY,
STATS=10

After performing the above, we will then start setting up the LogShipping for the database named test. On the primary server, perform the following steps:

Right Click on the database named test and Select Properties->Transaction Log Shipping, please refer the screen capture below:

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 |