SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I backup database DDL only?


Can I backup database DDL only?

Author
Message
don-357257
don-357257
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 215

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


John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14420 Visits: 15980

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
don-357257
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 215
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
Leda Behseresht
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 249

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
WILLIAM MITCHELL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2158 Visits: 3062

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
Luis Aguayo
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 21
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
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14420 Visits: 15980

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
Greg Charles
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 5932

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



Greg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search