How do I specify a recovery model (full, simple or bulk-logged) when creating a database in T-SQL code?

Question

How do I specify a recovery model (full, simple or bulk-logged) when creating a database in a CREATE DATABASE query in T-SQL code?

Answer 1

Using TSQL, it's part of ALTER DATABASE not CREATE DATABASE.

CREATE DATABASE MyDatabase;
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

However, it will inherit the setting from the Model system database, so change that to what you want if it's a common setting.

http://msdn.microsoft.com/en-us/library/ms176061.aspx

http://msdn.microsoft.com/en-us/library/ms190249.aspx