Actual combat SQL Server 2008 log shipping (log Shipping)

First, what is log shipping?

The principle is simple, three action six words: Copy------Restore with backup.

If a person completes this three action, it can only be called a log porter, and the SQL Server job automatically completes, called log shipping. The same thing, not the same grade, so the term is not the same.

Second, what problems can log shipping solve?

Resolves a multi-server hot backup issue for the database. Scheduled backups of multiple servers can be used as a substitute for the primary database server at any time.

Iii. What are the advantages of log shipping?

Simple! database replication, mirroring is much simpler than SQL Server.

Iv. What are the disadvantages of log shipping?

The transfer of log files only supports the way Windows file sharing, does not support FTP, just as the courier company sent only local, not sent to the field. All backup servers are in the same LAN, so the exact name should be called "Log local transfer".

V. How is the log delivered?

1. [Primary database server] The recovery mode of the database to transfer logs must be full

2. [Primary database server] 1 times and only 1 full backups of the database to transfer logs

BACKUP DATABASE [Cnblogsjob]  to DISK = N ' F:\FullBackup\CNBlogsJob.bak ' with Noformat, INIT,  NAME = N ' Cnblogsjob-full Database Backup ', SKIP, Norewind, Nounload, COMPRESSION,  STATS = 10GO

3. [Back up the database server] Copy the full backup file to the backup database server and restore it in a standby manner

RESTORE DATABASE [cnblogsjob] from  DISK = n ' F:\FullBackup\CNBlogsJob.bak ' with FILE = 1,  MOVE n ' cnblogsjob ' to n ' D : \data\cnblogsjob.mdf ',  MOVE n ' cnblogsjob_log ' to n ' E:\Logs\CNBlogsJob.LDF ',  STANDBY = N ' F:\Standby\ Rollback_undo_cnblogsjob.bak ', nounload, STATS = 10GO

After a successful recovery, the status shown in the SQL Server Administration tool appears:

4. [Back up the database server] Create a normal Windows user account (such as Logshipper), join the user to a group beginning with sqlserversqlagentuser (not verified to be non-additive), and set the user to SQL Server The running account of the agent.

5. [Back up the database server] Create a folder (such as F:\LogBackup) to hold log files copied from the primary database.

6. [Primary database server] Creates a Windows user account with the same password as the backup database server (for example, Logshipper).

7. [Primary database server] Creates a folder that holds log backup files and shares the folder with read-only permissions to Logshipper. Assuming the file name is Logbackup, the primary database server name is DBServer, and the backup database server has access to the shared path \\dbserver\LogBackup by setting up the share.

8. [Primary database server] Select the database to log shipping, open the Properties window, enter the transaction Log Shipping window, tick enable this as a primary db in a Log shipping config Uration, and then click Backup Settings to open the Log Shipping Backup Settings window, such as:

\\dbserver\LogBackup is the shared path that is accessed to the backup database server.

F:\LogBackup is the path to the primary database server where the log backup file is saved.

9. [Primary database server] Continue back to the Transaction Log Shipping window, in the Secondary databases section click the Add button, connect to the Backup database server, select the corresponding database; in initialize secondary Database window, select "No, the secondary database is initialized"; In the Copy Files window, enter the path to the backup log file (for example, F:\LogBackup) in the Back Up database server In the Restore Transaction log window, select Standby mode.

10. After a successful setup, log shipping begins to work. The job on the master data server periodically backs up the log of the current database at set intervals, saves the log file to the local folder (such as F:\LogBackup), and the job on the backup data server continues through the shared folder (for example, \\dbserver\ logbackup) Copy the log files from the primary database server to a local folder (such as F:\LogBackup), and then restore the log files to a database that is in the standby state.

Actual combat SQL Server 2008 log shipping (log Shipping)