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:
