October 28, 2008 at 4:21 pm
We have an application that dynamically generates a new database (not sure why).
For that particular server, we have one maintenance plan for all user databases.
When a new database is generated by the application, then the subsequent tran logs fail with the following error:
BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, I'm wondering if there is any way with ddl triggers to somehow kick off a database backup when a new database is created.
I tried to create a trigger, but it failed.
CREATE TRIGGER test_ddl_trigger
ON ALL Server
FOR CREATE_DATABASE
AS
Declare @data xml,
@dbname varchar(50),
set @data=Eventdata()
set @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
BACKUP DATABASE @dbname
TO DISK = '..filepath...'
However, I got the following error:
Msg 3021, Level 16, State 0, Procedure mk_test_ddl_trigger, Line 19
Cannot perform a backup or restore operation within a transaction.
Any suggestions?
October 28, 2008 at 4:36 pm
Use your DDL trigger to dynamically build and start a job that performs the backup operation. The job should also delete itself upon successful completion so that you don't end up with numerous one-shot jobs.
October 28, 2008 at 4:38 pm
Or use a single backup job, drop parameters in a table, let the job run pick up parameters (db name, type of backup, path to file, etc.) from the table. Then kick off the job from the trigger after dropping data in the table.
You can then have the job update the table, which would ensure that you had some history for backtracking things that happened.
October 28, 2008 at 4:49 pm
That would work also.
October 28, 2008 at 7:55 pm
Check this post. Hope it helps.
October 31, 2008 at 4:42 pm
Hey thanks for the suggestions!
I'm pretty sure I have the basics working fine, now.
I do have one additional question. You wrote: "You can then have the job update the table, which would ensure that you had some history for backtracking things that happened."
I think I'm going to do that. But what kind of check can I put in the job to make sure the backup job completes successfully? If the job doesn't complete successfully, I don't want to update the table saying that the new db was backed up. Do I wrap the backup job in a try/catch? Or is there a return code that I can use?
Here's my job:
DECLARE @dbname varchar(30),
@backupFilePath varchar(150),
@datevar datetime,
@filedate char(12),
@x varchar(150)
Use TriggerTest
select @dbname = DBName from tbl_newDB
select @backupFilePath = filePath from tbl_newDB
--where (right now this is the only record in my db
set @datevar = GetDate()
SET @filedate = CONVERT(VARCHAR(15), @datevar ,112) + LEFT(REPLACE(convert(varchar,getdate(),108),':',''),4)
set @backupcommand ='BACKUP DATABASE ' + @dbname + ' TO DISK = ''' + @backupFilePath + '\'+@dbname+'\' + @dbname + '_db_' + @filedate + '.bak'''
--select @backupcommand
exec (@backupcommand)
Update tbl_newDB SET backupJob = @datevar
May 6, 2009 at 3:57 am
Hi Steve,
Are you able to post your script to do this as I'm very weak at coding?
Thanks
September 8, 2009 at 5:38 am
My suggestion is to look in the system tables to see if a database is backed up fully or not. We do this before the log backup:
--use Filer
--go
--IF OBJECT_ID('spF_BackupNewDB') IS NOT NULL
-- DROP PROCEDURE dbo.spF_BackupNewDB
--GO
--CREATE PROCEDURE dbo.spF_BackupNewDB
ALTER PROCEDURE dbo.spF_BackupNewDB
@ipaddressVARCHAR(100) = 'SQLBACKUP1'-- #param @ipaddress IP address or name of SQL Server instance to backup. If blank, backup to local machine.
,@share1VARCHAR(100)= 'BACKUP'-- #param @share1 Name of first network share or local folder to use for storing backup (required).
,@share2VARCHAR(100) = ''-- #param @share2 Name of second network share or local folder to use for storing backup (optional).
,@share3VARCHAR(100) = ''-- #param @share3 Name of third network share or local folder to use for storing backup (optional).
,@share4VARCHAR(100) = ''-- #param @share4 Name of fourth network share or local folder to use for storing backup (optional).
,@db_nameVARCHAR(100) = ''-- #param @db_name Database to backup. If blank, all databases will be backed up.
AS
-- #descThis SP backs up one or all databases that has never been backed up (but tempdb). This SP is the same as the standard backup SP, spF_Backup, but the cursor definition, which only selects databases never backed up. It does not backup databases In Load, Not Recovered, In Recovery, Suspect, Offline, ReadOnly, In EmergencyMode and Shut Down. This job is supposed to run before any transaction log backup so you can get a regular scheduled check and backup. If you do not specify @ipaddress (backup server), backup will be done to the local server. Example: EXEC Filer.dbo.spF_BackupNewDB @ipaddress='',@share1='C:\SQLFULLBackup',@share2='',@share3='',@share4='',@db_name=''. See code for more run examples. NOTE: SQL server name is automatically added to @share1-4 input.
-- #authorLennart Gerdvall
-- #version2.1, WORKS in SQL 2000, 2005 and 2008.
/*
Examples:
Backup all new databases (never backed up) to local server and folder C:\SQLFULLBackup
EXEC Filer.dbo.spF_BackupNewDB @ipaddress='',@share1='C:\SQLFULLBackup',@share2='',@share3='',@share4='',@db_name=''
Backup all new databases (never backed up) to remote server and shared folder BACKUP
EXEC Filer.dbo.spF_BackupNewDB @ipaddress='SEVIS002BU',@share1='BACKUP',@share2='',@share3='',@share4='',@db_name=''
Backup all new databases (never backed up) to default server and default folder
EXEC Filer.dbo.spF_BackupNewDB
@ipaddress=DEFAULT
,@share1=DEFAULT
,@share2=''
,@share3=''
,@share4=''
,@db_name=''
*/
DECLARE
@NAME1VARCHAR(500),
@NAME2VARCHAR(500),
@NAME3VARCHAR(500),
@NAME4VARCHAR(500),
@DBNAMEVARCHAR(500),
@SSQL VARCHAR(1024),
@DBMode VARCHAR(50),
@StatusMsg VARCHAR(1024),
@testcolpos INT,
@testslash VARCHAR(2),
@lengthofshareINT,
@eofshareVARCHAR(200),
@lenshareINT,
@slashposINT,
@DIR1NVARCHAR(1000),
@DIR2NVARCHAR(1000),
@DIR3NVARCHAR(1000),
@DIR4NVARCHAR(1000)
--- Check that input for SHARE-variables are entered in sequence - else abort with error mess.
BEGIN
IF @share1 = '' -- first shared folder
BEGIN
PRINT 'QUITTED - first back backup catalog not specified!'
PRINT 'Please enter catalog names in sequence!'
RETURN
END
IF @share1 = '' AND @share2 ''
BEGIN
PRINT 'QUITTED - first back backup catalog not specified!'
PRINT 'Please enter catalog names in sequence!'
RETURN
END
IF (@share1 = '' OR @share2 = '') AND @share3 ''
BEGIN
PRINT 'QUITTED - first or second back backup catalog not specified!'
PRINT 'Please enter catalog names in sequence!'
RETURN
END
IF (@share1 = '' OR @share2 = '' OR @share3 = '') AND @share4 ''
BEGIN
PRINT 'QUITTED - first, second or third back backup catalog not specified!'
PRINT 'Please enter catalog names in sequence!'
RETURN
END
END
--- Check if input for SHARE-variables ends with a backslash. In that case, remove it.
BEGIN
SELECT @testslash = RIGHT(@share1,1)
IF @testslash = '\' OR @testslash = '/'
BEGIN
SELECT @lengthofshare = LEN(@share1)
SELECT @lengthofshare = (@lengthofshare - 1)
SELECT @share1 = SUBSTRING(@share1, 1, @lengthofshare)
END
SELECT @testslash = RIGHT(@share2,1)
IF @testslash = '\' OR @testslash = '/'
BEGIN
SELECT @lengthofshare = LEN(@share2)
SELECT @lengthofshare = (@lengthofshare - 1)
SELECT @share2 = SUBSTRING(@share2, 1, @lengthofshare)
END
SELECT @testslash = RIGHT(@share3,1)
IF @testslash = '\' OR @testslash = '/'
BEGIN
SELECT @lengthofshare = LEN(@share3)
SELECT @lengthofshare = (@lengthofshare - 1)
SELECT @share3 = SUBSTRING(@share3, 1, @lengthofshare)
END
SELECT @testslash = RIGHT(@share4,1)
IF @testslash = '\' OR @testslash = '/'
BEGIN
SELECT @lengthofshare = LEN(@share4)
SELECT @lengthofshare = (@lengthofshare - 1)
SELECT @share4 = SUBSTRING(@share4, 1, @lengthofshare)
END
END
BEGIN
IF @db_name ''
-- A named databased never backed up, is to be backed up!
DECLARE BACKUP_NEW_DATABASE_CUR CURSOR FOR
SELECT a.[name] from master.dbo.sysdatabases a
left join
(select database_name, max(backup_finish_date) backup_date
from msdb.dbo.backupset b
where b.type = 'D'
group by database_name) b
on a.[name] = b.database_name
where a.[name] = @db_name and backup_date IS NULL AND a.[name] NOT LIKE '%_ss_%'
ELSE
-- All databases never backed up, are to be backed up!
DECLARE BACKUP_NEW_DATABASE_CUR CURSOR FOR
SELECT a.[name] from master.dbo.sysdatabases a
left join
(select database_name, max(backup_finish_date) backup_date
from msdb.dbo.backupset b
where b.type = 'D'
group by database_name) b
on a.[name] = b.database_name
where a.[name] 'tempdb' and backup_date IS NULL AND a.[name] NOT LIKE '%_ss_%'
order by a.name asc
END
OPEN BACKUP_NEW_DATABASE_CUR
FETCH NEXT FROM BACKUP_NEW_DATABASE_CUR INTO @DBNAME
WHILE @@FETCH_STATUS=0
BEGIN
--Check Database Accessibility
SELECT @DBMode = 'OK'
IF DATABASEPROPERTY(@DBNAME, 'IsDetached') > 0
SELECT @DBMode = 'Detached'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsInLoad') > 0
SELECT @DBMode = 'Loading'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsNotRecovered') > 0
SELECT @DBMode = 'Not Recovered'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsInRecovery') > 0
SELECT @DBMode = 'Recovering'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsSuspect') > 0
SELECT @DBMode = 'Suspect'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsOffline') > 0
SELECT @DBMode = 'Offline'
ELSE IF (DATABASEPROPERTY(@DBNAME, 'IsReadOnly') > 0 AND @db_name = '') -- If a particular database is specified, try any way.
SELECT @DBMode = 'IsReadOnly'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsEmergencyMode') > 0
SELECT @DBMode = 'Emergency Mode'
ELSE IF DATABASEPROPERTY(@DBNAME, 'IsShutDown') > 0
SELECT @DBMode = 'Shut Down (problems during startup)'
IF @DBMode 'OK'
BEGIN
SELECT @StatusMsg = 'Unable to backup ' + @DBNAME + ' - database is in ' + @DBMode + ' state.'
PRINT @StatusMsg
GOTO NEXTDB
END
ELSE
BEGIN
SET @SSQL = 'BACKUP DATABASE ' + '[' + @DBNAME +']' + ' TO ' + CHAR(13) + ' DISK = '
--- Add instance name to backup folder and create the output folder if it does not exist.
-------------------------------------------------------------------------------------------------------
IF @share1 > ''
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share1)
SET @eofshare = REVERSE(@share1)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share1 = UPPER(@share1) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share1 = UPPER(@share1)
END
ELSE SET @share1 = UPPER(@share1) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
--Check if local or network folder
SELECT @testcolpos = CHARINDEX(':', @share1)
IF @testcolpos = 0 SELECT @DIR1 = '\\'+ @ipaddress+'\' + @share1
ELSE SELECT @DIR1 = @share1
-- Create the topdirectory if it doesn't exist
EXEC Filer.dbo.spF_CreateFolder @DIR1
END
IF @share2 > ''
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share2)
SET @eofshare = REVERSE(@share2)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share2 = UPPER(@share2) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share2 = UPPER(@share2)
END
ELSE SET @share2 = UPPER(@share2) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
--Check if local or network folder
SELECT @testcolpos = CHARINDEX(':', @share2)
IF @testcolpos = 0 SELECT @DIR2 = '\\'+ @ipaddress+'\' + @share2
ELSE SELECT @DIR2 = @share2
-- Create the topdirectory if it doesn't exist
EXEC Filer.dbo.spF_CreateFolder @DIR2
END
IF @share3 > ''
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share3)
SET @eofshare = REVERSE(@share3)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share3 = UPPER(@share3) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share3 = UPPER(@share3)
END
ELSE SET @share3 = UPPER(@share3) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
--Check if local or network folder
SELECT @testcolpos = CHARINDEX(':', @share3)
IF @testcolpos = 0 SELECT @DIR3 = '\\'+ @ipaddress+'\' + @share3
ELSE SELECT @DIR3 = @share3
-- Create the topdirectory if it doesn't exist
EXEC Filer.dbo.spF_CreateFolder @DIR3
END
IF @share4 > ''
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share4)
SET @eofshare = REVERSE(@share4)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share4 = UPPER(@share4) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share4 = UPPER(@share4)
END
ELSE SET @share4 = UPPER(@share4) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
--Check if local or network folder
SELECT @testcolpos = CHARINDEX(':', @share4)
IF @testcolpos = 0 SELECT @DIR4 = '\\'+ @ipaddress+'\' + @share4
ELSE SELECT @DIR4 = @share4
-- Create the topdirectory if it doesn't exist
EXEC Filer.dbo.spF_CreateFolder @DIR4
END
-------------------------------------------------------------------------------------------------------
IF @share1 '' -- First shared folder
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share1)
SET @eofshare = REVERSE(@share1)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share1 = UPPER(@share1) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share1 = UPPER(@share1)
END
ELSE SET @share1 = UPPER(@share1) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
-- Check if local or network folder
select @testcolpos = CHARINDEX(':', @share1)
IF @testcolpos = 0--- Net folder
BEGIN
SELECT @NAME1 = CHAR(39) + '\\'+ @ipaddress+'\' + @share1 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_1.BAK' + CHAR(39)
SET @SSQL = @SSQL + @NAME1 + CHAR(13)
END
ELSE--- Local disc
BEGIN
SELECT @NAME1 = CHAR(39) + @share1 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_1.BAK' + CHAR(39)
SET @SSQL = @SSQL + @NAME1 + CHAR(13)
END
END
IF @share1 '' AND @share2 '' -- Second shared folder
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share2)
SET @eofshare = REVERSE(@share2)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share2 = UPPER(@share2) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share2 = UPPER(@share2)
END
ELSE SET @share2 = UPPER(@share2) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
-- Check if local or network folder
select @testcolpos = CHARINDEX(':', @share2)
IF @testcolpos = 0-- Net folder
BEGIN
SELECT @NAME2 = CHAR(39) + '\\'+ @ipaddress+'\' + @share2 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_2.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME2 + CHAR(13)
END
ELSE-- Local disc
BEGIN
SELECT @NAME2 = CHAR(39) + @share2 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_2.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME2 + CHAR(13)
END
END
IF @share1 '' AND @share2 '' AND @share3 '' -- Third shared folder
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share3)
SET @eofshare = REVERSE(@share3)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share3 = UPPER(@share3) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share3 = UPPER(@share3)
END
ELSE SET @share3 = UPPER(@share3) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
-- Check if local or network folder
select @testcolpos = CHARINDEX(':', @share3)
IF @testcolpos = 0--- Net folder
BEGIN
SELECT @NAME3 = CHAR(39) + '\\'+ @ipaddress+'\' + @share3 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_3.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME3 + CHAR(13)
END
ELSE--- Local disc
BEGIN
SELECT @NAME3 = CHAR(39) + @share3 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_3.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME3 + CHAR(13)
END
END
IF @share1 '' AND @share2 '' AND @share3 '' AND @share4 '' -- Fourth shared folder
BEGIN
-- Add serverinstance name if needed
SET @lenshare = LEN(@share4)
SET @eofshare = REVERSE(@share4)
SET @slashpos = CHARINDEX('\', @eofshare)
SET @slashpos = @slashpos - 1
IF @slashpos > 0
BEGIN
IF UPPER(SUBSTRING(@eofshare,1,@slashpos)) UPPER(REVERSE(REPLACE(@@SERVERNAME, '\', '_')))
SET @share4 = UPPER(@share4) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
ELSE SET @share4 = UPPER(@share4)
END
ELSE SET @share4 = UPPER(@share4) + '\' + UPPER(REPLACE(@@SERVERNAME, '\', '_'))
-- Check if local or network folder
select @testcolpos = CHARINDEX(':', @share4)
IF @testcolpos = 0-- Net folder
BEGIN
SELECT @NAME4 = CHAR(39) + '\\'+ @ipaddress+'\' + @share4 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_4.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME4 + CHAR(13)
END
ELSE--- Local disc
BEGIN
SELECT @NAME4 = CHAR(39) + @share4 + '\' + REPLACE(@@SERVERNAME, '\', '_') + '_' + @DBNAME+'_4.BAK' + CHAR(39)
SET @SSQL = @SSQL + ', DISK = ' + @NAME4 + CHAR(13)
END
END
SET @SSQL = @SSQL + ' WITH FORMAT'-----, INIT, SKIP, STATS'
EXECUTE (@SSQL)
--PRINT @SSQL
END
NEXTDB:
FETCH NEXT FROM BACKUP_NEW_DATABASE_CUR INTO @DBNAME
END
CLOSE BACKUP_NEW_DATABASE_CUR
DEALLOCATE BACKUP_NEW_DATABASE_CUR;
--GO
Lennart Gerdvall
payex.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply