SQL Server 2008 读书笔记(9)：备份和恢复数据库

Lesson 1: Backing up Databases

Full Backups

BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]

<backup_device>::= { { logical_device_name | @logical_device_name_var }
| { DISK | TAPE } =
{ 'physical_device_name' | @physical_device_name_var } }

<MIRROR TO clause>::= MIRROR TO <backup_device> [ ,...n ]
<general_WITH_options> [ ,...n ]::=

--Backup Set Options
COPY_ONLY | { COMPRESSION | NO_COMPRESSION }
| DESCRIPTION = { 'text' | @text_variable }
| NAME = { backup_set_name | @backup_set_name_var }
| PASSWORD = { password | @password_variable }
| { EXPIREDATE = { 'date' | @date_var }
| RETAINDAYS = { days | @days_var } }

--Media Set Options
{ NOINIT | INIT } | { NOSKIP | SKIP } | { NOFORMAT | FORMAT }
| MEDIADESCRIPTION = { 'text' | @text_variable }
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }

--Error Management Options
{ NO_CHECKSUM | CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

 

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'AdventureWorks_1.bak', DISK = ' AdventureWorks_2.bak'
GO

Transaction Log Backups

BACKUP LOG { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ][;]

Differential Backups

Filegroup Backups

BACKUP DATABASE database_name READ_WRITE_FILEGROUPS
[,<file_filegroup_list>] TO <backup_device>

 

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<SpecifyStrongPasswordHere>';

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'
[ WITH PRIVATE KEY
( FILE = 'path_to_private_key_file' ,
ENCRYPTION BY PASSWORD = 'encryption_password'
[ , DECRYPTION BY PASSWORD = 'decryption_password' ] ) ]

RESTORE VERIFYONLY FROM <backup device>

 Quick Check
1. What are the four types of backups?
2. How can you detect and log corrupt pages?

Quick Check Answers
1. You can execute full, differential, transaction log, and fi le/fi legroup backups. A full backup is required before you can create a differential or transaction log backup.
2. Execute ALTER DATABASE <database name> SET PAGE_VERIFY CHECKSUM.

 Practice:

Full backup and Transaction log backup

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'c:\test\AdventureWorks_1.bak'
MIRROR TO DISK = 'c:\test\AdventureWorks_2.bak'
WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

USE AdventureWorks2008R2
GO

INSERT INTO HumanResources.Department(Name, GroupName)
VALUES('Test1', 'Research and Development')
GO

BACKUP LOG AdventureWorks2008R2
TO DISK = 'c:\test\AdventureWorks_1.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

INSERT INTO HumanResources.Department(Name, GroupName)
VALUES('Test2', 'Research and Development')
GO

BACKUP LOG AdventureWorks2008R2
TO DISK = 'c:\test\AdventureWorks_2.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

 Diffrrential backup

USE AdventureWorks2008R2
GO

INSERT INTO HumanResources.Department(Name, GroupName)
VALUES('Test3', 'Research and Development')
GO

BACKUP DATABASE AdventureWorks2008R2
    TO DISK = 'c:\test\AdventureWorks_1.dif'
    MIRROR TO DISK = 'c:\test\AdventureWorks_2.dif'
    WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

Lesson Summary

•   Full backups are the starting point for every backup procedure and recovery process. A full backup contains only the pages within the database that have been used.
•   Differential backups contain all pages that have changed since the last full backup and are used to reduce the number of transaction log backups that need to be applied.
•   Transaction log backups contain all the changes that have occurred since the last transaction log backup.
•   To execute a transaction log backup, the database must be in either the Full or Bulk-logged recovery model, a full backup must have been executed, and the transaction log must not have been truncated since the last full backup.
•   You can back up only the fi legroups that accept changes by using the READ_WRITE_FILEGROUPS option of the BACKUP DATABASE command.