SQL Server Backup and restore

SQL Server Backup

Recovery mode

The SQL Server Data recovery model is divided into three types: The full recovery model, the bulk-logged recovery model, and the simple recovery model.

Full Recovery model

The default recovery model, which fully records every step of the operational database, uses the full recovery model to restore a database to a specific point in time, which can be the last available backup, a specific date and time transaction.

Bulk-Logged Recovery model

It complements the full recovery model by minimizing logging of bulk operations and saving log file space.

For example, when inserting hundreds of thousands of records in a database, the full recovery model records every insert action, making the log file very large. Under the bulk-logged recovery model, only the necessary actions are logged and all logs are not logged, which improves database performance. However, due to incomplete logging, the data may not be recoverable in the event of a problem.

Therefore, it is generally only when the operation of large amounts of data to the recovery model to the bulk-logged recovery model, after the completion of data processing, the recovery mode is immediately changed back to the full database schema.

Simple Recovery Model

In the simple recovery model, the database automatically deletes inactive logs, thus simplifying the restore of the backup, but because there is no log backup, it cannot be recovered to a point in time of failure. Typically, this pattern is only used for databases that do not require a high level of database security. And the database can only make full and differential backups, and cannot use transaction log backups.

Database backup

SQL Server provides four backup modes: Full backup, differential backup, transaction log backup, file and filegroup backup.

Full backup

Backs up all the contents of the entire database, including the transaction log. This type of backup requires a large storage space to store the backup file, and it takes longer to restore a backup file when restoring the database.

such as: January 1, 2015 8 o'clock a full backup, then restore the data is restored to January 1, 2015 8 o'clock data.

Differential backup

A differential backup is a complement to a full backup that backs up only the data that was changed since the last full backup. Compared to full backups, the amount of data for a differential backup is smaller than full data backup, and backups are faster than full backups. Therefore, the differential backup is usually used for the backup mode of the seat. When restoring data, restore the previous full backup before restoring the last differential backup.

For example: January 1, 2015 8 o'clock a full backup was performed, and differential backups were performed on January 2 and January 3 respectively, so the January 2 recorded 1-2 of the data, and the January 3 was 1-3. Therefore, when restoring, if you want to go back to the 3rd state, you need to restore the full backup first and then restore the differential backup of January 3.

Transaction log Backups

Transaction log backups only back up the contents of the transaction log, and the transaction log records all the changes to the database after the last full or transaction log backup. The transaction log records the changes to the database over a period of time, so a full backup must be done before the transaction log backup. Similar to a differential backup, a transaction log backup produces a smaller file size and takes less time, but when you restore a database, you restore each transaction log backup in turn, instead of the last one, in addition to restoring the full backup line.

For example: January 1, 2015 8 o'clock a full backup is made, and all changes made to the database are stored in a transaction log backup in batches of time, which can be restored to a point in time, using SSMS to automatically complete the relevant time chain.

File and filegroup Backups

This can be used if you created multiple database files or filegroups for the database when you created the database. Using file and filegroup backups, you can back up only some of the files in the database, which is very effective when the database files are very large, and because you can back up the database several times each time you back up one or several files, avoid large database backups for too long. Also, when a file is damaged, you can restore only the damaged files or filegroups section.

Make a full backup

After the backup is complete, go to the selected directory to view the files.

--Full Backup SELECT COUNT (*) from news--79647

Insert test data at this point and perform a differential backup

--Insert test data declare @index int = 1while (@index <100) begin    INSERT INTO News (Title,content,datetime) VALUES (' title ' + CAST (@index as varchar), ' content ', GETDATE ())    Set @index = @index +1endselect count (*) from news--79746

Viewing the directory, the file has become larger.

Continue inserting test data and performing transaction log backups

--Insert test data declare @index int = 1while (@index <100) begin    INSERT INTO News (Title,content,datetime) VALUES (' title ' + CAST (@index as varchar), ' content ', GETDATE ())    Set @index = @index +1endselect count (*) from news--79845

The file under the catalogue is getting bigger again, I can't.

Delete all data at this time

--Deleting data Delete news--0

SQL Server Restore

When restoring a database, you must restore it in this order if you must restore a full backup, a differential backup, and a transaction log backup.

When you restore a database, the database is in an unusable state, so you can add subsequent differences and transaction log backups.

With the SSMs tool recovery, a full backup chain can be automatically identified to restore all backups based on the specified content, and the database is available after the restore.

Select COUNT (*) from news--79845

Executes the SQL statement, and the data is restored to the last valid transaction log backup.

About Backup There is a small thing, backup settings , you can set the physical path, a logical name, backup time to choose this name, not to find the path.

The only one by one-point function is to look inside the detailed backup information

Category: SQL Server

SQL Server Backup and restore