SQL Server Log Shipping

You can use log shipping to send transaction logs uninterrupted from one database (primary database) to another (secondary database). Continuously back up the transaction logs in the primary database, and then copy and restore them to the secondary database, which basically keeps the secondary database in sync with the primary database. The target server acts as a backup server, and the query processing can be reassigned from the primary server to one or more read-only secondary servers. Log shipping can be used with databases that use the full or bulk-logged recovery model.

Log shipping involves four jobs that are handled by a dedicated SQL Server agent job. These jobs include backup jobs, copy jobs, restore jobs, and alert jobs.

Backup Job: Create a backup job for each primary database on the primary server instance. It performs a backup operation, logs the history information to the local server and the monitoring server, and deletes the old backup file and history information.

Copy job: creates a replication job for each secondary server instance. This job copies the backup files from the primary server to the secondary server and records the history on the secondary server and the monitoring server.

Restore Job: Create a Restore job for each log shipping configuration on the secondary server instance. This job restores the copied backup files to the secondary database. It records the history information on the local server and the monitoring server, and deletes old and old history information.

Alert jobs: There are two scenarios for enabling alert jobs, one is to use alert jobs on separate servers, one that does not use an alert server, and that alerts jobs can be used separately in the primary and secondary libraries.

Basic institutions

Note: The database reply mode must be in full mode or bulk mode because log backups are required. Therefore, if you have other backup schedules, consider a full or differential backup, and do not log backups!

Also note that the proxy account has access to read and write files

Now configure log Shipping:

1. Right-click on the database that will enable log shipping, tap properties to open the database properties , check enable this database as the primary database in the log shipping configuration , that is, log shipping is enabled. The first configuration is backup, click Backup Settings.

2. Open the transaction log backup Settings , enter the backup folder, and the log backup will be backed up to that folder. Click Edit Job to set a backup schedule for the transaction date. In a compressed item, you can set the backup to compress . Confirm the backup settings After the configuration is complete.

3. Next, configure the secondary database, click Add in the secondary database option to add a secondary database, and you can add multiple secondary databases.

4. Connecting to the secondary DB instance and selecting the secondary database does not matter if the secondary database does not exist, and can be backed up to initialize the secondary database. In the Initialize secondary database option, select the first item "Generate Primary database backup and restore", and the restore option sets the storage directory for the data files and log files of the secondary database.

5. Click the Copy file option, which sets the destination folder for the copied files and the schedule for the copy job.

6. Click the Restore transaction log option to restore the backup, there are two state settings for the secondary database.

No Recovery mode: The secondary database is inaccessible and the database is in a restored state.

Standby mode: The secondary database is readable, but there are two situations when restoring, either forcing the user to disconnect while the database is being restored, or the database waiting for the user to fracture itself before restoring.

If you change the recovery model of an existing secondary database (for example, from "No recovery" mode to "standby" mode), the changes will not take effect until the next log backup is restored to the database.

7. After the configuration is complete, make sure to exit back to the Database properties box. If you enable the monitor, tick Use the monitor server instance and click Settings .

8. Turn on log shipping monitor settings, connect to the monitor server instance , and confirm the exit.

9. Confirm that log shipping is enabled and configured by exiting the database properties !

10. You can now see that the secondary database is created and is " restoring ..." Status, 4 related jobs are also created and running, and log shipping configuration is complete!

11. To view the current execution of log shipping, you can use the system to bring your own report to view. (right-click) Server Instance--reports--Flag report--transaction log shipping status

Basic information can be viewed using the table in msdb:

--Main Database select * FROM msdb.dbo.log_shipping_monitor_primary--secondary database select * FROM Msdb.dbo.log_shipping_primary_ Secondariesselect * from Msdb.dbo.log_shipping_secondaryselect * from Msdb.dbo.log_shipping_secondary_ Databasesselect * FROM msdb.dbo.log_shipping_monitor_secondary--log shipping alert Job Idselect * from Msdb.dbo.log_shipping_ Monitor_alertselect * from Msdb.dbo.sysjobs where job_id= '--Detailed operation history SELECT * FROM Msdb.dbo.log_shipping_monitor_history _detail--Verbose Error History select * FROM Msdb.dbo.log_shipping_monitor_error_detail

If you delete log shipping, uncheck enable this database as the primary database in a log shipping configuration！

Reference: Log Shipping

SQL Server Log Shipping