Implement real-time backup of SQL Server

From: http://topic.csdn.net/t/20040430/11/3024328.html How to create, maintain, and connect a backup server - SQL)
The establishment of the backup server usually includes creating database backup and regular transaction log backup on the master server, and then applying the backup to the backup server in order. The backup server remains read-only between restoration operations. When a backup server is required, any unfinished transaction log backup, including the active transaction log backup on the independent server, will be applied to the backup server, and the database must be restored.

Create backup on the master server

Run Backup Database Statement to create a database backup.


Run Backup Log Statement to create a transaction log backup.


Repeat the steps for each transaction log that you want to create over time 2 .
Create and maintain backup servers

Execute Restore Database Statement to restore steps 1 Back up the database created on the master server. Specify the name of the Undo file that contains the data page before rolling back the data page affected by the uncommitted transaction.


Execute Restore Log Procedure 2 Each transaction log created on the master server.


Repeat the steps for each transaction log created on the master server 2 .
Bring the backup server online (primary server failure)

Execute Backup Log Statement to back up the current active transaction log. When the backup server is online, this is the latest transaction log backup applied to the backup server. For more information, see how to create a backup of the active transaction log.


Execute Restore Log Statement to back up all transaction logs, including steps 1 The created active transaction log backup that has not been applied to the backup server.


Run Restore Database With The recovery statement to restore the database and bring the standby server online.
Example
In the following example, install the mynwind database on the backup server. You can use the database in read-only mode between restoration operations.

-- Restore the initial database backup on the standby server.
Use Master
Go
Restore Database Mynwind
From Mynwind_1
With Standby = ' C: \ undo. LDF '
Go
-- Apply the first transaction log backup.
Restore Log Mynwind
From Mynwind_log1
With Standby = ' C: \ undo. LDF '
Go
-- Apply the next transaction log backup.
Restore Log Mynwind
From Mynwind_log2
With Standby = ' C: \ undo. LDF '
Go
-- Repeat for each transaction log backup created on
-- Primary server.
--
-- Time elapses ......
--
-- The primary server has failed. Back up
-- Active transaction log on the primary server.
Backup Log Mynwind
To Mynwind_log3
With No_truncate
Go
-- Apply the final (active) Transaction Log backup
-- To the standby server. All preceding transaction
-- Log backups must have been already applied.
Restore Log Mynwind
From Mynwind_log3
With Standby = ' C: \ undo. LDF '
Go
-- Recover the database on the standby server,
-- Making it available for normal operations.
Restore Database Mynwind
With Recovery
Go


Parameters

Or use: log Transmission

In Microsoft & Reg; SQL Server & # 8482 ; 2000 In the Enterprise Edition, you can use Log shipping to continuously provide transaction logs from one database to another. The transaction logs are continuously backed up from the source database, and then copied and restored to the target database to keep the destination database synchronized with the source database. This allows you to have a backup server, which provides a way to detach query processing from the master computer (source server) to a read-only target server. SQL Server must be installed on all servers used for log transmission. 2000 Enterprise Edition.

Use the database maintenance plan Wizard to configure log Shipping
To easily configure log shipper, use the database maintenance plan wizard. With this wizard, you can:

Defines the frequency of log generation, the time between backup and restoration operations, and when the target server and the source server are asynchronous.


Register any new server.


Create a source database on all target servers. When you add a target database through the database maintenance plan wizard, you can choose to create a database on the target server or use an existing database. Before configuring a database for log shipping, any existing database must be in standby mode.


Specifies which target server can use the source server role.


Set restoration latency. The restoration delay defines the time that must be waited before the transaction log is restored. If the source server fails, this delay will provide additional time before the corrupted logs are restored to the target server.


Create scheduling to set backup scheduling.
Before using the database maintenance plan wizard, consider the following:

The user who configures log transmission must be a member of the SysAdmin server role so that the user has the right to modify the database for log transmission.


You can only configure log shipper on one database at a time. If more than one database is selected, the log shipper option on the Wizard is disabled.


Logons used to start the MSSQLServer and SQLServerAgent services must be able to access log transfer plan jobs, source servers, and target servers.


When you use the database maintenance plan Wizard to configure log shipping, logs can only be transferred to the disk. The backup to tape option is unavailable.
Manually configure log Shipper
If you are running SQL Server 7.0 Service Pack 2 SQL Server 2000 Supports manual transfer of logs from SP2 transaction logs.

To enable this option, perform the followingCode:

Exec Sp_dboption ' Database Name ' , ' Pending upgrade ' , ' True '

However, when you restore the database after the log is sent, you can only use the norecovery option to restore the database.



Description 2000 You cannot use SQL Server to copy logs when manually configuring log transfer between computers of an instance.


For more information, see the sp2 documentation.

Configure log Shipper

Enterprise Manager

How to configure log shipper (Enterprise Manager)
Add information - SQL Server 2000 SP3.

Use the database maintenance plan Wizard to configure log Shipping



Before configuring log shipper, you must create a share on the primary database to make transaction logs available. This is the sharing created from the transaction log dump directory. For example, if you want to dump logs to the directory e: \ data \ tlogs \, you can create \ logshipping \ tlogs sharing from this directory.

On the "select database" screen, select the "following Database" check box and select the database for log transfer.
If multiple databases are selected, log transfer is not performed and the log transfer option is not available. You cannot select a database with log transmission configured.

Select the "send transaction logs to other SQL Server" check box.


Continue to run the wizard and specify other database maintenance options until the "specified log shipper destination" screen is reached.


Click "add" to add the target database.
To make this option available, you must have selected to use Log shipper in the previous step of the wizard.

On the "add target database" screen, select a server name.
This server must have been registered and is running Microsoft & Reg; SQL Server & # 8482 ; 2000 Enterprise Edition. If you want to make the target available source database, You must select the "allow database to use master role" check box. If this check box is not selected, the target database will not be able to use the source database role in the future. If you have selected the "allow database to use master role" check box, you must also specify the "transaction log Backup Directory" in the target database to which the log will be backed up ".

To change the target database of transaction logs from the default location, enter a location in the "directory" box.


If the source database is not in the target database, select the "create database" check box.
The "Database Name" box defaults to the source database name. If you want to use another database name on the target server, specify a new name. If you have selected to allow the target database to assume the source role, you cannot change the name of the database from the default value.

If you have selected the "create database" check box, you must specify the directory of data and logs on the target database in the "for Data" and "for logs" boxes.


If the source database already exists in the target database, select the "use existing database" check box. If the database name on the target server is different, enter this name in the "Database Name" box. This database must be used With Standby option to correctly accept logs.


On the "initialize target database" screen, Perform one of the following operations:
Click extract full database backup now ".
-Or -

Click "Use latest backup file" to initialize the target database.
On the "log shipper scheduling" screen, view the default log shipper scheduling. To change the scheduling, click "change ".


In "Copy / In the load frequency box, set the frequency (in minutes) for the target server to back up and restore transaction logs from the source server ).


In the "load latency" box, set the latency (in minutes) to wait before the target database restores transaction logs from the source server ).
The default value of this box is 0 Minutes, indicating that the target database should restore all transaction log backups immediately.

In the "file retention period" box, specify the length of time before the transaction log is deleted.


In the log transfer threshold dialog box, set Backup alarm threshold ".
This is the longest time to back up transaction logs from the source server. Once the time exceeds the specified threshold, the monitoring server generates an alarm.

In the "Do not synchronize alert threshold" box, specify the time between the last transaction log backup on the source server and the last transaction log restore on the target server.
Once the time exceeds the specified threshold, the monitoring server generates an alarm.

On the "specify log transfer Monitor Information" screen, type the name of the server that will monitor log transfer.


Click "use Windows Authentication" or "use SQL Server Authentication" to connect to the monitoring server. The log_shipping_monitor_probe logon name is fixed and must be used to connect to the monitoring server. If this is a new account, select a new password. If the account already exists on the monitoring server, you must specify the existing password.


If possible, use Windows authentication.



Note: You can use the database maintenance Wizard to set up log transfer. You can only send logs to disks, but not the "back up to tape" option.