September 2004 Blog Posts
EDIT: Please see my new version of the isp_Backup stored procedure.
Here's the new version for the backup transaction log stored procedure:
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup_TLog
--
-- AUTHOR : Tara Duggan
-- DATE : May 12, 2004
--
-- INPUTS : @Path - location of the backups
-- @Retention - number of days to retain transaction log backups
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure performs a transaction log backup on the non-log
-- shipped user databases that do not have SIMPLE set as the recovery model.
--
-- EXAMPLES (optional) : EXEC isp_Backup_TLog @Path = 'C:\MSSQL\Backup\', @Retention = 5
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
--
----------------------------------------------------------------------------------------------------
CREATE PROC isp_Backup_TLog
(@Path VARCHAR(100), @Retention INT...
EDIT: Please see my new version of the isp_Backup stored procedure.
I decided to finish posting the new versions of my database maintenance routines. Here's the new version for backing up your databases using SQL LiteSpeed:
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup_LS
--
-- AUTHOR : Tara Duggan
-- DATE : December 18, 2003
--
-- INPUTS : @Path - location of the backups
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure performs a full backup on all of the user databases
--
-- EXAMPLES (optional) : EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\', @Retention = 5
----------------------------------------------------------------------------------------------------
CREATE PROC isp_Backup_LS
(@Path VARCHAR(100), @Retention INT = 2)
AS
SET NOCOUNT ON
DECLARE @Now CHAR(14) -- current date in the...
A common mistake when first creating a trigger is thinking that the inserted or deleted tables will contain only one row. This is not true. However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table. So you must code the trigger to handle multiple rows. In the forums of SQLTeam.com, we often see trigger code like this:
CREATE TRIGGER trg_Table1 ON Table1 For UPDATEAS
DECLARE @var1 int, @var2 varchar(50)
SELECT @var1 = Table1_ID, @var2 = Column2FROM inserted
UPDATE Table2SET SomeColumn = @var2WHERE Table1_ID = @var1
The above trigger will only work for the last row in...