Can I backup database DDL only?

Question

Can I backup database DDL only?

don-357257

Mr or Mrs. 500

Points: 572
More actions
August 10, 2006 at 4:46 am

#113848

I have a development server, with a small number of development databases on it.
Some of them can contain vast amounts of data - data which is randomly generated and of no real value.
I want to be able to backup the 'sturcture' of all the databases on the server, but not the data.
In worse-case scenario, if I lost the server, I want to be able to re-create the 'empty' databases, ie. without the data.
If I chose to perform 'normal' backups, even in the Simple Model, I will end up repeatingly backing up vast amounts of worthless data. All i want to backup is the empty databases.
Can this be done within SQL Server 2000, and/or will I need to create scripts, and/or do they already exist?
(I'm assuming I will need to perform normal backups of the master and msdb databases too.)
Cheers,
Don

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Don

The easiest way is indeed to create a script. Right-click on the database in Enterprise Manager and choose Generate SQL Script. You can then make lots of choices about what objects you script and how.

John

don-357257 Mr or Mrs. 500 Points: 572 More actions · Answer 2

I was aware how to do it manually, for individual databases, but I wanted a way of automating it.

Either automating it for individual databases - in which case I would set up a scheduled job for each database to backup it's own DDL - or to automate it for ALL databases to be backed up.

If I could at least find the TSQL script to perform the DDL backup for an individual database, I could create my own script to cycle through ALL the databases on a give server and apply that script.

Anyone with ideas of what the script looks like?

Cheers,

Don

Leda Behseresht SSC Veteran Points: 286 More actions · Answer 3

DDL is data stored in the system tables within a database...

Have you tried

http://www.sqlteam.com/publish/scriptio/

or

http://www.sqlservercentral.com/products/bwunder/archiveutility/

Leda

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

Here is a VB6 or VBA script to do one db, you could easily adapt to script them all...

Sub ScriptDatabase(dbname As String, outfile As String)

'

' this procedure requires a reference to:

'

' Microsoft SQLDMO Object Library

'

Dim svr As SQLDMO.SQLServer

Dim dbs As SQLDMO.Database

Dim tbl As SQLDMO.Table

Dim idx As SQLDMO.Index

Dim trg As SQLDMO.Trigger

Dim chk As SQLDMO.Check

Dim viw As SQLDMO.View

Dim prc As SQLDMO.StoredProcedure

'

Dim db2 As SQLDMO.Database2

Dim fnc As SQLDMO.UserDefinedFunction

'

Dim sql As String

'

On Error GoTo err_sub

'

Screen.MousePointer = vbHourglass

'

' init output file

Open outfile For Output As #1

'

' SERVER

Set svr = New SQLDMO.SQLServer

svr.LoginSecure = True

svr.Connect "localhost"

'

' DATABASE

Set dbs = svr.Databases(dbname, "dbo")

sql = dbs.Script

Print #1, sql

'

' TABLES

For Each tbl In dbs.Tables

If Not tbl.SystemObject Then

sql = tbl.Script

Print #1, sql

For Each idx In tbl.Indexes

sql = idx.Script

Print #1, sql

Next idx

For Each trg In tbl.Triggers

sql = trg.Script

Print #1, sql

Next trg

For Each chk In tbl.Checks

sql = chk.Script

Print #1, sql

Next chk

End If

Next tbl

'

' VIEWS

For Each viw In dbs.Views

If Not viw.SystemObject Then

sql = viw.Script

Print #1, sql

End If

Next viw

'

' STORED PROCEDURES

For Each prc In dbs.StoredProcedures

If Not prc.SystemObject Then

sql = prc.Script

Print #1, sql

End If

Next prc

'

' FUNCTIONS

Set db2 = svr.Databases(dbname, "dbo")

'

For Each fnc In db2.UserDefinedFunctions

If Not fnc.SystemObject Then

sql = fnc.Script

Print #1, sql

End If

Next fnc

exit_sub:

Screen.MousePointer = vbDefault

Close #1

Set dbs = Nothing

Set db2 = Nothing

svr.DisConnect

Set svr = Nothing

Exit Sub

'

err_sub:

MsgBox Err.Description, , "Error # " & Err.Number

Resume exit_sub

'

End Sub

Luis Aguayo SSC Enthusiast Points: 139 More actions · Answer 5

John, I have a similar problem, but myu databse has several store procedures, some of then referencing others.

It could be a problem the creation order of these procedures?

Thx

Luis

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Luis

It shouldn't be a problem. If you try to create a stored procedure that calls another that doesn't exist, you'll get a warning message but the stored procedure will be created anyway.

John

Greg Charles SSC-Forever Points: 45403 More actions · Answer 7

Create a DTS package with a Copy SQL Server Objects task that copies the objects but not the data to a documentation database (the task only works if copying between SQL Server databases) then schedule the package to run regularly.

You can have a separate task for each database in the one package.

Greg