Log shipping

Log shipping is the process of automating the backup of a database and transaction log files on a primary (production) database server, and then restoring them onto a standby server. The primary purpose of log shipping is to increase database availability by maintaining a backup server that can replace production server quickly.

Although the actual flavor mechanism in log shipping is manual, this implementation is often chosen due to its low cost in human and server resources, and ease of implementation. As comparison, SQL server clusters enable automatic failover, but at the expense of much higher storage and license costs. Compared to database replication, log shipping does not provide as much in terms of reporting capabilities, but backs up also system tables along with data tables, and locks standby server from users' modifications.

Stored Procedure to Restore Database Backups

Here's the script I use for creating the "restore_database_backups" stored procedure:

CREATE PROCEDURE restore_database_backups AS 

RESTORE DATABASE database_name
FROM DISK = 'g:mssql7backupdatabase_namedatabase_name_backup_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:mssql7backupdatabase_nameundo_database_name.ldf',
MOVE 'logical_name' TO 'h:mssql7datadatabase_name.mdf', 
MOVE 'logical_name' TO 'f:mssql7logdatabase_name_log.ldf'

WAITFOR DELAY '00:00:05′

EXEC sp_dboption 'database_name', 'single user', true

 

Stored Procedure to Restore Log Backups

Wow, that was a lot, but here's more. Here's the script for creating the restore_log_backupsstored procedure. Notice how similar it is to the restore_database_backups stored procedure.

CREATE PROCEDURE restore_log_backups AS 

RESTORE LOG database_name
FROM DISK = 'g:mssql7backuplogdatabase_namedatabase_name_log_backup_device.bak'
WITH 
DBO_ONLY,
STANDBY = 'g:mssql7backuplogdatabase_nameundo_database_name.ldf'

WAITFOR DELAY '00:00:05′

EXEC sp_dboption 'database_name', 'single user', true

 

 Posted By—Mr. JOYDEEP DAS