SQL Server Recovery Models

In SQL Server Enterprise Edition the model database is set to the full recovery mode. Because all our databases are basically a copy of the model database, the recovery model for the databases we create is also full. You can change the model database’s recovery model to another type if you want all new databases you create to start with another recovery model.

To change the recovery model for a database, in Enterprise Manager Right click the database, select properties, and then choose the options tab. You can also use the ALTER DATABASE statement to change the recovery model.

One last topic in the database recovery models section is the change from one model to another. Unlike previous versions of SQL server, SQL Server 2000 can switch between full and bulk_logged recovery models as needed. For example if you perform bulk operations infrequently, you can use the full recovery model and switch to bulk_logged as needed to have the bulk operations perform faster. The log backup will be larger and take longer however when you do this.

Switching between the simple recovery model and another recovery model is not so “simple.” In order to have the change take place you will need to make a full backup of your database. You should only use the simple recovery model for development databases, production databases should use the full and bulk_logged recovery models.

Making a backup

To make a backup manually, you have plenty of options. We are going to look at three ways you can create a backup.

Right Click the database you would like to backup in Enterprise Manager, select All Tasks, and then click Backup Database…

You can also use the Create Database Backup Wizard by selecting the Tools menu in Enterprise Manager, select Wizards…, expand Management, and select Backup Wizard.

The third way to backup your database is by using the BACKUP statement. For more information on the BACKUP statement see the SQL Server books online.

For more information on backups, take a look at this link.