SQLServer2008 database backup restore and data recovery

Under the full recovery model or the bulk-logged recovery model, you must back up the active transaction log (called the tail of the log) before you can restore the database in Sqlservermanagementstudio. For more information, see How to Back up the transaction log (Sqlservermanagementstudio). To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database. If there is no certificate or asymmetric key, the database cannot be restored.

Understanding database backups and transaction log backups

Database backup and log backup is the daily work of database maintenance, and the purpose of the backup is to reduce the loss to a minimum by restoring the database and transaction log files to the nearest time when the database fails or is destroyed.

Database backup

Database backups can be backed up and statement backed up manually

A. Manually backing up the database

1. Right mouse button to select 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 Add option to select the storage path of the database file

Note the filename with suffix. bak, easy to find when recovering

3. You can also select whether to append to an existing backup set or to overwrite all existing backup sets on the Options page, optionally to verify the integrity of the backup (recommended selection), or to compress the backup.

Two. Statement BACKUP database

Use master gobackup the DATABASE [test] to DISK = N ' D:microsoft sql ServerMSSQL10.MSSQLSERVERMSSQLBackuptest.bak ' with Noforma T, noinit, NAME = N ' test-full database backup ', SKIP, Norewind, nounload, STATS = 10GO

Database log Backups

The first thing to note is that the database log backup is based on a full database backup, 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, which is what the next point of this article is about.

A. Manually backing up the database log

1. Right key database-task-backup-Select backup type (transaction log)

2. Point add, add log file backup storage path

3. As with a full database backup, you can choose to overwrite an existing backup set or append to an existing backup set, now overwrite the existing backup set, verify integrity, and then confirm the backup

Two. Statement Backup database transaction log

Backup LOG [test] to DISK = N ' d:test.trn ' with Noformat, INIT, NAME = n ' test-transaction log backup ', SKIP, Norewind, nounload, STATS = 1 0GO

Database restore

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

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

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

2. Click to confirm Restore Database

Database recovery

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

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 shrink will exist shrink log file can not be reduced, This is because there are a lot of active logs that can't be shrunk to use

DBCC loginfo (' Database name ')

We see

A status=0 log that represents a log file that has been backed up to disk;

The status=2 log has not been backed up yet. When we shrink the log file, the shrinking empty

The room is actually

Status=0 space, if the log physical file cannot be reduced, here a

I'm going to see a lot of status=2 records.

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

2. Right key to come out of the database Select "Properties"-"options" to change the recovery model to "simple", and then use the shrink tool can be said log files shrink to very small, shrink remember to say recovery mode to "complete"

You can also use statements to process (dbname is the name of the database you want to shrink, Dbname_log is the logical log name of the database you want to shrink)

Use [master]

Go ALTER DATABASE [dbname] SET recovery simple and no_wait go

ALTER DATABASE [dbname] SET RECOVERY Simplicity--Simple mode

Go

Use [dbname]

Go

DBCC shrinkfile (N ' Dbname_log ', one, truncateonly) go

Use [master]

Go

ALTER DATABASE [dbname] set RECOVERY full and no_wait ALTER DATABASE [dbname] set RECOVERY full

For the first method does not agree to use, first of all, the separation and attachment of the database will sometimes destroy the database, resulting in the database can not be restored, as well as the online database also does not allow separation operations.

The second method is a way to shrink the log file, but this method cannot be used too often, 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 slq2008. And when you need to back up the transaction log, you will be prompted to make a full backup of the database.

For example: You made a full backup last night, and then you also made a log backup (the advance log was not truncated), and then you had a differential backup every hour, and the most recent differential backup point was 14, and if the database was incorrectly modified at this time, You can immediately back up a log file restores the database to any point in time before the log backup starts to the end of the log backup.

If you modify the database schema at this time, truncation log to shrink, then your data can only be restored to the log backup time last night before the date of any point in time, that is, the database changes made today can not be restored, because the log file has been truncated, do not know whether this explanation understand

Because the checkpoint (LSN) of the log file is contiguous, each log backup is incremented on the basis of the last backup, and the LSN range includes the LSN of the database file, and the database file can be rolled back only if the LSN of the log file includes the LSN of the database file.

With a total of three backup files, a full backup, a differential backup, and a log backup, you can observe that the first LSN of the full backup and the last LSN, and checkpoints

The first LSN of the second differential backup file with the last LSN, and the checkpoint, the first LSN of the last log backup, and the last LSN contain the LSN of the previous two backup files, which can be restored to any point in time before the log file is backed up. If the log file does not contain the last LSN of the database file, it cannot be recovered.

Conclusion

Daily backups of the database during database maintenance are required, after all, this is the most effective way to reduce losses, I hope that we are positive comments, out of limited capacity also hope that everyone haihan, the whole piece of writing down very tired, hehe, I hope that my point of view can bring help to everyone.