Index scripts backups
-
Hi All,
I need to back up all the index scripts at one shot.Is there any way i could do this by either using a command or any option in enterprise manager or any queries available for this ?
Think Ahead, Raj.D
-
I think the following may be what you are after via EM. Someone has probably created a complex T-SQL to do this as well and with greater control, but I think EM is possibly all you need. This will generate ALTER statements for the indexes and optionally the DROP/CREATE of the associated table. If you do not want to DROP/CREATE the table but still need DROP CONSTRAINT statements then that piece will unfortunately be missing. You can probably search the Scripts part of this site to find what you need in that case.
First, go to the Tables list in EM and press CTRL-A to select them all. Then right click and "All Tasks->Generate SQL Script...". Go to the Options tab and check each of the 4 "Table Scripting Options". If all you want are the index ALTERS then switch to the Formatting table and uncheck both the CREATE and DROP options. To then generate the scripts you can press the OK button and give it a file destination (or switch to the General tab, click preview, and COPY it to the clipboard).
Hope that helps.
-
In case you have a need to do this outside of EM, here's an s.p. using SQLDMO. Just pass the database name for the @db parameter, script file name for @ScriptFilePath parameter, and @ObjectType='I' (for index creation script). @SQLDMOHost should be passed as NULL for your local/connected sql server.
-----------------------------------------------
CREATE PROCEDURE dbo.TestOLEAutomationOfSQLDMO
(
@SQLDMOHost VARCHAR(40) = NULL
,@Db VARCHAR(100)
,@ObjectType VARCHAR(1) -- T=tables; I=indexes
,@ScriptFilePath VARCHAR(90)
,@DebugPrinting VARCHAR(1) = 'N'
)
AS
/*
** =============================================================================
** T-SQL Code Name: TestOLEAutomationOfSQLDMO
** T-SQL Type: PROCEDURE
** Author: Jeff Burton
** Date Created: 08/18/2004
** Purpose: Stored procedure to test OLE automation sp's
** for SQLDMO.
**
** Modifications History
** Date By Modification
** ---------- --- ------------
**
** =============================================================================
*/
DECLARE @objectSQLDMO int
DECLARE @ErrorStatus int
DECLARE @DestroyErrorStatus int
DECLARE @FinalErrorStatus int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @WinAuthLogin BIT
DECLARE @ConnectString VARCHAR(80)
DECLARE @ScriptExecString VARCHAR(200)
DECLARE @SQLExecString VARCHAR(200)
DECLARE @ScriptType VARCHAR(80)
DECLARE @Script2Type VARCHAR(80)
DECLARE @MethodName VARCHAR(80)
DECLARE @PropertyName VARCHAR(80)
DECLARE @TableName VARCHAR(80)
DECLARE @Q VARCHAR(1)
-- Create a SQLServer object.
SET NOCOUNT ON
SET @Q=CHAR(39)
SET @ScriptFilePath='"'+@ScriptFilePath+'"'
SET @SQLDMOHost=ISNULL(@SQLDMOHost,'(local)')
SET @WinAuthLogin=1
-- First, create the SQLDMO object.
SET @MethodName='sp_OACreate'
SET @PropertyName=''
SET @DestroyErrorStatus=0
EXEC @ErrorStatus = sp_OACreate 'SQLDMO.SQLServer', @objectSQLDMO OUT
IF @ErrorStatus <> 0
-- Report the error.
BEGIN
EXEC sp_OAGetErrorInfo @objectSQLDMO, @src OUT, @desc OUT
SELECT ErrorCode=convert(varbinary(4),@ErrorStatus)
, <A href="mailto
ropertyName=@PropertyName">PropertyName=@PropertyNameGOTO END_ROUTINE
END
ELSE
-- An SQLDMO object is successfully created.
BEGIN
-- Set a property.
SET @MethodName='sp_OASetProperty'
SET @PropertyName='HostName'
EXEC @ErrorStatus = sp_OASetProperty
@objectSQLDMO, @PropertyName, @SQLDMOHost
IF @ErrorStatus <> 0 GOTO CLEANUP
-- Get the hostname property set previously using an output parameter.
SET @MethodName='sp_OAGetProperty'
SET @PropertyName='HostName'
EXEC @ErrorStatus = sp_OAGetProperty @objectSQLDMO
, @PropertyName
, @property OUT
IF @ErrorStatus <> 0
GOTO CLEANUP
ELSE
IF ISNULL(@DebugPrinting,'N')='Y'
PRINT 'Returned hostname is:'+@property+char(10)
-- Call a connect method using windows authentication.
SET @MethodName='sp_OASetProperty'
SET @PropertyName='LoginSecure'
EXEC @ErrorStatus = sp_OASetProperty
@objectSQLDMO, @PropertyName, @WinAuthLogin
IF @ErrorStatus <> 0 GOTO CLEANUP
SET @MethodName='Connect'
SET @PropertyName=''
EXEC @ErrorStatus = sp_OAMethod @objectSQLDMO
, @MethodName, NULL
, @SQLDMOHost, NULL, NULL
IF @ErrorStatus <> 0 GOTO CLEANUP
-- Verify the connection.
SET @MethodName='VerifyConnection'
SET @PropertyName=''
EXEC @ErrorStatus = sp_OAMethod @objectSQLDMO, @MethodName, @return OUT
IF @ErrorStatus <> 0
GOTO CLEANUP
ELSE
IF ISNULL(@DebugPrinting,'N')='Y'
PRINT 'Verified connection status:'+@return+char(10)
-- Generate a script to create a table
SET @MethodName='Script'
SET @PropertyName=''
IF @ObjectType='T'
SET @ScriptType = '"'+CAST((
256 | -- append all scripting objects to same file
1 | -- DROP option
4 | -- CREATE option
64 -- don't return script to caller
-- (write to script file only)
) AS VARCHAR(80))+'"'
IF @ObjectType='I'
SET @ScriptType = '"'+CAST((
256 | -- append all scripting objects to same file
-- 1 | -- DROP option (for primary object)
--4 | -- CREATE option (for primary object)
64 | -- don't return script to caller
-- (write to script file only)
73736
) AS VARCHAR(80))+'"'
SET @Script2Type = '"0"' -- no scripting options
SET @SQLExecString
= 'DECLARE script_cursor CURSOR FOR SELECT name FROM ' + @db
+ '..sysobjects WHERE type = '+@Q+'U'+@Q+' ORDER BY Name'
EXEC (@SQLExecString)
OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @TableName
WHILE (@@fetch_status <> -1)
BEGIN
SET @ScriptExecString
= 'Databases("<A href="mailto:'+@db+'"
.Tables("'+@TableName+'".Script">'+@db+'").Tables("'+@TableName+'").Script '+'('+@ScriptType+','+@ScriptFilePath+')'
IF ISNULL(@DebugPrinting,'N')='Y'
PRINT '@ScriptExecString: '+@ScriptExecString+char(10)
EXEC @ErrorStatus
= sp_OAMethod
@objectSQLDMO
,@ScriptExecString
IF @ErrorStatus <> 0
GOTO CLEANUP
ELSE
IF ISNULL(@DebugPrinting,'N')='Y'
PRINT 'Scripting of table'+@TableName+' worked ok.'+char(10)
FETCH NEXT FROM script_cursor INTO @TableName
END
CLOSE script_cursor
DEALLOCATE script_cursor
END
CLEANUP:
-- Check whether an error occurred.
IF @ErrorStatus <> 0
BEGIN
-- Report the error.
EXEC sp_OAGetErrorInfo @objectSQLDMO, @src OUT, @desc OUT
SELECT ErrorCode=convert(varbinary(4),@ErrorStatus)
, <A href="mailto
ropertyName=@PropertyName">PropertyName=@PropertyNameEND
-- Destroy the object.
BEGIN
SET @MethodName='sp_OADestroy'
SET @PropertyName=''
EXEC @DestroyErrorStatus = sp_OADestroy @objectSQLDMO
-- Check if an error occurred.
IF @DestroyErrorStatus <> 0
BEGIN
-- Report the error.
EXEC sp_OAGetErrorInfo @objectSQLDMO, @src OUT, @desc OUT
SELECT ErrorCode=convert(varbinary(4),@ErrorStatus)
, <A href="mailto
ropertyName=@PropertyName">PropertyName=@PropertyNameEND
END
END_ROUTINE:
IF @ErrorStatus <> 0
SET @FinalErrorStatus=@ErrorStatus
ELSE
SET @FinalErrorStatus=@DestroyErrorStatus
RETURN @FinalErrorStatus
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply