Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can I backup database DDL only? Expand / Collapse
Author
Message
Posted Thursday, August 10, 2006 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:39 AM
Points: 4, Visits: 169

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

Post #300825
Posted Thursday, August 10, 2006 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,369, Visits: 9,922

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

Post #300856
Posted Thursday, August 10, 2006 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 9:39 AM
Points: 4, Visits: 169
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
Post #300970
Posted Friday, August 11, 2006 12:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 10:22 AM
Points: 22, Visits: 241

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




Post #301310
Posted Friday, August 11, 2006 2:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 1,510, Visits: 2,700

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

 

Post #301349
Posted Saturday, August 12, 2006 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 6, 2009 7:59 PM
Points: 7, 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
Post #301388
Posted Monday, August 14, 2006 1:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:32 AM
Points: 5,369, Visits: 9,922

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

Post #301453
Posted Monday, August 14, 2006 3:47 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 4,064, Visits: 5,323

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
Post #301735
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse