How to script out the database with all objects in the selected database
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