Backing up Stored procedures, Triggers, etc | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Backing up Stored procedures, Triggers, etc


What’s the best way to backup custom stored procs, triggers, jobs, alerts?
Can the security settings and db configuration settings be backed up at all? Thank you
Eli
If you take FULL backup of database it will take care of things defined.
Intermittently you can script SPs, Triggers, Jobs and keep them in safe place. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
You can automate the scripting of your procs and associated settings using the SQL-DMO API. Search the web, theres plenty of prewritten ones you can use.
for everything listed, i agree to perform a full backup of the database in question. if it is a custom item here and there you can use this proc …
/**
write cursor to generate scripts for multiple objects
**/
/**
create in db to be used in
**/
/** if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_genscript]’)
and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[sp_genscript]
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO **/ CREATE PROCEDURE sp_genscript
@ServerName varchar(30),
@DBName varchar(30),
@ObjectName varchar(50),
@ObjectType varchar(10),
@TableName varchar(50),
@ScriptFile varchar(255)
AS DECLARE @CmdStr varchar(255)
DECLARE @object int
DECLARE @hr int SET NOCOUNT ON
SET @CmdStr = ‘Connect(‘[email protected]+’)’
EXEC @hr = sp_OACreate ‘SQLDMO.SQLServer’, @object OUT –Comment out for standard login
–EXEC @hr = sp_OASetProperty @object, ‘LoginSecure’, TRUE — Uncomment for Standard Login
EXEC @hr = sp_OASetProperty @object, ‘Login’, ‘sql_access’
EXEC @hr = sp_OASetProperty @object, ‘password’, ‘~Wonka!’
EXEC @hr = sp_OAMethod @object,@CmdStr
SET @CmdStr =
CASE @ObjectType
WHEN ‘Database’ THEN ‘Databases("’
WHEN ‘Procedure’THEN ‘Databases("’ + @DBName + ‘").StoredProcedures("’
WHEN ‘View’ THEN ‘Databases("’ + @DBName + ‘").Views("’
WHEN ‘Table’THEN ‘Databases("’ + @DBName + ‘").Tables("’
WHEN ‘Index’THEN ‘Databases("’ + @DBName + ‘").Tables("’ + @TableName + ‘").Indexes("’
WHEN ‘Trigger’THEN ‘Databases("’ + @DBName + ‘").Tables("’ + @TableName + ‘").Triggers("’
WHEN ‘Key’THEN ‘Databases("’ + @DBName + ‘").Tables("’ + @TableName + ‘").Keys("’
WHEN ‘Check’THEN ‘Databases("’ + @DBName + ‘").Tables("’ + @TableName + ‘").Checks("’
WHEN ‘Job’THEN ‘Jobserver.Jobs("’
END SET @CmdStr = @CmdStr + @ObjectName + ‘").Script(5,"’ + @ScriptFile + ‘")’
EXEC @hr = sp_OAMethod @object, @CmdStr
EXEC @hr = sp_OADestroy @object
GO SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO /**
typical use exec sp_genscript ‘servername’, ‘database name ‘, ‘object name’, ‘object type *see above*’,’table name’, ‘path and file name’ exec sp_genscript ‘mysqlserver’, mydb’, ‘mytable’, ‘table’, ‘mytable’, ‘c:share able.sql’
**/
]]>