Log Shipping with SQL Server 2012.

SQL Server provides many ways for disaster recovery and log shipping is one of them.

You can configure log shipping in three topology.

1). Configure log shipping on one instance with different database name.

2). Configure log shipping on different instance in same server.

3). Configure log shipping on different server.

Log shipping engine just restore only transaction logs. the procedure is to restore one full backup of original database with no recovery and one transaction backup with also norecovery mode.

Please follow the below steps to configure Log shipping on your machine.

Step1 :  Creat sample database.

Use Master
Go
Create Database MyDatabase

Step2 : You must have your database full recovery mode.

Use Master
Go
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT

Step3 : Take one full backup and one transaction backup of your primary database.

Use Master
Go
Backup Database MyDatabase to Disk = 'D:\MyDatabase_Full.bak'
Backup log MyDatabase to Disk = 'D:\MyDatabase_Tran.trn'

Step4 : Restore full backup and then transactiosnal baackup.

Use Master
Go
RESTORE DATABASE [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Full.bak' WITH  FILE = 1,
MOVE N'MyDatabase' TO N'C:\SQL Server\Data\MyDatabase_LogShiiping.mdf',
MOVE N'MyDatabase_log' TO N'C:\SQL Server\Data\MyDatabase_LogShipping_log.ldf',
NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE Log [MyDatabase_LogShipping] FROM  DISK = N'D:\MyDatabase_Tran.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Step5 : Now right click on MyDatabase and click on property and go to “Option” pane.
Check on “Enable this as a priamary database in a log shipping configuration”
and click on “Backup Setting” button.

Step6 : Please create one sharing folder with full rights and that path in Network path to backup folder.

There is a default schedule of taking backup every 15 minutes, you can change it.

Click “OK” to complete transaction log backup setting window.

Step7 : Click on “ADD” button for Secondary databases in Log shipping property.

Click on connect to connect your log shipping server and database and move to Copy files tab.

Step8 : Create one folder to copy files from primary server with full permission.

Step9 : Please select your log shipping database where you want to restore your log on Secondary database.

There is two option to restore backup.

1).  No recovery mode – you can’t use log shipped database in this mode, but it gives better performance.

2) Standby mode – you can use log shipped database for only select statement, you can’t change data you can just read data. log shipping is stopped working when any user reading data from log shipping database, so this mode will slow down the performance.

Click “OK” button to complete your log shipping.

Step10 : To Recovery Log shipping database.

Use Master
Go
Restore database MyDatabase_logshipping with Recovery

now, log shipping database is ready to use.

Regards,

Nirav Gajjar