Change SQL Server 2000 Recovery Mode
In Rare Instances, You May Wish to Change the Database Recovery Mode
Description:
You should probably never have to change the recovery model for a database, the recovery model is something that should be planned for during database design. This exercise shows you how to easily change the recovery model of your database and also reminds you to get a full database backup after switching modes. The full backup is so that transaction logs (if used) will have a starting point to come off of in the new model if you are transitioning from the simple recovery model to the bulk-logged or full recovery models.
(back to top)Requirements:
As with all of the exercises on this site, do not perform this exercise on a production server. Perform this exercise in a closed development environment
(back to top)Setup:
None.
(back to top)GUI Steps:
1) In Enterprise Manager, right click on the "Northwind" database and choose properties.
2) From the Options tab, select your recovery model from the drop down list in the recovery section. When you have completed selecting a recovery model, click the "OK" button. Your next action should be to get a full backup of the database.
T-SQL Steps:
1) In Query Analyzer, change the recovery model to full by using the following ALTER DATABASE statement. Note that you can also change your recovery model into either of the other two options. Be sure to get a database backup after changing the recovery model.
