Friday 19 April 2013

How to script out the database with all objects in the selected database

When you Right click on the database -> Script Database As -> CREATE To New Query Editor Window,

you will get a script that looks similar to this:
USE [master]
GO
/****** Object: Database [AdventureWorks] Script Date: 05/14/2012 18:03:56 ******/
CREATE DATABASE [AdventureWorks] ON PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'D:\MSSQL\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 184256KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'D:\MSSQL\Data\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks_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

which will only create a database, without any objects in the database.
If you want to script the database with all the objects in the database (tables, views, stored procedures, functions…),
Right click on your database ->Tasks ->Generate Scripts
and choose database objects you want to script:

(in SQL Server 2005 check the option at the bottom that says Script all objects in the selected database)
With SQL Server 2008 you can choose to script data as well in the next window of the wizard.
Click Advanced -> Types of data to script -> Schema and data

As the file with data might be very large, you can use SQLCMD to execute the large script which will create database with schema and data:
In the Command Prompt window, type:
sqlcmd -S myServer\instanceName -i D:\myScript.sql

No comments:

Post a Comment