Application: SQL Server 08 log can also backup database
As a major product version of Microsoft SQLServer2008, in addition to the many new features and key improvements, making it become so far the most powerful and comprehensive SQLServer version, the backup in SQLServer2008 is the one large window, there are a variety of methods to use the backup of a database SQLServer2008, such as the differential backup and things log backup. The transaction log backup copy the last transaction log backup all data changes completely or in the past. Transaction log backup is usually very fast and very small, high reliability and backup scheme after the mirror, can achieve the minute level disaster recovery.
The detailed implementation process transaction logs backup:
First, the establishment of a database server, the name Server-DW, need to backup database for JKL_DW. At the same time, the establishment of a backup server, the name Server-ETL. Create a folder on the Server-ETL, is used to store the backup of the database, in this case named jklDW.
In addition, create another folder on the Server-ETL, is used to store the transaction log backup data is transmitted, named LogBackup, and then share this folder. In addition to the establishment of a deposit for restoring a transaction log database replication, named LogCopy, also share this folder.
Backup database - the general page
Next create a full backup of JKL_DW database.
In the target selection of the path database backup file.
Backup database options page
Media coverage: to the backup to the new media set and remove all existing backup sets, also has a name.
Reliability: verify the backup and write the media before checking and the two check is completed.
Compression: select the compression backup, can save about 80% space.
Then the backup file copy to the Server-ETL.
Page second: set transaction logs
Set transaction logs
Enable the transaction logs, database right click menu, select properties, switch to the transaction logs, tab, select the database used for the primary database in a log shipping configuration, enable the transaction logs.
The transaction log backup: click the backup set, you can open the transaction log backup set form.
The secondary database: click on the add, can open the secondary database settings window.
Configuration of the transaction log backup
Set up a backup operation
The network path backup folder: here to fill in the backup server set for network path to store the transaction log backup folder. Here to explain, the backup folder on the backup server, but this is not required, can also be the backup folder on the database server, or on third servers, as long as the guarantee of this folder from the network by the database server and backup server access.
The backup job, job name: in this case, named the jklDW transaction log backup: transaction log backup job, and then set the plan.
Set up an operation plan properties
Name: names for the jklDW transaction log backup: transaction log backup job.
Frequency: for every day every hour. Because in this case the backup of data warehouse, data updated once a day, there is no need to set too high frequency. If it is a business system, you can set a short, ensure data security.
Page third: target database backup
The target database backup
A backup copy of the database in the server connection
First need to connect to the instance database backup, click on the link, open the connection to the server window. At the same time, connected to the Server-ETL, the following options take effect after connection, can be set.
Initialize the secondary database
Assistant: here to choose the target database restore database, in this case jklDW.
Initialize the secondary database: because the front has to restore the database, so choose not, the secondary database is initialized.
Set the copy files
The destination folder copy: here to enter the network path copy folder.
Job name: named jklDW transaction log backup: transaction log copying homework, then edit operation.
Name: names for the jklDW transaction log backup: a transaction log backup operation plan.
Frequency: for every day every hour. This frequency can be decided according to the specific needs of the business.
The successful implementation of the backup task, backup database into standby / read-only status.
But the transaction log backup disadvantage is the recovery ability is limited, the process consumes a lot of resources. In addition, if a log backup is damaged or for some reason can not be used, then the entire recovery chain will be interrupted, and not as the effects of the log backup is good. Thus, SQLServer2008 provides several recovery method, including a full recovery and the simple recovery. Recovery method of choice will depend upon whether the transaction log file is saved, and if so, any type of backup can be performed.
Posted by Hebe at November 20, 2013 - 8:57 AM