There seems to be a little bit of confusion on how to script out the database. The correct answer is of course: just run all the scripts you have in source control 🙂

So for those who do not use/have source control I will show you how to do it

Logically you would think that this would be the way: Right click on the database –> Script Database As –> CREATE To and then pick your choice. See also image below

What that will do is just create the database and not much else, here is what the script might look like

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
USE [master]
GO
 
/****** Object:  Database [AdventureWorks]    Script Date: 06/30/2009 15:04:19 ******/
CREATE DATABASE [AdventureWorks] ON  PRIMARY 
( NAME = N'AdventureWorks_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON 
( NAME = N'AdventureWorks_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO
 
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
GO
 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
USE [master]
GO

/****** Object:  Database [AdventureWorks]    Script Date: 06/30/2009 15:04:19 ******/
CREATE DATABASE [AdventureWorks] ON  PRIMARY 
( NAME = N'AdventureWorks_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON 
( NAME = N'AdventureWorks_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO

ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

What you have to do is actually this: Right click on your database–>Tasks–>Generate Scripts

Select the database you want to script and make sure you check the option at the bottom that says Script all objects in the selected database. See image below

That is all, click next a couple of times and review your options and you are set

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum