MSSQL Backup statements and recovery introduction

----------------------------------------------------------------------


1, SQL database recovery model


----------------------------------------------------------------------


1) Full Recovery model


-----------------


(1) Data files and log files to back up the database when backing up


(2) Restores the database using a copy of the database's backup data file and all log information.


(3) Restores all data and restores the database to any specified time.


(4) in order to ensure real-time point recovery, all of the database will be recorded in a full log, so that the log occupies a large space, the performance has also affected.


------------------


(2) Bulk-logged Recovery model


------------------


(1) Data files and log files to back up the database when backing up


(2) Restores the database using a copy of the database's backup data file and all log information.


(3) log does not record * details (such as SELECT INTO, create INDEX, etc.), but only the final results of the *, so occupy little log space.


(4) only supports restoring a database to a transaction log backup, rather than point-in-time recovery, which can result in data loss.


-------------------


(3) Simple recovery model


-------------------


(1) Backs up only the data files and restores the database with the backed-up data files.


(2) can only restore data to the time the data file is backed up, possibly resulting in the most data loss.


(3) Not suitable for production system and large-scale * environment selection.


-----------------------------------------


Alter DB D1 set recovery simple--Set the database recovery model


ALTER DATABASE D1 set recovery bulk_logged


ALTER DATABASE D1 set recovery full


----------------------------------------------------------------------


2, backup device


----------------------------------------------------------------------


1) Physical equipment


---------------------------


Disk: Support for local disks or network backups


tape: Support for tape drive backup


name pipe: Supports third-party backup software


---------------------------


2 Logical Device


---------------------------


permanent backup files: can be reused and should be created before backup.


temporary backup file: For one-time backups, created at backup time.


-------------------------------------------------


exec sp_addumpdevice ' disk ', ' bak2 ', ' e:back_devicebak2.bak '--Create a permanent disk backup device


exec sp_addumpdevice ' disk ', ' Bak3 ', ' E:back_devicebak3.bak '


----------------------------------------------------------------------


exec sp_addumpdevice ' disk ', ' bak4 ', ' \sv2backupbak4.bak '--Create a network permanent disk backup device


exec sp_addumpdevice ' disk ', ' bak5 ', ' \sv2backupbak5.bak '


----------------------------------------------------------------------


exec sp_dropdevice ' bak5 '--delete backup devices


----------------------------------------------------------------------


Backup Database D3 to Bak3--backs up databases to backup devices


Backup Database d4 to Bak4


----------------------------------------------------------------------


RESTORE HEADERONLY from BAK2--View the contents of the backup device


----------------------------------------------------------------------


Backup Database D3 to disk= ' E:back_filed3.bak '--Backing up databases to temporary backup files


Backup Database d4 to disk= ' E:back_filed4.bak '


----------------------------------------------------------------------


Restore Database d3 from BAK3 restoring databases from backup devices


Restore Database D4 from disk= ' E:back_filed4.bak '-restoring databases from backup files


----------------------------------------------------------------------


3, using multiple backup files to store backups


----------------------------------------------------------------------


1 SQL can write * to multiple backup files at the same time. If you put these files on multiple tape drives or disks, you can increase your backup speed.


2 The multiple backup files must be in a media set with the same type of media.


3 files in a media set must be used simultaneously, not separately.


4 The media set can be reclassified through the Format command, but the data in the original backup set cannot be reused.


--------------------------------------------------------------------


Backup Database D4 to bak4,bak5,bak6 with Medianame= ' bak456 ', format--Backs up D4 and forms media Set


Backup Database D3 to BAK4-failed because the files in media set must be used concurrently


Backup Database D3 to Bak4,bak5,bak6-successful, D3 also back up to media set


RESTORE HEADERONLY from bak4,bak5,bak6--View backup content in media set


----------------------------------------------------------------------


Backup Database D4 to bak4 with Medianame= ' BAK4 ', format--re-dividing media Set


Backup Database D3 to bak5,bak6 with Medianame= ' bak56 ', format


----------------------------------------------------------------------


Backup Database D1 to BAK1 with init--with init overwrite content


Backup Database D2 to BAK1 with Noinit--with append content to backup device


restore HEADERONLY from Bak1


----------------------------------------------------------------------


4, Backup method


----------------------------------------------------------------------


1 Full backup


-------------------------------------------


(1) is the baseline for backup. A full backup is recommended for the first backup at backup time.


(2) A full backup backs up all data files, data objects, and data for the database.


(3) backs up any uncommitted transactions in the transaction log. Because the committed transaction has been written to the data file.


--------------------------------------------


Backup Database D1 to BAK1 with init--full backup


Backup Database D1 to BAK1 with Noinit


----------------------------------------------------------------------


2) Differential backup


---------------------------------------------


(1) is based on a full backup.


(2) Backs up all database changes since the most recent full backup.

When
(3) is restored, only the most recent full backup and the most recent differential backup are applied.


-----------------------------------------------


Backup Database D2 to BAK2 with Init,name= ' d2_full '-differential backups, which should be fully backed up at first time


CREATE TABLE B1 (c1 int not null,c2 char (a) NOT null)


Backup Database D2 to BAK2 with Differential,name= ' d2_diff1 '


Insert B1 VALUES (1, ' a ')


Backup Database D2 to BAK2 with Differential,name= ' d2_diff2 '


Insert B1 VALUES (2, ' B ')


Backup Database D2 to BAK2 with Differential,name= ' D2_DIFF3 '


Insert B1 VALUES (3, ' C ')


Backup Database D2 to BAK2 with Differential,name= ' D2_diff4 '


restore HEADERONLY from Bak2


----------------------------------------------------------------------


3 transaction log backup


-------------------------------------------------------------


(1) is based on a full backup.


(2) is an incremental backup that backs up the transaction log that was written since the last backup to the backup.


(3) allows recovery to a failure point or a mandatory point in time.


(4) When restoring, you need to apply a full backup and every log backup after a full backup.


-------------------------------------------------------------


Backup Database D3 to Bak3 with Init,name= ' d3_full '--log backups, which should be fully backed up at the first time


CREATE TABLE B1 (c1 int not null,c2 char (a) NOT null)


BACKUP LOG d3 to BAK3 with


Insert B1 VALUES (1, ' a ')


BACKUP LOG d3 to BAK3 with


Insert B1 VALUES (2, ' B ')


BACKUP LOG d3 to BAK3 with


Insert B1 VALUES (3, ' C ')


BACKUP LOG d3 to BAK3 with


restore HEADERONLY from Bak3


----------------------------------------------------------------------


CREATE TABLE B1 (c1 int not null,c2 char (a) NOT null)--full+log+diff


BACKUP log d4 to bak4 with


Insert B1 VALUES (1, ' a ')


BACKUP log d4 to bak4 with


Insert B1 VALUES (2, ' B ')


Backup Database D4 to bak4 with Differential,name= ' d4_diff1 '


Insert B1 VALUES (3, ' C ')


BACKUP log d4 to bak4 with


Insert B1 VALUES (4, ' d ')


BACKUP log d4 to bak4 with


Insert B1 VALUES (5, ' d ')


Backup Database D4 to bak4 with Differential,name= ' d4_diff2 '


restore HEADERONLY from Bak4


----------------------------------------------------------------------


Log Clear


-----------------------------------------


1 If the log space is filled, the database will not be able to record changes.


2 The log was truncated when the database was fully backed up.


3) If the ' Trans Log on checkpoint ' option is set to true, the result is that the log is not saved, that is, not logged, not recommended.


4 with TRUNCATE_ONLY and with no_log to clear log when log is full


5) with No_truncate can save the log completely without clearing it even if the data file is corrupted. Used primarily for database problems before recovery. The data can be restored to the point of failure.


-------------------------------------------


EXEC sp_dboption D3


exec sp_dboption


sp_dboption ' D3 ', ' trunc. Log on chkpt. ', ' true '--set automatic purge database log


sp_dboption ' D3 ', ' trunc. Log on chkpt. ', ' false '-removes the option to automatically clear the database log


----------------------------------------------------------------------


BACKUP log d4 with TRUNCATE_ONLY--sets the D4 log to clear log when full, and do a purge record


----------------------------------------------------------------------


BACKUP log d4 with NO_LOG--sets the D4 log to clear the log when it is full, but does not make a purge record


----------------------------------------------------------------------


BACKUP log d4 to bak4 with no_truncate--back up the current database log (DEMO) as soon as the D4 database is corrupted


--------


Use No_truncate


Complete + Modify 1+ difference + Change the difference + modify 3+ stop SQL, delete database data file + Restart SQL


BACKUP LOG no_truncate


Restore to modify 3