Have you ever encountered a situation where you had to manually change the recovery model of a newly created database in SQL Server? If so, you’re not alone. Many database administrators face this frustration and are looking for a more efficient solution. In this blog post, we will explore a simple and effective method to set the default recovery model for all new databases.
Recently, I received an email from a reader who was struggling with this exact issue. They were creating new databases using the T-SQL code CREATE DATABASE dbname, which resulted in all databases being created with the Simple Recovery Model. They wanted to change the default recovery model to Full, but also needed the ability to whitelist certain databases to use the Simple Recovery Model.
While the reader suggested a few methods, such as creating a policy or using triggers, the most efficient solution is actually much simpler. By modifying the Model database, we can ensure that all new databases inherit the desired recovery model.
Here’s how you can set the default recovery model in SQL Server:
- Open SQL Server Management Studio (SSMS) and navigate to the Model database.
- Right-click on the Model database and select “Properties”.
- In the “Options” tab, locate the “Recovery Model” dropdown and select “Full”.
That’s it! Now, every newly created database will have the Full Recovery Model by default.
It’s important to note that modifying the Model database will not affect any databases that were created before making these changes. Only new databases will inherit the modified recovery model.
While creating a policy or using triggers can also be effective methods, modifying the Model database is the simplest and most efficient solution for the reader’s specific requirements.
However, it’s worth mentioning that the Model database should not be modified unnecessarily. It is recommended to only make changes that are essential for all new databases. If you need to create an object that should exist in all databases, consider creating a separate database specifically for maintenance tasks.
I hope this blog post has provided you with a valuable solution to setting the default recovery model in SQL Server. If you have any further questions or topics you’d like me to cover, please let me know in the comments below.