December 21, 2008 at 12:05 pm
Actually, I believe that this could also be done either with Service Broker or through Event Notifications.
Nonetheless, I agree that a SQL Agent Job such as Jeffrey is describing is probably the best choice.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 21, 2008 at 12:52 pm
Thanks guys.
i tried to find another way to resolve this issue without SQL Agent Job.
not that i have anything with SQL Server Agent jobs but it did try to find an alternative.
December 21, 2008 at 6:20 pm
How about writing a simple backup statement at the end of database creation! the other point i mentioned in initial posts. I think that should do the needful for first db backup so he doesnt hv to worry abt differential or transaction log backups. 😉
December 21, 2008 at 6:38 pm
ps (12/21/2008)
How about writing a simple backup statement at the end of database creation! the other point i mentioned in initial posts. I think that should do the needful for first db backup so he doesnt hv to worry abt differential or transaction log backups. 😉
Because, most likely the OP does not have control over the code that is performing the database create, since that creation is performed by the ERP system. Hence, the request to find some way to have the initial backup triggered by the creation of the database.
The easiest method to implement is a SQL Agent job that runs every XX minutes/hours and checks for database creation. When found, perform inital backup of the database so the transaction log and/or differential backups don't fail.
Also, you would need to add code to the transaction log and differential backup processes so they would ignore newly created databases. This is just to avoid a timing issue (i.e. database is created, and tlog/diff backup tries to run prior to initial backup process).
Other methods involve implementing a trigger on the database create event, logging the creation to a queue and then processing the queued entry. The queued entry could be processed by a service broker. But, that seems like a lot of overhead just to get an initial backup performed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2008 at 9:03 pm
Thanks Jeffery for making it so clear.
The easiest method to implement is a SQL Agent job that runs every XX minutes/hours and checks for database creation. When found, perform inital backup of the database so the transaction log and/or differential backups don't fail.
So to summarize, one needs to write this is something like this.
Create job FindNewlyCreatedDatabase
--job schedule -- run every 15/20 minutes
--job steps
--Find newly created database,
--If found, 1. Create initial backup
-- 2. Create job for full backup
-- 3. create job for transaction log backup
-- 4. create job for differential log backup (if required)
Also, you would need to add code to the transaction log and differential backup processes so they would ignore newly created databases. This is just to avoid a timing issue (i.e. database is created, and tlog/diff backup tries to run prior to initial backup process).
Do you mean we shud have a generic backup job that takes tran log backup for all databases(even for newly created ones)? in this case i dont think i need to write steps 3 and 4 in the above job(new database finding job). I'd support this if number of databases are huge and there is frequent addition/deletion of databases. what would be your suggestion on this?
OR we should have sort of maintenence plan explicitly specifying the databases for which we're supposed to take backup. In this case i think we'd require steps 3 and 4.
December 22, 2008 at 2:16 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [BACKUP_NEWDB]
on ALL server
for CREATE_DATABASE
as
begin
DECLARE @newdb VARCHAR(500)
DECLARE @BACKUPSQL NVARCHAR(500)
SET @newdb = (SELECT TOP 1 NAME FROM SYS.DATABASES ORDER BY CREATE_DATE DESC)
SET @BACKUPSQL = 'BACKUP DATABASE '+@NEWDB+' TO DISK = C:\'+@NEWDB'.BAK'
EXEC (@BACKUPSQL)
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [BACKUP_NEWDB] ON ALL SERVER
December 22, 2008 at 2:54 am
Thanks Prajapati for writing this code. I tested DDL triggers for the first time 😉
i'd to do few modifications for it to run in this line...
SET @BACKUPSQL = 'BACKUP DATABASE '+@NEWDB+' TO DISK = ''C:\'+@NEWDB+'.BAK'''
The part of the code doing actual backup is running fine 🙂
however during actual database creation, i'm getting this error.
-----------------------------------------
Msg 3021, Level 16, State 0, Line 1
Cannot perform a backup or restore operation within a transaction.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
------------------------------------------
December 22, 2008 at 7:08 am
THIS WORK BEST :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [BACKUP_NEWDB]
on ALL server
for CREATE_DATABASE
as
begin
COMMIT TRANSACTION
DECLARE @newdb VARCHAR(500)
DECLARE @BACKUPSQL NVARCHAR(500)
SET @newdb = (SELECT TOP 1 NAME FROM SYS.DATABASES ORDER BY CREATE_DATE DESC)
SET @BACKUPSQL = 'BACKUP DATABASE '+@NEWDB+' TO DISK = C:\'+@NEWDB'.BAK'
EXEC (@BACKUPSQL)
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [BACKUP_NEWDB] ON ALL SERVER
December 22, 2008 at 1:58 pm
ps (12/21/2008)
Do you mean we shud have a generic backup job that takes tran log backup for all databases(even for newly created ones)? in this case i dont think i need to write steps 3 and 4 in the above job(new database finding job). I'd support this if number of databases are huge and there is frequent addition/deletion of databases. what would be your suggestion on this?OR we should have sort of maintenence plan explicitly specifying the databases for which we're supposed to take backup. In this case i think we'd require steps 3 and 4.
I was assuming that you already had a job that was doing this. If not, then all you really need is a notification that a new database was created - then you can manually create the necessary maintenance plans for the new database.
But, if you want to automate the whole process as you outlined it above - I don't see any issues with that as well.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 12:28 am
i get this error when i create new database
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
December 23, 2008 at 2:35 am
Yes u get error, but backup id done..
u can see the backup and database is also created..
December 23, 2008 at 3:38 am
yes the backup is done,but the created database is not function no noting in it.
thanks anyway ,but i think that i will go on the sql agent and check for new database with this syntax
SELECT name from master.sys.databases
where name not in ( SELECT database_name FROM msdb.dbo.backupset) and
name not in ('tempdb')
this will check if there are new database that never been backup.
THX
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply