I am using SQL Server 2008 R2 and creating databases through continuous deployment.
In our system, the default 1Mb/10% autogrowth settings in SQL Server work badly with our data. Particularly as we have a legacy application that prevents us from changing the schema much. We would like the database settings to be configured at the instance level so we can change it for staged deployments.
I have read in several places that default settings for a new db are based on the settings for 'model', but it appears this only works by clicking new database in SQL Management studio UI and not from a script e.g. CREATE DATABASE [MyDb].
msdn.microsoft.com/en-us/library/ms186388(v=sql.105).aspx
sqlservercentral.com/Forums/Topic1065073-391-1.aspx
Has anyone actually got this to work with a create script? Is there another way I can set autogrowth per server instance?
Best Answer
For SQL Server 2005+ You can use the following:
This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.
SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:
If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:
This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).
I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.
Good luck,
Roi