Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I want to create a SQL 2005 database with the following options:

  1. SIMPLE RECOVERY
  2. AUTO_SHRINK ON
  3. AUTO_CLOSE ON

What is the command to do this? The docs on Microsofts site are somewhat vague on this issue. They say:

ALTER DATABASE
{
  SET < optionspec > [ ,...n ] [ WITH < termination > ] 
}

whatever that means...

I have tried this, which fails:

CREATE DATABASE TESTX SET RECOVERY SIMPLE, AUTO_CLOSE ON;  

and I tried something like this, which failed also:

ALTER DATABASE TESTX
{
    AUTO_CLOSE ON  
  | AUTO_CREATE_STATISTICS ON 
  | AUTO_SHRINK ON 
}
share|improve this question
    
Not sure why this got a downvote. Seems decent enough. –  Wesley Mar 3 '10 at 3:17
    
You might want to leave your comment under Squillman's post so that he gets an alert and can then answer your question. –  Wesley Mar 4 '10 at 15:21

1 Answer 1

up vote 0 down vote accepted

Something like this should do it for you (change the initial file sizes as desired):

CREATE DATABASE [TESTX] ON  PRIMARY 
( NAME = N'TESTX', FILENAME = N'C:\Path\To\Data\TESTX_Data.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TESTX_log', FILENAME = N'C:\Path\To\Logs\Blah_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TESTX] SET AUTO_CLOSE ON 
GO

ALTER DATABASE [TESTX] SET AUTO_SHRINK ON 
GO

I'm guessing that this is for a dev database since you want AUTO_SHRINK and AUTO_CLOSE on. However, if I'm guessing wrong and this is for a production system then it's HIGHLY recommended you leave those options off as they are very detrimental to the performance of your database.

EDIT:
In response to your comment - no, you have to create the database and then alter it with your options. Have a look at the Books Online entry for CREATE DATABASE, it gives the complete syntax for this statement. You can't set database options with CREATE DATABASE. All of database options are set with ALTER DATABASE.

share|improve this answer
    
The answer seems to be that the database needs to be created first and then you do alter statements afterwards. There isn't a way to do it in one command, or not that I can tell. Can anyone validate this fact? –  djangofan Mar 5 '10 at 0:58
1  
@djangofan: see my edit. –  squillman Mar 5 '10 at 3:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.