Scripting out Databases
-
Hi. I'm preparing for Disaster Recovery and one scenerio would be to recreate all user databases on a 'like' server and then restore most recent backups.
I know I can script out the current production databases via Enterprise Manager - one at a time - does anyone know of a faster or easier way to do this ????
-
You don't have to do it one at a time....EM gives you the options of choosing between the various objects to be scripted...views, tables etc...all or some....in individual files or one composite file....et al....
**ASCII stupid question, get a stupid ANSI !!!** -
Sorry.. maybe I'm missing something.. but I cannot see where you can script out ALL databases at the same time. I can right-click on each Database - one at a time - and script them out that way.... I just thought that there was an easier way - or a better way to do it.. ??????
-
Sorry - My bad - I thought you said one object at a time....not one database....there are scripts on this site that should help you script all of them...I think it uses an sp_msforeachdb undocumented procedure....
**ASCII stupid question, get a stupid ANSI !!!** -
I'm not sure what you'd gain by scripting the databases. If you do full backups of the databases regularly, when you do a restore, the act of restoring will create the database at the same time.
-
It's useful to keep db scripts handy if you want to run some tests on a subset (or all) of the database and you want to use it with test data - not the existing one restored from a backup....
**ASCII stupid question, get a stupid ANSI !!!** -
I do by creating a BAT (or CMD) file with this in it...........:
REM set the Folder name in this script
REM SET @OutputFolder = 'C:\SQL_Rebuild_Scripts'
SET ServerName=DELL
isql.exe -S %ServerName% -E -d MASTER -i C:\SQL_Rebuild_Scripts\Script_All_DBs_Structure.sql -w400 -n -r
Then the script it calls is done this way........:
-- ==================================================================================================
-- Copyright © 2005 Jamie 'MudFace' Christian. All rights reserved.
-- ==================================================================================================
-- ==================================================================================================
-- Object: Script_ALL_DBs_Structure.sql
--
-- Description: This script will walk thru all the databases on the target system
--
--
-- Usage: Script_ALL_DBs_Structure.sql
--
--
-- Created by: Jamie 'MudFace' Christian.
--
--
--
--
--
-- Package Description: This Script is used with Script_ALL_DB_Structure_Worker.sql
-- You must set the location of the Script File Script_ALL_DB_Structure_Worker.sql
-- You must set the location of the Output reports
--
-- Generated Date: 12/29/2004
-- Generated Time: 7:48:36 AM
-- ==================================================================================================
SET NOCOUNT ON
-- Loop thru all the databases that we should Script. Northwind is just an example db and tempdb and model arent needed.
DECLARE @DB VARCHAR (255)
DECLARE @Names VARCHAR (255)
DECLARE @Status INT
DECLARE @Padding VARCHAR(255)
DECLARE @HowManySpaces INT
DECLARE @MyString VARCHAR(1000)
DECLARE @folderexist INT
DECLARE @query VARCHAR(1000)
DECLARE @MyFolder VARCHAR(1000)
DECLARE @L_OutputFolder VARCHAR(500)
DECLARE @OutputFolder VARCHAR(500)
DECLARE @OutPutSub VARCHAR(500)
DECLARE @ScriptName VARCHAR(500)
DECLARE @InputScript VARCHAR(500)
DECLARE @FinalOutput VARCHAR(500)
DECLARE @Server_Name SYSNAME
SET @Server_Name = @@ServerName
SET @OutputFolder = 'C:\SQL_Rebuild_Scripts\' + @Server_Name
-- SET @OutputFolder = 'C:\SQL_Rebuild_Scripts\' + @Server_Name + '\'
SET @L_OutputFolder = 'C:\SQL_Rebuild_Scripts\' + @ScriptName
SET @OutputSub = 'UserPermissionsScripts'
SET @ScriptName = 'Script_ALL_DB_Structure_Worker.sql'
SET @FinalOutput = @OutputFolder + '\'+ @OutputSub
SET @InputScript = @L_OutputFolder + '\' + @ScriptName
-- This creates the Output File Names and Location, even if it does not exist already
-- ===========================================================================================
/****** Object: Table [dbo].[#FILES] Script Date: 12/22/2004 5:31:05 PM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#FILES]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [#FILES]
-- This creates the Output File Names and Location
CREATE TABLE #files
(
Files INT,
Folder INT,
parent INT
)
-- Does the Destination Folder exist?
INSERT #files EXEC master.dbo.xp_fileexist @FinalOutput
SELECT @folderexist = Folder FROM #files
IF @folderexist <>1
BEGIN
-- The Destination Folder does NOT exists, MAKE IT
SET @query = 'MKDIR "'+ @FinalOutput +'"'
PRINT 'Information: "'+ @FinalOutput + '" is being created now....... Creating the Folder'
PRINT ' '
PRINT @query
EXEC master..xp_cmdshell @query
SET @FinalOutput = @FinalOutput
END
ELSE
BEGIN
-- The Destination Folder DOES exists, USE IT
PRINT 'Information: "'+ @FinalOutput + '" already exists....... Skipping Folder Creation'
PRINT ' '
END
DROP TABLE [#FILES]
-- declare Database_Cursor cursor for select name from master..sysdatabases where name <> 'tempdb' and name <> 'model' and name <> 'Northwind'
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Names = 'isql.exe -q -S '+ @Server_Name +' -E -d [' + @DB + '] -i ' + @InputScript +' -w400 -n'
EXEC master..xp_cmdshell @Names, no_output
SET @mystring = 30- LEN(@DB) + 15
PRINT UPPER(@Server_Name)+'_'+UPPER(@DB)+'_RE-GENERATE_SCRIPT.SQL' + REPLICATE('.',@mystring) +'. SQL file generated succesfully.'
FETCH NEXT FROM Database_Cursor INTO @DB
end
SET NOCOUNT OFF
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
-
And this is the Third part of teh script.............I know there are LOTS of ways to skin a cat, so no flaming please
-- ===============================================================================================
-- Copyright © 2005 Jamie 'MudFace' Christian. All rights reserved.
-- ===============================================================================================
-- ===============================================================================================
-- Object: sp_generate_script
-- Description: Generates a creation script for an object or collection of objects
--
-- Usage: sp_generate_script [@objectname='Object name or mask (defaults to all object in current database)']
-- [,@outputname='Output file name' (Default: C:\SERVERNAME_DATABASE_@objectname+'.SQL',
-- OR C:\SERVERNAME_DATABASE_GENERATED_SCRIPT.SQL for entire database)]
-- [,@scriptoptions=bitmask specifying script generation options]
-- [,@resultset=bit specifying whether to generate a result set
-- [,@includeheaders=bit specifying whether to generate discriptive headers for scripts
-- [,@server='server name'][, @username='user name'][, @password='password'][, @trustedconnection=1]
-- Returns: (None)
-- Author: Jamie "MudFace" Christian. -- Revision: 8.0
--
-- Example: sp_generate_script @objectname='authors', @outputname='authors.sql'
-- Created: 12//01/2004. Modtime: 12/28/2004.
--
--
-- NOTE:
-- Be sure to find and change the line defining the Local Folder Location (near Line # 257);
-- SET @outputname='C:\SQL_Admin\UserPermissionsScripts\'+ Upper(@@servername) +'_'+ Upper(@dbname) +'_GENERATED_SCRIPT.SQL'
--
-- The Local Folder Location should be changed to fit your system.
-- ===============================================================================================
-- ===[ Parameter Explanation ] ==================================================================
-- @objectname sysname=NULL, = Object mask to copy
-- @outputname sysname=NULL, = Output file to create (default: 'GENERATED_SCRIPT.SQL')
-- @scriptoptions int=NULL, = Options bitmask for Transfer
-- @resultset bit=1, = Determines whether the script is returned as a result set
-- @trustedconnection bit=1, = Use a trusted connection to connect to the server
-- @IncludeHeaders bit=1, = Determines whether descriptive headers are included with scripts
-- @server sysname=@@SERVERNAME, = Server Name (defaults to @@SERVERNAME)
-- @username sysname='sa', = Name of the user to connect as (defaults to 'sa')
-- @password sysname=NULL = User's password
-- ===============================================================================================
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- ===[ BEGIN COMMENT SECTION ] ==================================================================
-- USE master
-- GO
-- IF OBJECT_ID('sp_generate_script','P') IS NOT NULL
-- DROP PROC sp_generate_script
-- GO
-- CREATE PROC sp_generate_script
-- @objectname SYSNAME=NULL,
-- @outputname SYSNAME=NULL,
-- @scriptoptions INT=NULL,
-- @resultset BIT=1,
-- @trustedconnection BIT=1,
-- @IncludeHeaders BIT=1,
-- @server SYSNAME=@@SERVERNAME,
-- @username SYSNAME='sa',
-- @password SYSNAME=NULL
-- AS
-- IF (@outputname='/?') OR (@outputname IS NULL) GOTO Help
-- ===[ END COMMENT SECTION ] ====================================================================
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- ===============================================================================================
-- ===============================================================================================
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- ===[ BEGIN COMMENT SECTION ] ==================================================================
-- NOTES:
-- - Find and Un-Comment all "RETURN -1" found throughout the script as well
--
-- - If you do not have the Stored Procedure named "sp_displayoaerrorinfo" Then
-- you must use "sp_OAGetErrorInfo" for your Error Handling.
--
-- - I have commented out the calls to "sp_displayoaerrorinfo" in favor of calls to "sp_OAGetErrorInfo"
--
-- Sample = "EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT"
-- Instead of: "EXEC sp_displayoaerrorinfo @srvobject, @hr"
-- ===============================================================================================
SET NOCOUNT ON
DECLARE
@ObjectName SYSNAME,
@OutputName SYSNAME,
@ScriptOptions INT,
@Resultset BIT,
@TrustedConnection BIT,
@IncludeHeaders BIT,
@Server SYSNAME,
@UserName SYSNAME,
@Password SYSNAME
SET @Server = @@SERVERNAME
SET @TrustedConnection = 1
-- ===[ END COMMENT SECTION ] ====================================================================
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- ===============================================================================================
-- ===============================================================================================
-- SQLDMO_SCRIPT_TYPE vars
-- ===============================================================================================
DECLARE @SQLDMOScript_Default INT
DECLARE @SQLDMOScript_Drops INT
DECLARE @SQLDMOScript_ObjectPermissions INT
DECLARE @SQLDMOScript_PrimaryObject INT
DECLARE @SQLDMOScript_ClusteredIndexes INT
DECLARE @SQLDMOScript_Triggers INT
DECLARE @SQLDMOScript_DatabasePermissions INT
DECLARE @SQLDMOScript_Permissions INT
DECLARE @SQLDMOScript_ToFileOnly INT
DECLARE @SQLDMOScript_Bindings INT
DECLARE @SQLDMOScript_AppendToFile INT
DECLARE @SQLDMOScript_NoDRI INT
DECLARE @SQLDMOScript_UDDTsToBaseType INT
DECLARE @SQLDMOScript_IncludeIfNotExists INT
DECLARE @SQLDMOScript_NonClusteredIndexes INT
DECLARE @SQLDMOScript_Indexes INT
DECLARE @SQLDMOScript_Aliases INT
DECLARE @SQLDMOScript_NoCommandTerm INT
DECLARE @SQLDMOScript_DRIIndexes INT
DECLARE @SQLDMOScript_IncludeHeaders INT
DECLARE @SQLDMOScript_OwnerQualify INT
DECLARE @SQLDMOScript_TimestampToBinary INT
DECLARE @SQLDMOScript_SortedData INT
DECLARE @SQLDMOScript_SortedDataReorg INT
DECLARE @SQLDMOScript_TransferDefault INT
DECLARE @SQLDMOScript_DRI_NonClustered INT
DECLARE @SQLDMOScript_DRI_Clustered INT
DECLARE @SQLDMOScript_DRI_Checks INT
DECLARE @SQLDMOScript_DRI_Defaults INT
DECLARE @SQLDMOScript_DRI_UniqueKeys INT
DECLARE @SQLDMOScript_DRI_ForeignKeys INT
DECLARE @SQLDMOScript_DRI_PrimaryKey INT
DECLARE @SQLDMOScript_DRI_AllKeys INT
DECLARE @SQLDMOScript_DRI_AllConstraints INT
DECLARE @SQLDMOScript_DRI_All INT
DECLARE @SQLDMOScript_DRIWithNoCheck INT
DECLARE @SQLDMOScript_NoIdentity INT
DECLARE @SQLDMOScript_UseQuotedIdentifiers INT
-- ===============================================================================================
-- SQLDMO_SCRIPT2_TYPE vars
-- ===============================================================================================
DECLARE @SQLDMOScript2_Default INT
DECLARE @SQLDMOScript2_AnsiPadding INT
DECLARE @SQLDMOScript2_AnsiFile INT
DECLARE @SQLDMOScript2_UnicodeFile INT
DECLARE @SQLDMOScript2_NonStop INT
DECLARE @SQLDMOScript2_NoFG INT
DECLARE @SQLDMOScript2_MarkTriggers INT
DECLARE @SQLDMOScript2_OnlyUserTriggers INT
DECLARE @SQLDMOScript2_EncryptPWD INT
DECLARE @SQLDMOScript2_SeparateXPs INT
-- ===============================================================================================
-- SQLDMO_SCRIPT_TYPE values
-- ===============================================================================================
SET @SQLDMOScript_Default = 4
SET @SQLDMOScript_Drops = 1
SET @SQLDMOScript_ObjectPermissions = 2
SET @SQLDMOScript_PrimaryObject = 4
SET @SQLDMOScript_ClusteredIndexes = 8
SET @SQLDMOScript_Triggers = 16
SET @SQLDMOScript_DatabasePermissions = 32
SET @SQLDMOScript_Permissions = 34
SET @SQLDMOScript_ToFileOnly = 64
SET @SQLDMOScript_Bindings = 128
SET @SQLDMOScript_AppendToFile = 256
SET @SQLDMOScript_NoDRI = 512
SET @SQLDMOScript_UDDTsToBaseType = 1024
SET @SQLDMOScript_IncludeIfNotExists = 4096
SET @SQLDMOScript_NonClusteredIndexes = 8192
SET @SQLDMOScript_Indexes = 73736
SET @SQLDMOScript_Aliases = 16384
SET @SQLDMOScript_NoCommandTerm = 32768
SET @SQLDMOScript_DRIIndexes = 65536
SET @SQLDMOScript_IncludeHeaders = 131072
SET @SQLDMOScript_OwnerQualify = 262144
SET @SQLDMOScript_TimestampToBinary = 524288
SET @SQLDMOScript_SortedData = 1048576
SET @SQLDMOScript_SortedDataReorg = 2097152
SET @SQLDMOScript_TransferDefault = 422143
SET @SQLDMOScript_DRI_NonClustered = 4194304
SET @SQLDMOScript_DRI_Clustered = 8388608
SET @SQLDMOScript_DRI_Checks = 16777216
SET @SQLDMOScript_DRI_Defaults = 33554432
SET @SQLDMOScript_DRI_UniqueKeys = 67108864
SET @SQLDMOScript_DRI_ForeignKeys = 134217728
SET @SQLDMOScript_DRI_PrimaryKey = 268435456
SET @SQLDMOScript_DRI_AllKeys = 469762048
SET @SQLDMOScript_DRI_AllConstraints = 520093696
SET @SQLDMOScript_DRI_All = 532676608
SET @SQLDMOScript_DRIWithNoCheck = 536870912
SET @SQLDMOScript_NoIdentity = 1073741824
SET @SQLDMOScript_UseQuotedIdentifiers = -1
-- ===============================================================================================
-- SQLDMO_SCRIPT2_TYPE values
-- ===============================================================================================
SET @SQLDMOScript2_Default = 0
SET @SQLDMOScript2_AnsiPadding = 1
SET @SQLDMOScript2_AnsiFile = 2
SET @SQLDMOScript2_UnicodeFile = 4
SET @SQLDMOScript2_NonStop = 8
SET @SQLDMOScript2_NoFG = 16
SET @SQLDMOScript2_MarkTriggers = 32
SET @SQLDMOScript2_OnlyUserTriggers = 64
SET @SQLDMOScript2_EncryptPWD = 128
SET @SQLDMOScript2_SeparateXPs = 256
-- ===============================================================================================
-- === [ sp_OAGetErrorInfo Parameters ] ===
-- ===============================================================================================
DECLARE
@dbname SYSNAME,
@srvobject INT, -- SQL Server object
@object INT, -- Work variable for accessing COM objects
@hr INT, -- Contains HRESULT returned by COM
@tfobject INT, -- Stores pointer to Transfer object
@res INT,
@src VARCHAR(255),
@desc VARCHAR(255)
DECLARE @OutputFolder VARCHAR(500)
DECLARE @OutPutSub VARCHAR(500)
DECLARE @FinalOutput VARCHAR(500)
SET @OutputFolder = 'C:\SQL_Rebuild_Scripts\'+ @@ServerName + '\'
SET @OutputSub = 'UserPermissionsScripts'
SET @FinalOutput = @OutputFolder + '\'+ @OutputSub
SET @res=0
IF (@objectname IS NOT NULL) AND (CHARINDEX('%',@objectname)=0) AND (CHARINDEX('_',@objectname)=0)
BEGIN
SET @dbname=ISNULL(PARSENAME(@objectname,3),DB_NAME()) -- Extract the DB name; default to current
SET @objectname=PARSENAME(@objectname,1) -- Remove extraneous stuff from table name
IF (@objectname IS NULL)
BEGIN
RAISERROR('Invalid object name.',16,1)
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- RETURN -1
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
END
-- IF (@outputname IS NULL)
SET @dbname=DB_NAME()
END ELSE
BEGIN
SET @dbname=DB_NAME()
-- IF (@outputname IS NULL)
SET @outputname=@FinalOutput + '\'+ Upper(@@servername) +'_'+ Upper(@dbname) +'_RE-GENERATE_SCRIPT.SQL'
END
-- ===============================================================================================
-- Create a SQLServer object
-- ===============================================================================================
EXEC @hr=sp_OACreate 'SQLDMO.SQLServer', @srvobject OUTPUT
IF (@hr <> 0)
BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @srvobject, @hr
RETURN
END
-- Connect to the server
IF (@trustedconnection=1)
BEGIN
EXEC @hr = sp_OASetProperty @srvobject, 'LoginSecure', 1
IF (@hr <> 0)
BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @srvobject, @hr
GOTO ServerError
END
EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server
END
ELSE
BEGIN
IF (@password IS NOT NULL)
BEGIN
EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username, @password
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @srvobject, 'Connect', NULL, @server, @username
END
END
IF (@hr <> 0)
BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @srvobject, @hr
GOTO ServerError
END
-- ===============================================================================================
-- Create a Transfer object
-- ===============================================================================================
EXEC @hr=sp_OACreate 'SQLDMO.Transfer', @tfobject OUTPUT
IF (@hr <> 0) BEGIN
EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeSrv
END
-- Set Transfer's Drop Destination Object First property
EXEC @hr = sp_OASetProperty @tfobject, 'DropDestObjectsFirst', 1
IF (@hr <> 0)
BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Set Transfer's CopyData property
EXEC @hr = sp_OASetProperty @tfobject, 'CopyData', 0
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Tell Transfer to copy the schema
EXEC @hr = sp_OASetProperty @tfobject, 'CopySchema', 1
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- ===============================================================================================
-- Get all objects in the database
-- ===============================================================================================
IF (@objectname IS NULL) BEGIN
-- Tell Transfer to copy all objects
EXEC @hr = sp_OASetProperty @tfobject, 'CopyAllObjects', 1
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Tell Transfer to get groups as well
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeGroups', 1
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Tell it to include users
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeUsers', 1
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Include object dependencies, too
EXEC @hr = sp_OASetProperty @tfobject, 'IncludeDependencies', 1
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
IF (@scriptoptions IS NULL) BEGIN
SET @scriptoptions=@SQLDMOScript_OwnerQualify | @SQLDMOScript_Default | @SQLDMOScript_Triggers |
@SQLDMOScript_Bindings | @SQLDMOScript_Permissions | @SQLDMOScript_Indexes | @SQLDMOScript_DRI_Defaults | @SQLDMOScript_IncludeIfNotExists | @SQLDMOScript_Drops --| @SQLDMOScript_NoDRI
IF @includeheaders=131072 SET @scriptoptions=@scriptoptions | @SQLDMOScript_IncludeHeaders | @SQLDMOScript_IncludeIfNotExists
END
END -- IF (@objectname IS NULL)
ELSE BEGIN
DECLARE
@obname SYSNAME,
@obtype VARCHAR(2),
@obowner SYSNAME,
@OBJECT_TYPES VARCHAR(50),
@obcode INT
-- ===============================================================================================
-- Used to translate SYSOBJECTS.TYPE into the bitmap that Transfer requires
-- DO NOT CHANGE THIS STRING -- it serves as a translate table
-- ===============================================================================================
SET @OBJECT_TYPES='T V U P D R TR FN TF IF '
-- ===============================================================================================
-- Find all the objects that match the supplied mask and add them to the Transfer's list of objects to script
DECLARE ObjectList CURSOR FOR
SELECT name,CASE type WHEN 'TF' THEN 'FN' WHEN 'IF' THEN 'FN' ELSE type END AS type,USER_NAME(uid) FROM sysobjects
WHERE (name LIKE @objectname)
AND (CHARINDEX(type+' ',@OBJECT_TYPES)<>0)
AND (OBJECTPROPERTY(id,'IsSystemTable')=0)
AND (status>0)
UNION ALL -- Include user-defined data types
SELECT name,'T',USER_NAME(uid)
FROM SYSTYPES
WHERE (usertype & 256)<>0
AND (name LIKE @objectname)
OPEN ObjectList
FETCH ObjectList INTO @obname, @obtype, @obowner
WHILE (@@FETCH_STATUS=0) BEGIN
SET @obcode=POWER(2,(CHARINDEX(@obtype+' ',@OBJECT_TYPES)/3))
EXEC @hr = sp_OAMethod @tfobject, 'AddObjectByName', NULL, @obname, @obcode, @obowner
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
FETCH ObjectList INTO @obname, @obtype, @obowner
END
CLOSE ObjectList
DEALLOCATE ObjectList
IF (@scriptoptions IS NULL)
SET @scriptoptions=@SQLDMOScript_Default -- Keep it simple when not scripting the entire database
IF @includeheaders=131072 SET @scriptoptions=@scriptoptions | @SQLDMOScript_IncludeHeaders | @SQLDMOScript_Drops
END -- ELSE IF (@objectname IS NULL)
-- Set Transfer's ScriptType property
EXEC @hr = sp_OASetProperty @tfobject, 'ScriptType', @scriptoptions
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Set Transfer's Script2Type property
EXEC @hr = sp_OASetProperty @tfobject, 'Script2Type', @SQLDMOScript2_NoFG
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Get a pointer to the database
DECLARE
@itemname VARCHAR(255)
SET @itemname='Databases.Item("<A href="mailto:'+@dbname+'"
'">'+@dbname+'")'EXEC @hr = sp_OAGetProperty @srvobject, @itemname, @object OUT
IF @hr <> 0 BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @srvobject, @hr
GOTO FreeAll
END
DECLARE
@cmd VARCHAR(8000)
-- ===============================================================================================
-- Call the Database object's Transfer method to transfer the schemas to the file
-- We go to a file rather than directly to the target because of bugs in DMO that cause
-- it to script certain constraints multiple times. This causes the sp_OA call to fail.
-- EM also double-scripts these constraints, but it has the luxury of being able to ignore the
-- the errors.
-- ===============================================================================================
-- We begin by scripting the objects without DRI references, then we script the PKs, then the FKs
EXEC @hr = sp_OAMethod @object, 'ScriptTransfer',NULL, @tfobject, 2,@outputname
IF @hr <> 0 BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @object, @hr
GOTO FreeAll
END
-- ===============================================================================================
-- Now get the PKs and UKs (append to the original script file)
-- We get the PKs and UKs separately from the tables themselves because
-- getting PKs sometimes also pulls FKs despite our not having requested FKs
-- ===============================================================================================
SET @scriptoptions=@SQLDMOScript_NoDRI | @SQLDMOScript_DRI_PrimaryKey | @SQLDMOScript_DRI_UniqueKeys | @SQLDMOScript_AppendToFile | @SQLDMOScript_OwnerQualify | @SQLDMOScript_IncludeIfNotExists
IF @includeheaders=131072 SET @scriptoptions=@scriptoptions | @SQLDMOScript_IncludeHeaders | @SQLDMOScript_IncludeIfNotExists
-- Reset Transfer's ScriptType property
EXEC @hr = sp_OASetProperty @tfobject, 'ScriptType', @scriptoptions
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
EXEC @hr = sp_OAMethod @object, 'ScriptTransfer',NULL, @tfobject, 2,@outputname
IF @hr <> 0 BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @object, @hr
GOTO FreeAll
END
-- Now get the FKs (append to the original script file)
SET @scriptoptions=@SQLDMOScript_NoDRI | @SQLDMOScript_DRI_ForeignKeys | @SQLDMOScript_DRI_Checks | @SQLDMOScript_DRI_Defaults | @SQLDMOScript_AppendToFile | @SQLDMOScript_OwnerQualify
IF @includeheaders=131072 SET @scriptoptions=@scriptoptions | @SQLDMOScript_IncludeHeaders | @SQLDMOScript_IncludeIfNotExists
-- Reset Transfer's ScriptType property
EXEC @hr = sp_OASetProperty @tfobject, 'ScriptType', @scriptoptions
IF (@hr <> 0) BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @tfobject, @hr
GOTO FreeAll
END
-- Generate the last section of the script
EXEC @hr = sp_OAMethod @object, 'ScriptTransfer',NULL, @tfobject, 2,@outputname
IF @hr <> 0 BEGIN
EXEC @hr = sp_OAGetErrorInfo @object, @src OUT, @desc OUT
-- EXEC sp_displayoaerrorinfo @object, @hr
GOTO FreeAll
END
IF (@resultset=1) BEGIN
SET @cmd='TYPE "'+@outputname+'"'
EXEC master.dbo.xp_cmdshell @cmd
END
GOTO FreeAll
ServerError:
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- SET @res=-1
-- RAISERROR ('Error generating script', 16, 1)
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
FreeAll:
EXEC sp_OADestroy @tfobject -- For cleanliness
FreeSrv:
EXEC sp_OADestroy @srvobject
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
-- RETURN @res
-- ===[ UNCOMMENT OUT THIS SECTION TO CONVERT THIS FROM A SCRIPT TO A STORED PROCEDURE ] =========
GO
SET NOCOUNT OFF
-
Hi,
you could exec the following proc for each database:
/* -------------------------------------------------------------------------- */
create proc master.dbo.sp_ScriptDatabase @dbname sysname
as
declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Programme\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s servername /I /d '
+ @dbname + ' /f c:\temp\' + @dbname + '_' + @texttime + '.txt /r'
print @command
exec master..xp_cmdshell @command
GO
/* -------------------------------------------------------------------------- */
/* -------------------------------------------------------------------------- */
DECLARE @msg VARCHAR(999)
DECLARE @dbname sysname
SELECT @msg = convert(char(25),getdate(),113)+': ++++++++++ begin of scripting +++++++++++'
RAISERROR(@msg,10,1) WITH NOWAIT
-- Cursor zum Auslesen der DB-Namen
DECLARE dbnames_cursor CURSOR
FOR
SELECT name
FROM dbo.sysdatabases where name 'tempdb'
OPEN dbnames_cursor
-- Read all Db-names and execute the procedure for each one
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@@FETCH_STATUS -2)
BEGIN
SELECT @msg = convert(char(25),getdate(),113)+': beginning to script database ' + @dbname
RAISERROR(@msg,10,1) WITH NOWAIT
EXEC master.dbo.sp_ScriptDatabase @dbname
SELECT @msg = convert(char(25),getdate(),113)+': end of scripting database ' + @dbname
RAISERROR(@msg,10,1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
END
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT @msg = convert(char(25),getdate(),113)+': +++++ Scripting of all databases done ++++++++++++'
RAISERROR(@msg,10,1) WITH NOWAIT
GO
/* -------------------------------------------------------------------------- */
regards
karl
Best regards
karl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply