Backup, Recovery, and System Availability

This chapter first covers two of the most important tasks related to system administration: backup and recovery. Backup refers to the process of making copies of the database(s) and/or transaction logs to separate media that can later be used for recovery, if necessary. Recovery is the process of using the backup media to replace uncommitted,… [Continue Reading]

Database backup is the process of dumping data (from a database, a transaction log, or a file) into backup devices that the system creates and maintains. A backup device can be a disk file or a tape. The Database Engine provides both static and dynamic backups. Static backup means that during the backup process, the… [Continue Reading]

You can perform backup operations using the following: Transact-SQL statements SQL Server Management Studio All types of backup operations can be executed using two Transact-SQL statements: BACKUP DATABASE BACKUP LOG Before these two Transact-SQL statements are described, the existing types of backup devices will be explained. Types of Backup Devices The Database Engine allows you… [Continue Reading]

Before you can perform a database or transaction log backup, you must specify (or create) backup devices. SQL Server Management Studio allows you to create disk devices and tape devices in a similar manner. In both cases, expand the server, expand Server Objects, right-click Backup Devices, and choose New Backup Device. In the Backup Device… [Continue Reading]

The following databases should be backed up regularly: The master database All production databases Backing Up the master Database The master database is the most important system database because it contains information about all of the databases in the system. Therefore, you should back up the master database on a regular basis. Additionally, you should… [Continue Reading]

Whenever a transaction is submitted for execution, the Database Engine is responsible either for executing the transaction completely and recording its changes permanently in the database or for guaranteeing that the transaction has no effect at all on the database. This approach ensures that the database is consistent in case of a failure, because failures… [Continue Reading]

After executing the BACKUP statement, the selected device (tape or disk) contains all data of the object you chose to back up. The stored data is called a backup set. Before you start a recovery process, you should be sure that The backup set contains the data you want to restore The backup set is… [Continue Reading]

All restore operations can be executed using two Transact-SQL statements: RESTORE DATABASE RESTORE LOG The RESTORE DATABASE statement is used to perform the restore process for a database. The general syntax of this statement is db_name is the name of the database that will be restored. (The name of the database can be supplied using… [Continue Reading]

To restore a database from a full database backup, expand the server, choose Databases, right-click the database, and choose Tasks | Restore | Database. The Restore Database dialog box appears (see Figure 16-4). On the General page, select databases to which and from which you want to restore. Then check the backup set that you… [Continue Reading]

The corruption of the master system database can be devastating for the whole system because it comprises all system tables that are necessary to work with the database system. The restore process for the master database is quite different from the same process for user-defined databases. A damaged master database makes itself known through different… [Continue Reading]