Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Haden Kingsland (theflyingdba),
This script was written over the past couple of days to fill a whole in the deployment process, whereby databases could be promoted into production under change with an incorrect compatibility settings and some incorrect settings. As we don't live in an ideal world where everything is as it should be, I came up with this, albeit a bit clunky, so that it is run as Step 1 of any deployment where a new database is required. It standardises database creation and also forces a few size/option decisions at time of creation and removes the possibility of lower level compatibility settings.
Usage is as follows...
DECLARE @databasename varchar(400), @filepath varchar(400), @logpath varchar(400), @recoverymodel varchar(11) = NULL, @datamaxsize int = NULL, @logmaxsize int = NULL, @filegrowth int = NULL, @collation varchar(100), @initdatasize int = NULL, @initlogsize int = NULL, @containment varchar(7), @RC int; select @databasename = 'mytestdatabase' -- change this to suit your needs select @filepath = 'S:\some folder\Data\' -- change this to suit your needs select @logpath = 'T:\another folder\Logs\' -- change this to suit your needs select @recoverymodel = 'SIMPLE' -- can be FULL, SIMPLE or NULL (will then default to SIMPLE) select @datamaxsize = NULL -- default to 8192 if NULL select @logmaxsize = NULL -- size is dependant on recovery model. If FULL mode, then will be 30% of the combined max size of both main data files -- this is a discussion point, as logs should always be sized accordingly to fit in the largest index when rebuilt... however 30% of database size is a good starter for 10! select @filegrowth = NULL -- default to 64MB if left as NULL select @collation = 'DEFAULT' -- takes default collation from instance if not specified select @initdatasize = NULL -- 4096 -- if NULL then will default to an initial data file size of 2GB per file select @initlogsize = NULL -- 2048 -- if NULL then will default to an initial log file size of 1GB select @containment = 'DEFAULT' -- if DEFAULT, and SQL Version 2012 or higher, containment will be NONE. Values should be DEFAULT or PARTIAL. -- please read this article... --https://technet.microsoft.com/en-us/library/hh534404.aspx -- before creating a contained database! EXECUTE @RC = dbadmin.dba.usp_generic_create_database @databasename, @filepath, @logpath, @recoverymodel, @datamaxsize, @logmaxsize, @filegrowth, @collation, @initdatasize, @initlogsize, @containment
Finding Default Database
Change Default Database Location
The default database is one of the options when creating a login in SQL Server. This is the...
Steve Jones comments on containers and their suitability for databases.