MSSQL2008 database backup restore and data recovery

Original: MSSQL2008 database backup restore and data recovery

Preface

Always wanted to write an article on database backup and recovery, but limited to the database based on the ability of the limited fear of not enough accurate expression, the final thought for a long time or decided to write some of their own understanding for reference, but also in order to repay themselves; If there are said in the wrong place also hope that we put forward timely, good timely change not wrong guide others.

Understanding database backups and transaction log backups

Database backup and log backup are the daily tasks of database maintenance, and the purpose of backup is to restore the loss to the lowest point based on the backup database and transaction log files when the database fails or is compromised.

Database backup

Database backups can be manually backed up and statement backed up

I. Manually backing up the database

1. Right-click on the database you want to back up-tasks-backup

On the General Options page you can choose whether the backup type is a full database backup or a differential database backup

2. Click the Add option to select the storage path for the database file.

Note the filename is appended with the suffix. bak to facilitate recovery when searching

3. You can also append to an existing backup set on the Options page, overwrite all existing backup sets, select Backup validation integrity (recommended), and choose whether to compress backups, etc.

Two. Statement BACKUP database

 UseMasterGoBACKUP DATABASE [Test]  to  DISK =N'D:\Microsoft SQL Server\mssql10. Mssqlserver\mssql\backup\test.bak'  withNoformat, Noinit, NAME=N'test-full Database backup', SKIP, Norewind, Nounload, STATS= TenGO

Database log Backups

The first thing to note is that the backup of the database log is based on a full backup of the database, which means that you first have to make a full backup of the database before you back up the database log, because it involves sticking to the checkpoint LSN, This is the next point in this article.

I. Manually backing up the database log

1. Right-click Database-Tasks-backup-Select the type of backup (transaction log)

2. Click Add, add log file backup storage path

3. As with a full database backup, you can also choose to overwrite an existing backup set or append to an existing backup set, which now overwrites the existing backup set, verifies the integrity, and then confirms the backup

Two. Statement back up the database transaction log

BACKUP LOG [test]  to  DISK = n'D:\test.trn' with Noformat, INIT,  = n'  test-transaction log  backup ', SKIP, Norewind, nounload,  =tenGO

Database restore

Right-click Database-Restore Database-Add the database file path that needs to be restored

In the Restore source option you can select ' Source database ', ' source device '. 1. Selecting the Source database tool automatically displays some of the backups that were before the database, and then directly selects the database backup set that needs to be restored.

2. Select the source device click on the following ... to add the database files that need to be restored

2. Click Confirm to restore the database

Database recovery

The prerequisite for database recovery is 1. A full database backup 2. Transaction log backups that contain this full database backup 3. There can also be several differential backups between a full backup

For database maintenance space is always a headache, especially for large databases, the daily log file growth is huge, many database administrators will periodically shrink the database log files, but often shrinkage will exist in the shrinking of the log file or can not be reduced, This is because there are a lot of active logs that cannot be shrunk.

DBCC Loginfo (' database name ')    We seea log of status=0 , which represents a log file that has been backed up to disk andthe log for status=2 has not been backed up. When we shrink the log file, the shrinking space is actually the status=0 of the space, if the log physical files can not be reduced, there will be a lot of status to see  2 of records

WORKAROUND: 1. You can detach the database that you want to shrink, then manually delete the log file, and then attach the database, and the database will produce a very small log file (this method is not recommended)

2. Right-click the database you want to choose "Properties"-"options" to change the recovery mode to "simple", and then use the Shrink tool can tell the log file shrink to very small, after shrinking remember to say recovery mode to "full"

You can also use statements for processing (dbname is the name of the database you want to shrink, and dbname_log is the logical log names of the database you want to shrink)

 Use [Master]    GO    ALTER DATABASE [dbname] SETRECOVERY Simple withno_waitGO    ALTER DATABASE [dbname] SETRECOVERY Simple--Simple Mode    GO     Use [dbname]    GO    DBCCShrinkfile (N'Dbname_log', One, Truncateonly)GO     Use [Master]    GO    ALTER DATABASE [dbname] SETRECOVERY Full  withno_waitALTER DATABASE [dbname] SETRECOVERY Full

For the first method does not agree with the use of the first for the database separation and attach can sometimes destroy the database, resulting in the database can not be restored, there is also for the online database is not allowed to separate operations.

For the second method, one method of shrinking the log file is slq2008, but this method cannot be used too frequently, because changes to the database recovery model truncate the transaction log file, so that the checkpoint cannot contain the database file when the transaction log file is used for recovery. And when you want to back up the transaction log, you will be prompted to make a full backup of the database.

For example, you did a full backup last night, and then you also made a log backup (advance log is not truncated), then you have a differential backup every hour, the most recent differential backup point of time is 14 points, if the database error modifies the data at this time, You can immediately back up a log file to restore the database to a log backup at any point in time before the end of the log backup.

If you modify the database mode at this time, truncate the log to shrink, then your data can only be restored to last night backup of the log backup time before the point of time, that is, the database changes made today can no longer be restored, because the log file has been truncated, do not know whether the explanation is clear

Because the checkpoint (LSN) of the log file is contiguous, each log backup is incremented on the basis of the last backup, the LSN scope also includes the LSN of the database file, and only the LSN of the log file includes the LSN of the database file to roll back the database file.

There are three backup files in total, one full backup, one differential backup, and one log backup.

You can observe the first LSN of the full backup and the last LSN and checkpoint (from 05700064 to 08500001).

The first LSN of the second differential backup file with the last LSN and checkpoint (from 09300034 to 10800001)

The first LSN and last LSN of the last log backup contains the LSN (from 05700064 to 10800001) of the previous two backup files, which allows the database to revert to any point in time before the log file backup. If the log file does not contain the last LSN of the database file, it cannot be recovered.

Conclusion

In the database maintenance process for the daily backup of the database is necessary, after all, this is the most effective way to reduce the loss, I hope you actively comment, I hope that my point of view can bring you help.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

MSSQL2008 database backup restore and data recovery