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.)
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.
DDL is data stored in the system tables within a database...
Have you tried
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 fncexit_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
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.
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.