May 18, 2005 at 7:16 am
Hello,
We would like to script off our database schemas as part of out disaster recovery plans. We want to use a job to do this monthly. We have tried the SCPTXFR program, but it does not give us everything we need. We have tried using Profiler to capture the code behind an Enterprise manager generate SQL script function, but the results are very complex and will take some time to understand. Has anyone come up with a way to create a job to produce a script of an entire database schema? Any advice would be welcome. Thanks.
May 19, 2005 at 12:01 am
Using Ken Henderson's sp_generate_script stored procedure (see below)...
Create a SQL Job with 2 steps. This is Step 1. It copies creates as script of the database with the date taged onto it.
/******************************************************************************/
DECLARE @DATE_ADDON AS VARCHAR(20)
DECLARE @PATH_AND_FILE AS VARCHAR(255)
SET @DATE_ADDON = convert(varchar,getdate(),112)+ REPLACE(convert(VARCHAR(5),getdate(),108),':','')
SET @PATH_AND_FILE = 'D:\SQL_DDL_BAK\MyDatabase\MyDB_db_script_'+ @DATE_ADDON +'.sql'
print @PATH_AND_FILE
EXEC sp_generate_script @outputname=@PATH_AND_FILE,
@server='MY-SERVER',
@includeheaders=0
/******************************************************************************/
Then use another script (as Step 2 of the job) to delete all the scripted out
/******************************************************************************/
EXEC xp_cmdshell 'C:\VBScripts\CleanOldDDLBaks.bat'
/******************************************************************************/
The CleanOldDDLBaks.bat file really just calls a VBScript file:
'------------------------------------------------------------------------------
'Author: James Heaton
'Source: http://cwashington.netreach.net/depo/view.asp?Index=777&ScriptType=vbscript
'Name: cleanup.vbs
'example: cscript cleanup.vbs /dir:C:\PROGRA~1\MICROS~2\Backup\MYSQLD~1 /days:30 /ext:bak
'example: cscript c:\vbs\cleanup.vbs /dir:C:\PROGRA~1\MICROS~2\Backup\MYSQLD~1 /days:30
'Description: The script deletes all file in a specified directory that are over a
'specified age since last updated, eg. could be used to delete all files in a directory
'that are over 30 days old (30 days since last update). Expected that this would be
'used as a scheduled task. This was orginally written to delete old log files generated
'by an application running on a Windows 2000 Server. Errors are written to the
'Windows Event Log. Usage instructions provided by ShowUsage() sub - just run script
'without arguments to view them.
Option Explicit
' Declare Variables
Dim wshArgs, wshShell
Dim FilePath, Retention, FileFilter
' Create wshArguments Object
Set wshArgs = wscript.arguments
' Create wshShell Object
Set wshShell = wscript.createobject("wscript.shell")
If ChkArgs = True then
' Get Arguments and Populate Variables
FilePath = wshArgs.named.item("dir")
Retention = wshArgs.named.item("days")
' Call Sub-Procedure to perform file deletion
DelFiles FilePath, Retention, FileFilter
' Display message stating that operation complete.
wscript.echo "Delete Operation Complete. Any problems encountered have been logged to the Application Log."
' Log Information Event in Application Log
wshShell.LogEvent 4, "Delete Operation Completed (CLEANUP.VBS)."
Else
' Display Usage Instructions
ShowUsage 1
' Log Warning in Event Log
wshShell.LogEvent 2, "DELETE OPERATION FAILED (CLEANUP.VBS). Required Arguments Not Supplied"
' Display error message. When run as a scheduled task this message is suppressed.
wscript.echo "DELETE OPERATION FAILED!"
wscript.echo "The required Arguments have not been entered."
End If
' Clear Objects
Set wshArgs = Nothing
Set wshShell = Nothing
FilePath = ""
Retention = ""
'******************************************************************
' SUB-PROCEDURES AND FUNCTIONS
'******************************************************************
Function ChkArgs
' Declare Variables
Dim PathArg, DaysArg, ExtArg, HelpArg
' Count Number of Arguments Entered
PathArg = wshArgs.Named.exists("dir")
DaysArg = wshArgs.named.exists("days")
ExtArg = wshArgs.named.exists("ext")
HelpArg = wshArgs.named.exists("?")
' If all necessary arguments (2) entered Return True
' If not return False
' If the Help Argument (/?) is entered display Usage Instructions
If HelpArg = True then
ShowUsage 0
wscript.quit
End If
If PathArg = True and DaysArg = True then
ChkArgs = True
Else
ChkArgs = False
End If
If ExtArg = True then
FileFilter = True
Else
FileFilter = False
End If
End Function
Sub ShowUsage(Mode)
' Display Usage Instructions
wscript.echo "cleanup.vbs Usage Instructions:" & _
vbcrlf & _
vbcrlf & _
" cleanup.vbs /dir:[path] /days:[retention period] /ext:[file extension]" & _
vbcrlf & _
vbcrlf & _
"path = this is the full path to the directory to be cleaned" & _
vbcrlf & _
vbcrlf & _
"retention period = this is the length of time in days that files are to be kept" & _
vbcrlf & _
vbcrlf & _
"file extension = optional argument. Only files with specified extension will be deleted (enter WITHOUT a preceding dot)" & _
vbcrlf & _
vbcrlf & _
"Any files that are outside the specified retention period will be deleted."
' 0 = Help Only Mode. In this mode exit script after displaying usage instructions
' and do not attempt a delete operation
If Mode = 0 then
wscript.quit
Else
End If
End Sub
Sub DelFiles(Dir, Days, DelFilter)
' Procedure to cleanup old files
' Declare Variables
Dim DelDate, FSO, Folder, Files, File, FileDate, FilterExt, Ext
' Determine date six months ago by calculating todays date minus 182 days
DelDate = Now - Days
' Create Scripting Runtime FileSystemObject
Set FSO = createobject("scripting.filesystemobject")
' Create Folder Object
' If Path Not Found Generate Error and Event Log entry then exit script
On Error Resume Next
Set Folder = FSO.GetFolder(Dir)
If Err.Number = 76 then
wscript.echo "DELETE OPERATION FAILED! - Path Not Found."
wshShell.LogEvent 2, "DELETE OPERATION FAILED (CLEANUP.VBS) - Path Not Found."
wscript.quit
End If
On Error Goto 0
' Create File Object
Set Files = Folder.Files
' For each file in the directory check the file age and if older than the specified
' retention period delete it
' If an extension filter was specified only delete if the extention matches as well
For each File in Files
FileDate = File.DateCreated
Ext = FSO.GetExtensionName(File)
On Error Resume Next
If DelFilter = True then
If FileDate <= DelDate and Ext = FilterExt then
File.Delete
End If
Else
If FileDate <= DelDate then
File.Delete
End If
End If
' Log error to Event Log and Screen if permission denied
If Err.Number = 70 Then
wscript.echo "Deletion of " & Dir & "\" & File.Name & _
" Failed - ACCESS DENIED!"
wshShell.LogEvent 2, "Deletion of " & Dir & "\" & _
File.Name & " Failed - ACCESS DENIED! (CLEANUP.VBS)"
End If
On Error Goto 0
Next
' Clear File and Folder Objects
Set Files = Nothing
Set Folder = Nothing
Set FSO = Nothing
End Sub
'------------------------------------------------------------------------------
You can get Ken Henderson's sp_generate_inserts stored procedure from "Guru's Guide to SQL Server Stored Procedures, XML, and HTML."
If you don't own it, you should.
G. Milner
May 23, 2005 at 8:28 am
I looked over this book. Does this script do more than list tables? We can get that with the tools that we have. We need all objects(tables, views, stored procedures, permissions, roles, users, etc).
May 23, 2005 at 8:52 am
Yes. I use it to script out the entire database. It looks something like this (in a SQL Job that runs nightly):
/******************************************************************************/
EXEC sp_generate_script @outputname='\\MY_OTHER_SVR\DBs_scripted\MyDB_db_script.sql',
@server='MY_SERVER_NAME',
@includeheaders=0
/******************************************************************************/
This scripts out all objects in the database to another server as a large script, where our version control software picks it up. In this case, unlike above, it has the same name every time. If you want a name with the date stuck on it, the above method with the variables is appropriate.
You can either do it this way, with SQL Agent, or as mentioned above with VBScript and Task Sheduler
G. Milner
June 3, 2005 at 2:44 pm
Can somebody publish the script for sp_generate_script?
June 3, 2005 at 3:13 pm
I don't know. Is that kosher? It has a copyright (c) in the version in the book. Would we have to have Ken Henderson's permission to put it up here?
Maybe you could google it and get it of another (less conscientious) site.
If you don't have the book, maybe you should get it.
G. Milner
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply