Ms SQL Server backups

Database backup and recovery
I. Back up the database

1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console.
2. SQL Server group --> double-click to open your server --> double-click to open the database directory
3. Select Your Database Name (for example, Forum database Forum) --> click the tool in the menu above --> select backup database
4. Select full backup as the backup option. If there is a path or name for the backup in the target project, click Delete and then add. If there is no path or name, select Add directly, specify the path and file name, click OK to return to the backup window, and click OK to back up

2. Restore the database

1. Open SQL Enterprise Manager and click Microsoft SQL server in the root directory of the console.
2. SQL Server group --> double-click to open your server --> click the new database icon in the icon bar to retrieve the name of the new database.
3. Click the name of the newly created database --> click the tool in the menu above --> select recover Database
4. In the displayed window, select "restore from device"> "select device"> "add"> "select your backup file name"> "add" and click "OK" to return, at this time, the device column should display the database backup file name you just selected. The default backup number is 1. (If you have backed up the same file multiple times, click View next to the backup number, select the latest backup in the check box and click OK) --> then click the option button next to the general button
5. In the displayed window, select force restore on the existing database, and select the option to enable the database to continue running but not to restore other transaction logs in the recovery completion status. To restore the database file in the middle of the window, you need to set it according to your SQL installation (you can also specify your own directory). The logical file name does not need to be changed, the file name to be moved to the physical server must be changed based on the recovered machine. For example, if your SQL database is installed in D: "Program Files" Microsoft SQL Server "MSSQL" data, then modify the directory of the recovered machine according to the change, and the final file name should be changed to your current database name (for example, zw0001.mdf, and zw0002, change it to zw0002.mdf). The log and data files must be modified in this way (the log file name is. LDF at the end of the directory. You can set the recovery directory as needed, provided that the directory must exist (for example, you can specify D: "sqldata" zw0002.mdf or D: "sqldata" zw0002.ldf ), otherwise, an error is returned.
6. After the modification is complete, click "OK" below to restore. a progress bar is displayed, prompting the recovery progress. After the restoration is complete, the system will automatically prompt "success". If an error is reported in the middle, please record the relevant error content and ask people familiar with SQL operations. The common error is nothing more than a directory error, repeated file names, incorrect file names, insufficient space, or database in use errors, you can close all SQL windows and re-open them to restore the database. If an error is prompted, stop the SQL Service and restart it, as for the above other errors, you can change the content of the errors to restore them.

3. Set daily automatic Database Backup

1. Open the Enterprise Manager and click Microsoft SQL Server> SQL Server group in the root directory of the console. Double-click to open your server.
2. Click Tools in the menu above --> select Database Maintenance Scheduler
3. Next, select the data to be automatically backed up --> next, update the data optimization information. You do not need to select here --> next, check data integrity.
4. Next, specify the database maintenance plan. The default one-week backup is performed. Click "change" and select "backup every day". Click "OK ".
5. Next, specify the backup disk directory and select the specified directory. For example, you can create a new directory on disk D, for example, D: "databak, and then select to use this directory here, if you have a large number of databases, it is best to create a subdirectory for each database, and then select the number of days before the deletion of the backup, generally set to 4-7 days, depending on your specific backup requirements, the backup file extension is generally Bak and the default is used.
6. Specify the transaction log backup plan in the next step. Check whether you need to select the report to be generated in the next step. Generally, do not select the report, it is best to use the default option --> next step to complete
7. After completion, the system will probably prompt that the SQL Server Agent service is not started. First, click confirm to complete the plan settings, find the SQL green icon in the rightmost status bar of the desktop, and double-click it, select SQL Server Agent from the service list, and click the run arrow to automatically start the service when the OS is started.
8. At this time, the database plan has been successfully run and will be automatically backed up according to the above settings.

Auto: http://blog.csdn.net/hzyishion/archive/2007/02/10/1507031.aspx

SQL backup
Bytes -----------------------------------------------------------------------------------
1. SQL database recovery model
Bytes -----------------------------------------------------------------------------------
1) completely recover the Model
-----------------
(1) data files and log files of the database must be backed up during Backup
(2) restore the database by using copies of the data files backed up by the database and all log information.
(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) data files and log files of the database must be backed up during Backup
(2) restore the database by using copies of the data files backed up by the database and all log information.
(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 the data files are backed up 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
----------------------------------------------------------------------------------
2. Backup Devices
----------------------------------------------------------------------------------
1) physical device
---------------------------
Disk: Supports local or network backup.
Tape: supports tape drive backup
Name pipe: third-party backup software supported
---------------------------
2) Logical Device
---------------------------
Permanent backup file: it can be used repeatedly and should be created before backup.
Temporary backup file: used for one-time backup and created during Backup.
-------------------------------------------------
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'
Bytes ------------------------------------------------------------------------------------------------
Exec sp_addumpdevice 'disk', 'bak4 ',' "" sv2 "backup" bak4.bak '-- creates a permanent network disk backup device.
Exec sp_addumpdevice 'disk', 'bak5 ',' "" sv2 "backup" bak5.bak'
Bytes ------------------------------------------------------------------------------------------------
Exec sp_dropdevice 'bak5 '-- delete the backup device
Bytes ------------------------------------------------------------------------------------------------
Backup database D3 to bak3 -- back up the database to the backup device
Backup database D4 to bak4
Bytes ------------------------------------------------------------------------------------------------
Restore headeronly from bak2 -- view the content in the backup device
Bytes ------------------------------------------------------------------------------------------------
Backup database D3 to disk = 'e: "back_file" d3.bak '-- back up the database to a temporary backup file
Backup database D4 to disk = 'e: "back_file" d4.bak'
Bytes ------------------------------------------------------------------------------------------------
Restore database D3 from bak3 -- Restore database from backup device
Restore database D4 from disk = 'e: "back_file" d4.bak '-- restore the database from the backup file
Bytes ------------------------------------------------------------------------------------------------
3. Use multiple backup files for 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.
----------------------------------------------------------------------
Backup database D4 to bak4, bak5, bak6 with medianame = 'bak456', format -- back up D4 and form a media set

Backup database D3 to bak4 -- failed because files in the media set must be used at the same time

Backup database D3 to bak4, bak5, bak6 -- success. Back D3 to media set.

Restore headeronly from bak4, bak5, bak6 -- view the backup content in the media set
Bytes ------------------------------------------------------------------------------------------------
Backup database D4 to bak4 with medianame = 'bak4 ', format -- Re-divide the media set
Backup database D3 to bak5, bak6 with medianame = 'bak56', format
Bytes -----------------------------------------------------------------------------------------------
Backup database D1 to bak1 with init -- with init rewrite the backup device content

Backup database D2 to bak1 with noinit -- with noinit Append content to the backup device

Restore headeronly from bak1
Bytes -----------------------------------------------------------------------------------------------
4. Backup Method
Bytes ----------------------------------------------------------------------------------------------
1) Full backup
-------------------------------------------
(1) is the benchmark for backup. 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

Backup database D1 to bak1 with noinit
Bytes -----------------------------------------------------------------------------------------------
2) Differential backup
---------------------------------------------
(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. Full backup should be performed 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 _ diff3'

Insert B1 values (3, 'C ')

Backup database D2 to bak2 with differential, name = 'd2 _ diff4'

Restore headeronly from bak2
Bytes ----------------------------------------------------------------------------------------------
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. Full backup should be performed during the first backup.

Create Table B1 (C1 int not null, C2 char (10) Not null)

Backup log D3 to bak3 with name = 'd3 _ log1'

Insert B1 values (1, 'A ')

Backup log D3 to bak3 with name = 'd3 _ log2'

Insert B1 values (2, 'B ')

Backup log D3 to bak3 with name = 'd3 _ log3'

Insert B1 values (3, 'C ')

Backup log D3 to bak3 with name = 'd3 _ log4'

Restore headeronly from bak3
Bytes -----------------------------------------------------------------------------------------------
Create Table B1 (C1 int not null, C2 char (10) Not null) -- full + Log + diff

Backup log D4 to bak4 with name = 'd4 _ log1'

Insert B1 values (1, 'A ')

Backup log D4 to bak4 with name = 'd4 _ log2'

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 name = 'd4 _ log3'

Insert B1 values (4, 'D ')

Backup log D4 to bak4 with name = 'd4 _ log4'

Insert B1 values (5, 'D ')

Backup database D4 to bak4 with differential, name = 'd4 _ diff2'

Restore headeronly from bak4
Bytes -----------------------------------------------------------------------------------------------
Log clearing
-----------------------------------------
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, 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

Sp_dboption 'd3 ', 'trunc. Log On chkpt.', 'true' -- set to automatically clear database logs
Sp_dboption 'd3 ', 'trunc. Log On chkpt.', 'false' -- remove the option to automatically clear database logs
Bytes -----------------------------------------------------------------------------------------------
Backup log D4 with truncate_only -- set to clear logs when D4 logs are full and clear records
Bytes -----------------------------------------------------------------------------------------------
Backup log D4 with no_log -- set to clear logs when D4 logs are full, but do not clear records
Bytes -----------------------------------------------------------------------------------------------
Backup log D4 to bak4 with no_truncate -- immediately back up the current database log when the D4 database is damaged (DEMO)
--------
Use no_truncate
Completely + modify 1 + difference + modify 2 + difference + modify 3 + stop SQL, delete database data files + restart SQL

Backup log no_truncate

Restore again, which can be restored to modification 3
Bytes -----------------------------------------------------------------------------------------------
4) file/File Group Backup
------------------------------------------------------------------
(1) It 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 -- add d5_data3 to file group FG2

Alter database D5 add filegroup FG3 -- add file group FG3

Alter database d5 -- add d5_data4 to the FG2 File Group
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] -- place different tables in 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) Not null) on FG3
Bytes ----------------------------------------------------------------------------------------------
Backup database D5 to bak5 with init, name = 'd5 _ full' -- filegroup backup

Backup database D5 filegroup = 'primary' to bak5 with name = 'd5 _ primary'
Backup log D5 to bak5 with name = 'd5 _ log1'

Backup database D5 filegroup = 'fg2 'to bak5 with name = 'd5 _ FG2'
Backup log D5 to bak5 with name = 'd5 _ log2'

Backup database D5 filegroup = 'fg3' to bak5 with name = 'd5 _ fg3'
Backup log D5 to bak5 with name = 'd5 _ log3'
Bytes ----------------------------------------------------------------------------------------------
Backup database D5 to bak6 with init, name = 'd5 _ full' -- file backup

Backup database D5 file = 'd5 _ data1 'to bak6 with name = 'd5 _ data1'
Backup log D5 to bak6 with name = 'd5 _ log1'

Backup database D5 file = 'd5 _ data2 'to bak6 with name = 'd5 _ data2'
Backup log D5 to bak6 with name = 'd5 _ log2'

Backup database D5 file = 'd5 _ data3 'to bak6 with name = 'd5 _ data3'
Backup log D5 to bak6 with name = 'd5 _ log3'

Backup database D5 file = 'd5 _ data4 'to bak6 with name = 'd5 _ data4'
Backup log D5 to bak6 with name = 'd5 _ log4'

Restore headeronly from bak6
========================================================== ========================================================== ====================
SQL Restoration
========================================================== ========================================================== ====================
1. Verify backup
------------------------------------------------------------
Restore headeronly from bak3

Restore filelistonly from bak3 with file = 1

Restore labelonly from bak3

Restore verifyonly from bak3
Bytes -----------------------------------------------------------------------------------------------
2. Restore from backup
Bytes -----------------------------------------------------------------------------------------------
Restore headeronly from bak1
Restore database D1 from bak1 with file = 2 -- restore from full backup
Bytes -----------------------------------------------------------------------------------------------
Restore headeronly from bak2 -- restore from differential backup
Restore database D2 from bak2 with file = 1, norecovery
Restore database D2 from bak2 with file = 5, recovery
Bytes -----------------------------------------------------------------------------------------------
Restore headeronly from bak3 -- restore from log backup
Restore database D3 from bak3 with file = 1, 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
Bytes -----------------------------------------------------------------------------------------------
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'
Bytes -----------------------------------------------------------------------------------------------
Restore database D5 filegroup = '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
Bytes -----------------------------------------------------------------------------------------------
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 with file = 9, recovery
Bytes -----------------------------------------------------------------------------------------------
Restore database D5 from bak6 with Replace -- delete an existing database and recreate the database from the backup
Bytes -----------------------------------------------------------------------------------------------
Create Database D6 -- move to move the database file to a 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,
Filename = 'e: "Program Files" Microsoft SQL Server "MSSQL" data "d6_log.ldf ',
Size = 2 MB)
Go
Backup database 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
Bytes -----------------------------------------------------------------------------------------------
3. Separate and reconnect to the database
--------------------------------------
Sp_detach_db 'd6'
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
Bytes -----------------------------------------------------------------------------------------------
4. Recover the damaged system database
Bytes -----------------------------------------------------------------------------------------------
1) Back up Master and MSDB
2) Stop the SQL Service and delete or rename the master database file. In this way, the SQL service cannot be started.
3) restore the system database
-----------------------------------------------
(1) If the SQL service can be started, the system database will be restored from the backup.
(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 in Single User Mode
CD programe Files "Microsoft SQL Server" MSSQL "binn
Sqlservr.exe-C-m
(5) query analyzer to restore the master database from the backup.
Restore database master from masterbak
Restore database MSDB from disk = 'e: "Bak" MSDB. Bak'
After the master node 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.
Bytes -----------------------------------------------------------------------------------------------
5. implement automatic backup (set the SQLServerAgent service to auto start and start the service)

Since: http://www.80design.net/article.asp? Id = 489