0

I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was created 15 mins before it is working. Anything I need alters to be able to take simultaneous multiple new database backups within the specified time (here the script job runs every 15 mins)?

Below is the script.

-- Alert on the new database(s) created in the past 15 mins
-- Replace with your recipients in line #24

-- declare the variable for interval Minutes to detect the new database(s)
DECLARE @IntervalInMinutes TINYINT -- check new database(s) in the past 15 mins(s)
SET @IntervalInMinutes = 15 -- replace with the number of mins you wish to check for new database(s)

select GETDATE()
-- check for new database(s)
IF EXISTS(  
    SELECT 1 
    FROM master.sys.databases db 
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb') 
BEGIN -- new database(s) exist(s)

DECLARE @DBname varchar(64)

SELECT  @DBname =  [name]
    FROM master.sys.databases db 
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb'

DECLARE @Path varchar(64)
declare @dates varchar(64)
set @dates = CONVERT(date, GETDATE(),112);
SET @Path  = 'H:\Fullbackups\'+ @DBname +'_'+ @dates+'.sqb'


BACKUP DATABASE  @DBname TO  
DISK =@Path
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
--GO

P.S. I have not provided the dbmail and alert script here.

1

Use a cursor to iterate over the new databases. Like this:

DECLARE @IntervalInMinutes TINYINT = 15;

DECLARE @DBname VARCHAR(64);
DECLARE c CURSOR LOCAL FOR
SELECT  [name]
    FROM master.sys.databases db 
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb';
OPEN c;
FETCH NEXT FROM c INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @dates VARCHAR(64) = CONVERT(DATE, GETDATE(),112);
    DECLARE @Path VARCHAR(260) = 'c:\temp\'+ @DBname +'_'+ @dates+'.bak';

    PRINT 'backing up ' + @DBname + ' to ' + @Path;
    BACKUP DATABASE  @DBname TO  
    DISK =@Path
    WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

    FETCH NEXT FROM c INTO @DBName;
END

CLOSE c; 
DEALLOCATE c;

Taking a full backup will also set the differential base, so you could also take backups of all the databases which don't have a differential base set.

SELECT  [name]
    FROM master.sys.databases db 
    WHERE name != 'tempdb'
    and database_id not in 
    ( select database_id
      from sys.master_files
      where file_id = 1
        and differential_base_lsn is not null)

Beware that taking this full backup will start the log growth on any FULL RECOVERY databases. And Model (and new databases) are in FULL recovery by default. So be sure to have log backup jobs for all these databases too, or switch them to SIMPLE recovery.

| improve this answer | |
  • The Cursor is able to take three databases simultaneously, David, thanks heaps – vicky Jul 29 at 10:24
  • David, generally we have a weekly (Sunday) full backup for all databases, so only immediate full backup required here and if restore situation occurs after some time of database creation and as we have the log backup maintenance plan which will include this new database as well in it (i.e. every 15mins), so the database recovery/restore will be properly planned. – vicky Jul 30 at 3:26
2

You can use a CREATE_DATABASE Event Notification to monitor database create events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.

I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the create database event.

-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO
 
-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseCreateEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseCreateEvent ON SERVER;
END
 
-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseCreateEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseCreateEventRoute;
END
 
-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseCreateEventService'
        )
BEGIN
    DROP SERVICE DatabaseCreateEventService;
END
 
-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseCreateEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseCreateEventQueue;
END
 
--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseCreateEventQueue]
    WITH STATUS = ON;
GO
 
--  Create a service broker service receive the events
CREATE SERVICE [DatabaseCreateEventService] ON QUEUE [DatabaseCreateEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseCreateEventRoute]
    WITH SERVICE_NAME = 'DatabaseCreateEventService'
        ,ADDRESS = 'LOCAL';
GO
 
-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseCreateEvent] ON SERVER
    WITH FAN_IN
FOR CREATE_DATABASE TO SERVICE 'DatabaseCreateEventService'
    ,'current database';
GO
--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseCreateEventQueue]
   WITH STATUS=ON,
      ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseCreateEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

I then tweaked his example stored procedure that consumes the event.

Make sure you change this line in the stored procedure to be your email address

@recipients = 'YourEmailAddress'

DROP PROCEDURE [dbo].[ProcessDatabaseCreateEvents];
GO
 
CREATE PROCEDURE [dbo].[ProcessDatabaseCreateEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);
 
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;
 
    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseCreateEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away
 
    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;
        BREAK;
    END
 
    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;
 
    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');
 
    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseCreate event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName
    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseCreate Event Notification'
        ,@body = @email_message;
 
    --  Commit the transaction.  At any point before this, we could roll
    --  back. The received message would be back on the queue AND the
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END

Then, I ran a simple create database

USE [master]
GO
DROP DATABASE IF EXISTS [TestCreateDb]
GO
CREATE DATABASE [TestCreateDb]

After a few seconds or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture and process whatever you need, such as initiating the backup immediately.

| improve this answer | |
  • My script can take a database backup as soon as it creates but for a specific application a project creates three databases, in that the script is giving notification of that three database creation but taking any one of the databases backup, probably needed a cursor to take remaining new databases backups in that duration. – vicky Jul 29 at 10:08
  • @vicky - I was simply offering an alternative method for polling for a newly created database and then immediately taking a backup as quickly as possible. – Scott Hodgin Jul 29 at 10:21
  • Yeap, I agree, David's was very simple indeed, tested and it worked. Thanks for your time and inputs. – vicky Jul 29 at 10:24
0

You could also create a trigger on the "Create database". This trigger can launch a SQL job that will do the backup (that way, the trigger execution time will be really short). Everytime a database is created, the trigger will launch the job that will take the backup (giving you some kind of history at the same time). I guess this is the best way to have the backup done for each new database as soon as those databases are created, whatever the number of DBs that are created (each DB will trigger its own trigger execution).

https://www.sqlservercentral.com/forums/topic/any-ideas-on-how-to-run-an-immediate-backup-after-a-new-database-is-created

| improve this answer | |
  • just curious , will this wait for the trigger to finish to create the database. if that's the case then our time will be doubled. time for creating database + time for taking backup – Biju jose Jul 29 at 13:46
  • 1
    See my modification in the answer and the link. As you will only launch a Job, that will take nearly no time so your DB creation will not take longer and the job that will run the backup will be launch on every new DB creation. (as you create 3 DBs at the same time, I would not go with the option to have a single job but instead, create a job for each new DB. You can also create a cleanup job that would run daily to delete the old jobs) – Dominique Boucher Jul 29 at 14:02
  • yup that should work – Biju jose Jul 29 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.