This article describes how you can script a Microsoft SQL Server database. This script holds the complete database structure and all objects in the database, such as functions and Stored Procedures.

If you change a database often, it is useful to have a backup of the structure of the database and of all objects in it. You can use the Stored Procedure below for this. It creates a database script of the database you give it. The name of the script becomes “Naam_Database_Datum_RandomNummer”.

SQL
CREATE PROCEDURE [dbo].[sp_GenDBscript] @vcDatabase as Varchar(50)
AS
DECLARE @vcPath            as Varchar(150)
DECLARE @vcCMD            as Varchar(150)
DECLARE @vcParam        as Varchar(150)
DECLARE @vcTotcmd        as Varchar(250)
DECLARE @vcDate            as Varchar(10)
DECLARE @vcTextPath        as Varchar(150)
DECLARE @vcTextFile        as Varchar(150)
DECLARE @vcMkdirStr        as Varchar(150)
DECLARE @vcTime            as Varchar(150)
DECLARE @iRC            as Int


Set @vcTime = REPLACE(Convert(varchar,getdate(), 114),':','')
SET @vcDate = Convert(varchar, getdate(), 112)
SET @vcTextPath = 'E:SQLSCRIPTS' + @vcDatabase
SET @vcTextFile = @vcDatabase + '_' + @vcDate + '_' + @vcTime + '.sql'
SET @vcMkdirStr = 'mkdir ' + @vcTextPath


--checking if dir exist, If it doesnt exist it creates it
EXEC @iRC = master.dbo.xp_cmdshell @vcMkdirStr, NO_OUTPUT
IF @iRC <> 0
BEGIN
exec master.dbo.xp_cmdshell @vcMkdirStr , NO_OUTPUT
END


SET @vcPath = '"C:Program FilesMicrosoft SQL Server90ToolsPublishingSqlPubWiz.exe"'
SET @vcParam = ' script ' + @vcTextPath + '' + @vcTextFile + ' -schemaonly -d ' + @vcDatabase
SET @vcTotcmd =  @vcPath + @vcParam


print @vcTotcmd


EXEC master..xp_cmdshell @vcTotcmd

Then you run the script with this statement:

SQL
EXEC sp_GenDBscript 'AdventureWorks'

You can now also schedule the scripting of the database, for example at the start of an ETL run. Watch out for the hard-coded fields in the Stored Procedure (E:SQLSCRIPTS ). This is the location of the script output (where it should put the file).

Frequently asked questions

How do I script a complete SQL Server database including functions and stored procedures?

Use the stored procedure sp_GenDBscript. It creates a script of the database structure and all objects, such as functions and stored procedures.

How do I run sp_GenDBscript?

Call the procedure with the database name, for example EXEC sp_GenDBscript 'AdventureWorks'. The name of the script becomes Naam_Database_Datum_RandomNummer.

Why would I want a backup of the structure of a database?

If you change a database often, a script of the structure and all objects is a useful backup.

Which tool does sp_GenDBscript use to script the database?

The procedure starts SqlPubWiz.exe through xp_cmdshell, with the option -schemaonly. So the script holds only the structure.

Can I schedule the scripting of the database?

Yes. You can schedule the scripting, for example at the start of an ETL run. Watch out for the hard-coded output location in the stored procedure.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. How do I make a date dimension?

    SQL Server24 Feb 2009 · 12.4k reads
    12.4k

  2. T-SQL Group By ALL

    SQL Server26 Feb 2009 · 3.7k reads
    3.7k

  3. Recursive Query with CTE II

    SQL Server17 Dec 2008 · 4.2k reads
    4.2k
(3 rows affected)SQL Server