Restore classic backup documents on SQL Server

SQL backup ------------- 1. SQL database recovery model ‑ 1) full recovery model ----------------- (1) backup of database data files and log files (2) the backup data file copy and all log information of the database are used to restore the database. (3) Restore all data and restore the database to any specified time point. (4) to ensure immediate point recovery, all * operations of the database are fully recorded in the log. In this way, the log occupies a large space and affects the performance. ------------------ (2) large-capacity log record recovery model ---------------- (1) backup of database data files and log files (2) the backup data file copy and all log information of the database are used to restore the database. (3) logs do not record * For details (such as select into, create index, etc.), but only record * for the final result. Therefore, the log space is small. (4) it only supports restoring the database to the time when the transaction log is backed up, and does not support instant point recovery. Therefore, data may be lost. ----------------- (3) simple recovery model ------------------- (1) only backup data files are used during backup, and the database is also restored using the backup data files. (2) data can only be recovered to the time when the data file is backed up, which may lead to the most data loss. (3) It is not suitable for selection in production systems and large-scale * production environments. Alter database d1 set recovery simple -- set the database recovery model alter database d1 set recovery bulk_logged alter database d1 set recovery full recovery 2. Backup Device snapshot 1) physical device ----------------------- disk: support local disk or network backup tape: Support for tape drive backup name pipe: support for third Backup Software --------------------------- 2) logical device --------------------------- permanent backup file: can be reused, should be created before backup. Temporary backup file: used for one-time backup and created during Backup. Using exec sp_addumpdevice 'disk', 'bak2', 'e: \ back_device \ bak2.bak' -- create a permanent disk backup device exec sp_addumpdevice 'disk', 'bak3 ', 'e: \ back_device \ bak3.bak '{exec sp_addumpdevic' disk', 'bak4 ',' \ sv2 \ backup \ bak4.bak '-- create a network permanent disk backup device exec sp_addumpdevic' disk ', 'bak5 ',' \ sv2 \ backup \ bak5.bak '----------- Export exec sp_dropdevice 'bak5 '-- delete the backup device snapshot backup database d3 to bak3 -- back up the database to the backup device backup database d4 to bak4 snapshot restore headeronly from bak2 -- view the contents of the backup device Snapshot ----------------------------------------------------- ----------------- Backup database d3 to disk = 'e: \ back_file \ d3.bak '-- back up the database to the temporary backup file backup database d4 to disk = 'e: \ back_file \ d4.bak '-------------------------------------------------------------------- restore database d3 from bak3 -- restore database d4 from disk = 'e: \ back_file \ d4.bak '-- restore the database from the backup file ------------------------------------------------------------ 3. Use multiple backup files to store the backup --- ------------------------------------------------------------------- 1) SQL can be written to multiple backup files at the same time. If these files are stored on multiple tape drives or disks, the backup speed can be improved. 2) These backup files must be stored in a media set in the same industry. 3) files in a media set must be used at the same time, but cannot be used separately. 4) You can use the format command to re-divide the media set, but the data in the original backup set cannot be used any more. Restore backup database d4 to bak4, bak5, bak6 with medianame = 'bak456', format -- back up D4 and form Media Set backup database d3 to bak4 -- failed, because the files in the Media set must use backup database d3 to bak4, bak5, bak6 at the same time -- success, D3 is also backed up to the Media Set restore headeronly from bak4, bak5, bak6 -- view the backup content in Media Set ------------------------------------------------------------------------ bac Kup database d4 to bak4 with medianame = 'bak4 ', format -- redivide Media Set backup database d3 to bak5, bak6 with medianame = 'bak56 ', format your backup database d1 to bak1 with init -- with init rewrite the backup device content backup database d2 to bak1 with noinit -- with noinit append the content to the backup device restore headeronly from bak1 ----------------------------------------------- ----------------------- 1) Full backup --------------------------------------------- (1) is the backup benchmark. Full backup is recommended for the first backup. (2) Full backup backs up all data files, data objects, and data of the database. (3) Any uncommitted transactions in the transaction log will be backed up. Because the committed transaction has been written into the data file. ---------------------------------------- Backup database d1 to bak1 with init -- full backup database d1 to bak1 with noinit defaults 2) Differential backup slave (1) based on full backup. (2) backup of all database changes since the last full backup. (3) only the last full backup and the latest differential backup are applied during restoration. ------------------------------------------- Backup database d2 to bak2 with init, name = 'd2 _ full' -- Differential backup. During the first backup, create table b1 (c1 int not null, c2 char (10) 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 _ di Ff3 'insert b1 values (3, 'C') backup database d2 to bak2 with differential, name = 'd2 _ diff4' restore headeronly from bak2 limit 3) transaction Log backup ------------------------------------------------------------- (1) based on full backup. (2) Incremental backup, that is, the transaction logs written from the last backup to the backup. (3) The fault can be recovered to the time point or a forced time point. (4) During restoration, full backup and full backup must be applied for each log backup. ----------------------------------------------------------- Backup database d3 to bak3 with init, name = 'd3 _ full' -- log backup. create table b1 (c1 int not null, c2 char (10) during the first backup) 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 --- Using create table b1 (c1 int not null, c2 char (10) 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 wi Th insert b1 values (5, 'D') backup database d4 to bak4 with differential, name = 'd4 _ diff2' restore headeronly from bak4 logs clear ----------------------------------------------- 1) if the log space is filled up, the database cannot record the changes. 2) logs are truncated when the database is fully backed up. 3) if the 'Trans log on checkpoint' option is set to TRUE, the result is that no logs are saved, that is, no logs are recorded. We do not recommend that you use this option. 4) with truncate_only and with no_log set to clear logs when logs are full. 5) with no_truncate, logs can be fully saved without being cleared, even if the data file is damaged. It is mainly used for database use before recovery after a problem occurs. Data can be restored to the time when a fault occurs. ----------------------------------------- Exec sp_dboption d3 exec sp_dboption 'd3 ', 'trunc. log on chkpt. ', 'true' -- set to automatically clear the Database Log sp_dboption 'd3', 'trunc. log on chkpt. ', 'false' -- remove logging backup log d4 with truncate_only -- set to clear logs when D4 logs are full, and clear the logs ------------------------------------------------------------------ ---- Backup log d4 with no_log -- clear logs when D4 logs are full, but no clear records -------------------------------------------------------------------- backup log d4 to bak4 with no_truncate -- immediately back up the current database log (DEMO) when the D4 database is damaged) -------- use no_truncate to completely + modify 1 + difference + modify 2 + difference + modify 3 + stop SQL, delete database data files + restart SQL backup log no_truncate and restore, can be restored to modify 3 -------------------------------------------------------------------- 4) file/File Group Backup ------------------------- ----------------------------------------- (1) is used for super large databases. (2) Back up only selected files or file groups. (3) log backup must be performed at the same time. (4) file/File Group Backup and log backup are used for restoration. (5) The backup volume is small and the recovery speed is fast. Create database d5 on primary (name = d5_data1, filename = 'e: \ data \ d5 \ d5_data1.mdf ', size = 2 MB ), filegroup FG2 -- create filegroup FG2 when creating a database (name = d5_data2, filename = 'e: \ data \ d5 \ d5_data2.ndf ', -- and put the file d5_data2 in FG2 size = 2 Mb) log on (name = d5_log1, filename = 'e: \ data \ d5 \ d5_log1.ldf', size = 2 Mb) use d5 go alter database d5 add file (name = d5_data3, filename = 'e: \ data \ d5 \ d5_data5.ndf ', size = 2 MB) to filegroup FG2 -- Set Add d5_data3 to alter database d5 add filegroup FG3 in file group FG2 -- add file group FG3 alter database d5 -- add d5_data4 to file group FG2 add file (name = d5_data4, filename = 'e: \ data \ d5 \ d5_data4.ndf ', size = 2 MB) to filegroup FG3 sp_helpdb d5 create table t1 (c1 int not null, c2 char (10) not null) on [primary] -- put different tables into different filegroups create table t2 (c1 int not null, c2 char (10) not null) on FG2 create table t3 (c1 int not null, c2 char (10) no T null) on FG3 ---------------------------------------------------------------------- backup database d5 to bak5 with init, name = 'd5 _ full' -- filegroup backup database d5 filegroup = 'primary' to bak5 with backup log d5 to bak5 with backup database d5 filegroup = 'fg2 'to bak5 with backup log d5 to bak5 with backup database d5 filegroup = 'fg3' to bak5 with backup log d5 to bak5 ------------------- ------------------------------------------------- Backup database d5 to bak6 with init, name = 'd5 _ full' -- file backup database d5 file = 'd5 _ data1 'to bak6 with backup log d5 to bak6 with backup database d5 file = 'd5 _ data2' bak6 with backup log d5 to bak6 with backup database d5 file = 'd5 _ data3 'to bak6 with backup log d5 to bak6 with backup database d5 file = 'd5 _ data4' to bak6 backup log d5 To bak6 with restore headeronly from bak6 ============================== ============================================================== SQL restore = ========================================================== ===========================1. Verify the Backup restore headeronly from bak3 restore filelistonly from bak3 with file = 1 restore labelonly from bak3 restore verifyonly from bak3 ------------- Restore 2. restore headeronly from bak1 restore database d1 from bak1 with file = 2 -- restore headeronly from bak2 from full backup -- restore database from differential backup d2 from bak2 with file = 1, norecovery restore datab Ase d2 from bak2 with file = 5, recovery ---------------------------------------------------------------------- restore headeronly from bak3 -- restore database d3 from bak3 with file = 1 from log backup, norecovery restore log d3 from bak3 with file = 2, norecovery restore log d3 from bak3 with file = 3, norecovery restore log d3 from bak3 with file = 4, norecovery restore log d3 from bak3 with file = 5, recovery -------- Restore database d3 from bak3 with file = 1, norecovery -- restore to the specified time restore log d3 from bak3 with file = 2, norecovery restore log d3 from bak3 with file = 3, norecovery restore log d3 from bak3 with file = 4, recovery, stopat = '2017-08-15 11:29:00. 000 '---------------------------------------------------------------------- restore database d5 filegrou P = 'fg2 'from bak5 with file = 4, norecovery -- restore file Group Backup restore log d5 from bak5 with file = 5, norecovery restore log d5 from bak5 with file = 7, recovery ---------------------------------------------------------------------- restore headeronly from bak6 -- restore file Backup restore database d5 file = 'd5 _ data3 'from bak6 with file = 6, norecovery restore log d5 from bak6 with file = 7, norecovery restore log d5 from bak6 File = 9, recovery ---------------------------------------------------------------------- restore database d5 from bak6 with replace -- delete an existing database, rebuilding the database from the backup ------------------------------------------------------------------ create database d6 -- move to move the database file to the new location on primary (name = d6_data, filename = 'e: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ d6_Data.MDF ', size = 2 MB) log on (name = d6_log, filenam E = 'e: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ d6_log.ldf', size = 2 MB) go backupdatabase d6 to bak6 with init drop database d6 restore database d6 from bak6 with move 'd6 _ data' to 'e: \ data \ d6 \ d6_data.mdf ', move 'd6 _ log' to 'e: \ data \ d6 \ d6_log.ldf' sp_helpdb d6 3. Detach and reconnect to the database ---------------------------------- sp_detach_db 'd 6 'sp_attach_db 'd6 ', 'e: \ data \ d6 \ d6_data.mdf', 'e: \ data \ d6 \ d6_log.ldf' ------------------------------------ sp_detach_db d6 go create database d6 on primary (filename = 'e: \ data \ d6 \ d6_data.mdf ') for attach go defaults 4. Recover the damaged system database ---------------------------------------------------------------------- 1) first back up MASTER and MSDB 2) Stop the SQL service and set the MASTER database Delete or rename the file. In this way, the SQL service cannot be started. 3) restore the system database ----------------------------------------------- (1) restore the system database from the backup if the SQL service can be started. (2) If the SQL service cannot be started, the system database must be rebuilt. Use rebuildm.exe in the SQL folder TOOLS \ binndirectory to recreate the master database. (3) create a backup device that points to the previous backup device. (4) start SQL cd programe files \ microsoft SQL server \ mssql \ binn sqlservr.exe-c-m (5) in single-user mode and restore the master database from the backup. Restore database master from masterbak restore database msdb from disk = 'e: \ bak \ msdb. Bak' after the MASTER is restored, the user database information in the SQL statement is also restored. (6) If the MASTER database is not backed up, use the sp_attach_db command to attach the user database to the new MASTER database.