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?
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