Shulou(Shulou.com)06/01 Report--

Goal:

1. Understand the backup mode (full, differential, log backup) and recovery model of SQLServer2008R2

two。 Ability to perform full backup, differential backup operation, transaction log backup and restore operation

3. Use maintenance plan to realize daily database backup operation

1. Backup and recovery of SQLServer2008R2

1. Type of recovery model

All databases can be set to three different recovery models: simple (simple), complete (full), and bulk logging (Bulk-Logged).

A full recovery model

The full recovery model is the default recovery model. Under the full recovery model, transaction logs need to be managed manually. The advantage of using the full recovery model is that you can recover to a database failure or a specified point in time. The disadvantage is that if not managed, the transaction log will grow rapidly and consume disk space. To clear the transaction log, you can only back up the transaction log or switch to simple mode.

B simple recovery model

Unlike the full recovery model, under the simple recovery model, transaction logs that are currently committed are cleared when a checkpoint occurs (checkpoint).

Therefore, under the simple recovery model, it is easy to cause data loss because the database cannot be restored to the moment of failure. It is important to note that although the system automatically clears the logs periodically under the simple recovery model, this does not mean that the transaction log files will not grow. For example, if you perform a bulk insert operation, SQL SERVER will treat the related operation as a transaction, during which the number of logs generated can be significant in extreme cases.

C bulk log recovery model

The bulk-logged recovery model is very similar to the full recovery model, but unlike the full recovery model, bulk operations are recorded as little as possible.

There are several types of bulk operations:

Import data in bulk, such as using the BCP (Bulk Copy Import), BULK INSERT command, or using the OPENROWSET command on BULK; large object operations (LOB), such as using WRITETEXT or UPDATETEXT;SELECT INTO words on TEXT, NTEXT, IMAGE columns; CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD, DBCC REINDEX

Under the full recovery model, the logs generated by the above operations will be very large. Using the bulk-logged recovery model will prevent unnecessary or unexpected log growth.

When a batch operation occurs, SQL SERVER only records the ID of the relevant data page (data page). In SQL SERVER, SQL SERVER pages has an internal ID, such as 5ID 547. In this way, a large amount of page ID can be recorded in small log files.

The use of bulk-logged recovery model will reduce a lot of space in data warehouses or databases with mass operations. However, when using the bulk recovery model, the recovery will become more difficult, generally speaking, can only be restored to the last transaction log backup point, but if all the transaction logs have been backed up, the recovery can still be successful.

2. Change the recovery model

Changing the recovery model of the database can be achieved by the following statement:

ALTER DATABASE database_name SET RECOVEY BULK_LOGGED

Changing the recovery model does not require a restart of the database instance.

II. Introduction to database backup

1. Backup location

On SQL Server, there are a variety of backup locations to choose from, such as local disk, network disk, remote address, tape, etc. Each backup location has its own advantages and disadvantages.

2. Logical backup device

On SQL Server, you can complete the backup by creating a logical backup device. The advantage of using a logical backup device is that when you change the backup address, you don't need to change the backup script, just the definition of the logical backup device.

The script to create a logical backup device is as follows:

SQL code

EXEC sp_adddumpdevice @ devtype='disk',@logicalname='MYBackup',@physicalname='D:\ backup\ mydb.bak'

Delete the script for the backup device:

SQL code

Sp_dropdevice @ logicalname='MYBackup'

The above script simply deletes the definition of the logical backup device, and the following script deletes the backup file at the same time:

SQL code

Sp_dropdevice @ logicalname='MYBackup',@devfile='DELFILE'

The logical backup device is used in the following ways:

SQL code

Backup database mydb to MYBackup

Of course, backup attributes such as expiration time can also be specified on logical backup devices, such as:

SQL code

Backup database mydb to MYBackup WITH EXPIREDATE='13/01/2010'

Or:

SQL code

BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7

3. Backup set and save set

Each backup is contained in a backup set, while a backup set is contained in a save set. "when backing up through the system GUI, SQL Server automatically assigns backup and save sets to simplify management." To display the assignment in T-SQL, use the following syntax:

SQL code

BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7

NAME='FULL',MEDIANAME='ALLBackups'

NAMEs refers to the backup set name, and MEDIANAME refers to the save set name.

4. Full backup

Regardless of the recovery model, all backups must have a full backup, especially log backups and differential backups, which cannot be restored without a full backup.

A simple full backup script is shown below, or you can specify a full backup through a maintenance plan:

SQL code

BACKUP DATABASE mydb to DISK='D:\ Backup\ mydb.bak'

It is important to note, however, that the above command appends a database backup to the currently existing file, creates it if it does not exist, and does not overwrite the original file. To overwrite a backup file with the same name, you need to specify the INIT parameter.

SQL code

BACKUP DATABASE mydb to DISK='D:\ Backup\ mydb.bak' WITH INIT

5. Log backup

Under the full recovery model or bulk-logged recovery model, log backup is not only a need for recovery, but also a way to manage transaction log files manually. If you never make a backup, under the full recovery model or the bulk recovery model, the transaction log will continue to grow until the disk is consumed.

The script for log backup is as follows:

SQL code

BACKUP LOG mydb_log TO DISK='D:\ backup\ mydb.trn'

You need to get into the habit of using the .trn extension for log backups.

Each action on the database is assigned a Log Sequence Number (LSN). If you need to restore to a specified point in time, you need to have a continuous LSN record. In other words, in the full recovery model or bulk logging mode, an uninterrupted transaction log backup chain is a basic requirement for database recovery.

6. Differential backup

Using log backups to restore is undoubtedly a slow process, especially when the last full backup has a long history. Using differential backups can shorten the recovery time. In fact, differential backup is just an option for BACKUP DATABASE, as follows:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ backup\ mydb.dif' WITH DIFFERENTIAL,INIT

When restoring the database, first restore the full backup of the database, then restore the differential backup of the database, and finally restore the log backup. Differential backups are closely linked to the last full backup, and no matter how many log backups and differential backups there are during the period, differential backups start from the last full backup. Therefore, it is often encountered that when you need to use the database temporarily on the production database, you will use BACKUP DATABASE. TO DISK='..' After you have made a backup, the next differential backup will be based on the full backup this time. If you delete this temporary full backup later, the later differential backup will be useless. Differential backup does not necessarily mean that there will be less disk space, depending on the actual situation. When a large number of operations occur during this period, differential backups can still become very large.

7. Error detection

During the backup process, the backup process validates the data at the same time, either verifying incomplete pages (torn page) or verifying checksums (checksum). To use this feature, you need to activate this option.

Incomplete page detection (Torn-page dection) simply checks each page to see if it has been written. If you find that only part of a page has been written, mark it as torn.

Checksum validation (checksum validation) is a new page validation mechanism. It adds a value to each page to indicate the actual size of the page. Although it may seem like a costly operation that affects performance, in fact, it is very efficient, similar to torn-page.

The backup process validates when backing up the database by comparing the values in the database and when the backup page is written to the hard disk. However, this validation is not automatic and needs to be specified, which is an option on the GUI page. If the backup is done through T-SQL, the statement is as follows:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ data\ mydb.bak' WITH CHECKSUM

If an error is found during the backup, SQL Server writes the error message to the SUSPECT_PAGE table on MSDB. At the same time, by default, the backup behavior is stopped (STOP_ON_ERROR) so that administrators can troubleshoot errors.

However, there is another option for checksum verification during backup (CONTINUE_ON_ERROR), that is, if an error is found, the backup process will not be interrupted, but the error page information will be recorded on MSDB..SUSPECT_PAGE. It is important to note that the SUSPECT_ page table has a row limit of up to 1000 rows, and if so, the backup will fail. Activating checksum verification will obviously affect the performance of the backup. But it's still necessary.

8. Secure backup

Full and log backup statements also support the use of password attributes, such as:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ mydb.bak' WITH PASSWORD='mydb'

The specified password is easy to crack. Therefore, if you do need to encrypt some backup data, you can store the backup on an encrypted file system or other secure storage device.

At the same time, SQL Server also provides the ability to encrypt real columns. The encryption function is an industry standard.

9. Stripe backup

In some cases, when a single hard disk cannot store a complete database backup, the database backup can be divided into multiple parts and stored on different disks, which is called a stripe backup. The advantage of using stripe backup is that it can make good use of space, but if some part of the backup is lost or damaged, the whole backup will be invalid.

The statement is as follows:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ mydb.bak',DISK='E:\ mydb.bak' WITH INIT,CHECKSUM, CONTINUE_ON_ERROR

The backups on disk D and disk E above are inseparable.

10. Mirror backup

Unlike stripe backups that keep the same backup on multiple disks, mirrored backups keep multiple backups on different disks. The statement is as follows:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ mydb.bak'

MIRROR TO DISK='E:\ mydb.bak'

WITH INIT,CHECKSUM,CONTINUE_ON_ERROR

In practice, it is more appropriate to mirror the log backup.

11. COPY-ONLY backup

As mentioned in the differential backup, the differential backup is based on the previous full backup. Therefore, if in a pre-arranged backup plan, if there is another full backup between the full backup and the differential backup, the differential backup will be interrupted, and if the temporary full backup is deleted, the data loss will occur. After SQL SERVER 2005, SQL SERVER provides an option, copy-only. Full backups using the copy-only option do not plan for the original backup schedule with the following statement:

SQL code

BACKUP DATABASE mydb TO DISK='D:\ mydb.bak' WITH INIT,CHECKSUM,COPY_ONLY

III. File and filegroup backup

1. Backup data files

Backing up data files can also be achieved through BACKUP DATABASE statements. As follows:

SQL code

BACKUP DATABASE mydb FILE='D:\ Data\ mydb.ndf' TO DISK='E:\ Backup\ mydbdata.bak'

The above statement is equivalent to data file-level full backup, similar to database-level backup, file-level backup also has differential backup, of course, the premise is that there must be a corresponding full backup of files.

The statements for differential backups are as follows:

SQL code

BACKUP DATABASE mydb FILE='D:\ Data\ mydb.ndf' WITH DIFFERENTIAL

TO DISK='E:\ Backup\ mydbdata_dif.bak'

5. Back up the filegroup

Similar to individual backup files, you can perform a similar backup operation on a filegroup. There are also two ways to back up filegroups, one is specified through the GUI interface, and the other is through T-SQL.

The statement of T-SQL is as follows:

SQL code

BACKUP DATABASE mydb FILEGROUP='PRIMARY' TO DISK='E:\ Backup\ mydbpri.bak'

6. Incomplete backup (partial backup)

On filegroup backups, an incomplete backup is actually equivalent to a full backup, which can be achieved by specifying the keyword READ_WRITE_FILEGROUPS.

The statement is as follows:

SQL code

BACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK='D:\ mydb.bak'

What exactly does incomplete backup mean? When do you need an incomplete backup? If you set a read-only filegroup and the read-only filegroup needs to be backed up again, you can do the backup without the BACKUP DATABASE statement, just pick a time to stop the instance, and then perform an incomplete backup.

IV. Data recovery

1 、 Restore vs. Recovery

Restore and Recovery are two different concepts, but they are closely related in the process of data recovery.

Restore is equivalent to rebuilding the whole or part of the database from the backup set, and Restore cannot change the state of the database, such as offline or online.

Once you have Recovery the database, you can no longer perform Restore operations.

The statements for database recovery are as follows:

SQL code

RESTORE DATABASE mydb FROM mydbdevice WITH RECOVERY

It is a good habit to display a specified RECOVERY or NORECOVERY.

2. Back up the information in the file

In the primary file, some information related to the database structure, such as the file location, is stored. Therefore, after the backup, this information is also retained in the backup file. If you want to restore to a different disk or server, you need to make additional changes.

In SQL Server, RESTORE HEADERONLY, RESTORE FILELISTONLY, RESTORE LABELONLY, RESTORE VERIFYONLY and other commands are provided to read the backup file information. You can also get information about backup sets through the tables in MSDB.

A.RESTORE HEADERONLY

SQL code

RESTORE HEADERONLY

FROM DISK='D:\ family_20100108.bak'

The meaning of each field can be viewed in Books online.

B.RESOTRE FILELISTONLY

SQL code

RESTORE FILELISTONLY

FROM DISK='D:\ family_20100108.bak'

C.RESOTRE LABELONLY

SQL code

RESTORE LABELONLY

FROM DISK='D:\ family_20100108.bak'

D.RESOTORE VERIFYONLY

SQL code

RESTORE VERIFYONLY

FROM DISK='D:\ family_20100108.bak'

3. Restore from full backup

It should be noted that before doing a restore, you should get into the habit of backing up the current log, otherwise it is easy to cause data loss. SQL Server provides the REPLACE option to force recovery, but in this way the end log is lost.

In general, if the current log is not backed up, you will receive the following error

The above error mentions the use of WITH REPLACE or WITH STOPAT commands to complete the restore, but should be avoided as far as possible.

If you want to restore the database to a different disk, you can specify it through the GUI page or through the T-SQL statement.

T-SQL is implemented as follows:

SQL code

RESTORE DATABASE Family

FROM DISK='D:\ family_20100108.bak'

WITH MOVE 'Family'TO 'D:\ Family.mdf'

MOVE 'Family_Log' TO'D:\ Family.ldf'

WITH RECOVERY

4. Restore to the specified point in time

There are three options to restore to a specified point in time, one is to specify a time explicitly, one is to specify a LSN number, and the other is to create and specify a log marks.

A. TIME

In general, recovery requires recovery to a specified point in time, which can be achieved through the GUI interface or through T-SQL.

SQL code

RESTORE DATABASE Family

FROM DISK='D:\ family_20100108.bak'

WITH NORECOVERY

RESTORE LOG Family

FROM DISK='D:\ family_20100108.trn'

WITH RECOVERY,STOPAT 'jan 8, 2009 3 10 pm'

B. LSN

If you know the exact LSN number, you can also use the LSN number to restore the specified LSN. For information about LSN, you can use RESOTRE HEADERONLY. This approach can only be achieved through T-SQL.

SQL code

RESTORE DATABASE Family

FROM DISK='D:\ family_20100108.bak'

WITH NORECOVRY

RESTORE LOG Family

FROM DISK='D:\ family_20100108.trn'

WITH RECOVRY,STOPATMARK LSN:2433:5422

C. Log Marks

You can also revert to the specified Log Marks by creating a Log Mark. For example, if you create a logmarkexample, you will restore to logmarkexample when you restore.

SQL code

RESTORE DATABASE Family

FROM DISK='D:\ family_20100108.bak'

WITH NORECOVRY

RESTORE LOG Family

FROM DISK='D:\ family_20100108.trn'

WITH RECOVRY,STOPATMARK 'logmarkexample'

5. Restore a mirror backup or stripe backup

For mirrored backups, every backup is the same, so restoring any backup can complete the recovery. For stripe backups, you need to specify all stripe backups at the same time, which can be faster than a single backup.

Examples of stripe backups are as follows:

SQL code

RESTORE DATABASE Family

FROM DISK='D:\ family_20100108.bak'

DISK='D:\ family_20100108.bak'

WITH NORECOVRY

6. Recover the data page

After SQL Server2005, SQL Server provides the ability to recover data pages. Data page recovery can be done online or offline

However, only the actual user data pages can be restored, while other pages cannot be restored by backup. Such as Global Allocation Map (GAM), Secondary Global Allocation Map (SGAM), Page Free Space (PFS), etc.

Restoring a data page is equivalent to a full recovery, except that you need to specify a specific page.

SQL code

RESTORE DATABASE Family PAGE'20 1570, 1570, 2014, 1571, 20, 15, 72'.

FROM DISK='D:\ family_20100108.bak'

WITH NORECOVRY

Data pages can be found through MSDB..SUSPECT_PAGE or DBCC CHECKDB.

7. Restore the system database

The system database stores the information of the relevant database on a SQL Server instance. If it is lost, it will bring more losses.

A. MASTER

The recovery of Master database is different from that of other databases. To restore the MASTER database, you need to switch from SQL Server to single-user mode, if you cannot, stop the SQL Server service, and then start with the sqlserver-m command line.

Of course, you can also start the SQL Server service with the net start "server name". After startup, restore with the SQLCMD command

B. MSDB

What is stored in MSDB is the content of SQL Agent, such as job, scheduling, operator, warning and other information; at the same time, it also stores SQL Server Integration Service (SSIS) and other information.

The recovery process is the same as the normal database recovery process. And because it is in simple mode, the recovery process is simpler.

C. MODEL

The Model database is used to store the information needed to create the database, and it also needs to be backed up and restored if the MODEL database is used.

The process of backup and recovery is the same as that of a normal database.

D. Tempdb

Tempdb does not require backup and recovery, and SQL Server automatically clears tempdb and restarts tempdb during each startup. One thing to pay attention to on tempdb is its spatial planning, because in some cases the tempdb becomes so large that it runs out of space, resulting in SQL Server shutting down.

To modify the storage path of tempdb, use the following statement:

SQL code

Use master

Go

Alter database tempdb modify file (name = tempdev, filename ='E:\ Sqldata\ tempdb.mdf')

Go

Alter database tempdb modify file (name = templog, filename ='E:\ Sqldata\ templog.ldf')

Go

E. Resource

Resource is a newly introduced database after SQL Server2005, which stores part of the information previously stored in master and other system databases in Resource database.

Resource cannot be backed up through T-SQL or GUI, because it cannot be seen, and its physical files can only be copied directly by hand.

6. Restore the backup of data files

The third part mentions the backup of files, which is a backup of single or multiple files in a manner similar to database recovery. But get into the habit of backing up your current log files before restoring.

SQL code

BACKUP LOG Family

TO DISK='E:\ Familylog.bak'

WITH NORECOVERY

Then restore the files that need to be restored, such as

SQL code

RESTORE DATABASE Family

FILE='D:\ DATA\ Family.mdf'

FROM DISK='E:\ Familyprimary.bak'

WITH NORECOVERY

RESTORE LOG Family

FROM DISK='E:\ Familylog.bak'

WITH NORECOVERY

RESTORE DATABASE Family

WITH RECOVERY

If there is a differential backup on the data file, perform a differential backup recovery before the log recovery, such as:

SQL code

RESTORE DATABASE Family

FILE='D:\ DATA\ Family.mdf'

FROM DISK='E:\ Familyprimary.bak'

WITH NORECOVERY

RESTORE DATABASE Family

FILE='D:\ DATA\ Family.mdf'

FROM DISK='E:\ Familyprimay.dif'

WITH NORECOVERY

RESTORE LOG Family

FROM DISK='E:\ Familylog.bak'

WITH NORECOVERY

RESTORE DATABASE Family

WITH RECOVERY

7. Recovery of filegroup

It is consistent with the principle of data file recovery, except that the specific file groups are changed.

SQL code

BACKUP LOG Family

TO DISK='E:\ Familylog.bak'

WITH NORECOVERY

RESTORE DATABASE Family

FILEGROUP='PRIMARY'

FROM DISK='E:\ Familyprimary.bak'

WITH NORECOVERY

RESTORE LOG Family

FROM DISK='E:\ Familylog.bak'

WITH NORECOVERY

RESTORE DATABASE Family

WITH RECOVERY

Scheduled tasks:

◆ 1, Administration-> SQL Server Agent-> Job (press the right mouse button)-> New Job->

◆ 2. New job attributes (General)-> name [customize the name of this job]-> check mark in the enabled box->

You can choose or use the default [unclassified (local)]-> owner defaults to login SQL Server user [optional other login]->

Description [fill in the detailed description of this job]

[to create a job classification:

SQL Server Agent-> Job-> right-click to select all tasks-> add, modify, delete]

◆ 3. Properties of New Job (step)-> New-> step name [Custom first step name]-> Type [Transact-SQL (TSQL) script]->

Database [Database to operate]-> command

[if it is a simple SQL, you can write it directly, or you can use the open button to enter a written * .sql file.

If you want to execute a stored procedure, fill in

Exec p_procedure_name v_parameter1, [v_parameter2... V_parameterN]

-> OK

(if you have more than one step, you can call the new button below again, or you can insert, edit, or delete multiple steps that already exist.)

◆ 4. Attribute (schedule)-> New schedule-> name [Custom schedule name]-> check mark in the enabled box-> schedule-> repeat->

Change [scheduling schedule]-> OK

(if you just save this job, don't do it regularly to remove the check mark in the enabled box.)

◆ 5. Build Job Properties (Notification)-> use the default notification method [write to the Windows application system log when the job fails]-> OK.

Some SQL Server knowledge related to job execution:

The SQLSERVERAGENT service must be running properly, and the NT login user who started it should be the same as the NT login user who started the SQL Server database.

Right click on the job to view the history of the execution of the job, or you can start and stop the job immediately.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.