Full Backup Based on Creation Date

1

I need a T-SQL Script that will check the creation date of a database, and if the creation date is one hour or less, I want a job to run to perform a full backup of that database. I am using SQL Server 2012. I have an application that creates a workspace on the front end, but creates a SQL Database on the back end. Ideally when this SQL Database is created, I need to perform a full backup on it. Problem is this could happen in the middle of the night, weekends, etc. Any thoughts on this?

2

The key is the "create_date" column in sys.databases table/view; for example:

DECLARE @database_name nvarchar(128)
DECLARE @backup_file_name varchar(255)

DECLARE cursor_db CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE create_date >= DATEADD(HOUR, -1, GETDATE())

OPEN cursor_db

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursor_db INTO @database_name
    IF @@FETCH_STATUS <> 0
        IF @@FETCH_STATUS = -1
            BREAK
        ELSE
            CONTINUE;
    SET @backup_file_name = '...'
    /*PRINT @database_name*/
    BACKUP DATABASE @database_name TO DISK = @backup_file_name WITH INIT /*, COMPRESSION, ... */
END /*WHILE*/

DEALLOCATE cursor_db
1 Like