troubleshooting Question

SQL - Create database script with database name as parameter

Avatar of holemania
holemania asked on
SQL
4 Comments2 Solutions559 ViewsLast Modified:
I need help with a create script to create a blank new database.  How can I take the following script and create a parameter for database name so that it replace "DB1" with something I just have to type once as the parameter?  

USE [master]
GO
CREATE DATABASE [DB1] ON  PRIMARY
( NAME = N'DB1_Data', FILENAME = N'D:\Microsoft SQL Server\Data\DB1_Data.MDF' , SIZE = 78784KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'DB1_Log', FILENAME = N'D:\Microsoft SQL Server\Logs\DB1_log.ldf' , SIZE = 768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DB1', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DB1].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DB1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DB1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DB1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DB1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DB1] SET ARITHABORT OFF
GO
ALTER DATABASE [DB1] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DB1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DB1] SET AUTO_SHRINK ON
GO
ALTER DATABASE [DB1] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DB1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DB1] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DB1] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DB1] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DB1] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DB1] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DB1] SET  DISABLE_BROKER
GO
ALTER DATABASE [DB1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DB1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DB1] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DB1] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DB1] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DB1] SET  READ_WRITE
GO
ALTER DATABASE [DB1] SET RECOVERY SIMPLE
GO
ALTER DATABASE [DB1] SET  MULTI_USER
GO
ALTER DATABASE [DB1] SET PAGE_VERIFY TORN_PAGE_DETECTION  
GO
ALTER DATABASE [DB1] SET DB_CHAINING OFF
GO
EXEC [DB1].sys.sp_addextendedproperty @name=N'NOTE', @value=N'TEST DATABASE'
ASKER CERTIFIED SOLUTION
Robert Schutt
Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros