Generate script for objects along with data using Microsoft SQL Server Database Publishing Wizard

Last week, we had a request from the Application Team to generate the DDL script for all objects on a Production database. Since the number of objects on that database was quite high (more than 100,000), the Generate Script wizard in SSMS stopped responding. Later on we informed the Application Team to go for backup/restore the database, instead of waiting forever for the DDL script to be generated.

While I was looking out for better options to script out the database, I stumbled upon the Microsoft SQL Server Database Publishing Wizard tool by Microsoft. This is a good utility to generate scripts for exporting a database (SQL Server 2000 & 2005) to a file. This generates the T-SQL commands for the schema and data inside the tables. Here are some of the screenshots of the Microsoft SQL Server Database Publishing Wizard.

The script generated by this tool looks like the one below.

/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
DROP TABLE [dbo].[TimeTable]
GO
/****** Object:  Table [dbo].[TimeTable]    Script Date: 03/19/2011 12:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimeTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TimeTable](
	[StartTime] [datetime] NOT NULL,
	[stoptime] [datetime] NULL
)
END
GO

INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	VALUES
	(CAST(0x00009D1800E8F0C2 AS DateTime), CAST(0x00009D1800E8C4AF AS DateTime))
INSERT [dbo].[TimeTable] ([StartTime], [stoptime], [downtime])
	VALUES
	(CAST(0x00009D1800F3E452 AS DateTime), CAST(0x00009D1800F3B415 AS DateTime))

Since the data export progress is not very elaborate, while exporting huge databases one gets the feeling that the tool is hung. However, this utility does a good job while exporting data for relatively small databases and does it quickly.

2 thoughts on “Generate script for objects along with data using Microsoft SQL Server Database Publishing Wizard

  1. Pingback: Cannot open MSI file | Windows cannot access the specified file error | SQL Server DBA Diaries of Pradeep Adiga

Leave a Reply