2 Replies Latest reply: Feb 27, 2013 5:03 PM by ivan.huter RSS

SQL Script to dynamically create job that includes all non-sys databases in backup

jeffbennett Novice
Currently Being Moderated

Hi,

 

Before I reinvent the wheel here, has anyone created a SQL Job that polls sys.sysdatabases, and creates the BACKUP statement for NETApp on the fly.  In most cases, this shouldn't be needed because we know which databases are on the server, but we do have a few servers where the application actually creates new databases based on size of the previous database.  It would be great to be able to make sure those databases are being backed up, too, without having to go in and recreate the backup in SnapManager.

 

So, for example, a database server has SnapManager installed and is currently backing up appdb1, appdb2, and appdb3.  Based on some configuration of the application, it is determined that there is a need for appdb4, so it is created.  The job script I am looking for would run every time there is a scheduled backup, and in this case, would see the list of databases, including the new database, and incorporate that into a new backup statement to be executed.

 

Does anyone have something like that?

 

Thanks

 

Jeff Bennett

Saint Louis, MO

  • Re: SQL Script to dynamically create job that includes all non-sys databases in backup
    jeffbennett Novice
    Currently Being Moderated

    I answered it myself.  Here is a script that will create a job on your database server.  Here are some assumptions:

    1. You have already run the SnapManager Configuration Wizard to configure SnapManager

    2. You have altered script to use your <domainname>\<yourlogin> in line 26 of the script below

    3. You have altered script to use your specific location of the SmsqlJobLauncher.exe file

    4. You have modified the script to retain backups and log backups as you deem appropriate.  They are set to 2 and 2 in this script. See line 70

    5. You have modified the script to log to the location you deem appropriate.  See line 86

    6. You can create multiple scripts by modifying the name and items in step 4, as well as changing the value 'standard' to 'daily', or 'weekly'

    Good Luck

     

    USE

    [msdb]

    GO

    /****** Object: Job [_DBA_SMSQL_BACKUP_HOURLY] Script Date: 12/10/2010 11:29:14 ******/

    BEGIN

    TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/10/2010 11:29:14 ******/

     

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

     

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_DBA_SMSQL_BACKUP_HOURLY',

    @enabled =1,

    @notify_level_eventlog =2,

    @notify_level_email =0,

    @notify_level_netsend =0,

    @notify_level_page =0,

    @delete_level =0,

    @description =N'job creates a backup statement using SnapManager syntax, and iteratively attaches database names to the statement as needed. Excludes all system databases.',

    @category_name =N'[Uncategorized (Local)]',

    @owner_login_name =N'<yourdomain>\<yourlogin>', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [SnapManager for SQL Server Backup Step] Script Date: 12/10/2010 11:29:14 ******/

     

    EXEC

    @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SnapManager for SQL Server Backup Step',

    @step_id =1,

    @cmdexec_success_code =0,

    @on_success_action =1,

    @on_success_step_id =0,

    @on_fail_action =2,

    @on_fail_step_id =0,

    @retry_attempts =0,

    @retry_interval =0,

    @os_run_priority =0, @subsystem=N'TSQL',

    @command =N'

    SET NOCOUNT ON

    DECLARE @FileName varchar(255)

    DECLARE @File_Exists int

    DECLARE @DBCount int

    DECLARE @DBCounter int

    DECLARE @DBName varchar(50)

    SELECT @DBCount = count(*) from master.dbo.sysdatabases

    WHERE name NOT IN (''master'',''msdb'',''model'',''tempdb'')

    AND CONVERT(sysname,databasepropertyex(name,''status'')) =''ONLINE''

    AND CONVERT(sysname,databasepropertyex(name,''updateability'')) =''READ_WRITE''

    --PRINT @DBCount

    SELECT @FileName = ''C:\Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe''

    EXEC master.dbo.xp_fileexist @FileName, @File_Exists OUT

    IF @File_Exists = 1

    BEGIN

    SET @DBCounter = 1

    DECLARE @BackupCmd varchar(500)

    SET @BackupCmd = ''C:\"Program Files\NetApp\SnapManager for SQL Server\SmsqlJobLauncher.exe" new-backup svr '''''' + @@SERVERNAME + '''''' -d '''''' + @@SERVERNAME + '''''', '''''' + cast(@DBCount as varchar(3)) + '''''',''

    DECLARE strategy_cursor CURSOR FOR

    SELECT name from master.dbo.sysdatabases

    WHERE name NOT IN (''master'',''msdb'',''model'',''tempdb'')

    AND CONVERT(sysname,databasepropertyex(name,''status'')) =''ONLINE''

    AND CONVERT(sysname,databasepropertyex(name,''updateability'')) =''READ_WRITE''

    ORDER BY name

    OPEN strategy_cursor

    FETCH NEXT FROM strategy_cursor INTO @DBName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @DBCounter < @DBCount SET @BackupCmd = @BackupCmd + '''''''' + @DBName + '''''',''

    IF @DBCounter = @DBCount SET @BackupCmd = @BackupCmd + '''''''' + @DBName + '''''' -RetainBackupDays 2 -lb -bksif -RetainSnapofSnapInfoDays 2 -trlog mgmt standard''

    SET @DBCounter = @DBCounter + 1

    --PRINT @BackupCmd

    --PRINT @DBCounter

    FETCH NEXT FROM strategy_cursor INTO @DBName

    END

    CLOSE strategy_cursor

    DEALLOCATE strategy_cursor

    END

    --PRINT @BackupCmd

    DECLARE @rc int

    EXEC @rc = master.dbo.xp_cmdshell @BackupCmd',

    @database_name =N'master',

    @output_file_name =N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\NETAppBackup.log',

    @flags =0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

     

         IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    GO

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

  • Re: SQL Script to dynamically create job that includes all non-sys databases in backup
    ivan.huter CertifiedPlus Novice
    Currently Being Moderated

    Jeff,

    I know this is an old post, but in case somebody else stumbles up on this with similar requirements here's an easy way to solve this. SMSQL will create consistent backup of ANY database as long as it shares the same volume (and is connected to the same SQL instance) as the 'known' database. This means you just need to specify the name of the database you know exist and all other databases, created on the fly by your application, will benefit from this by sharing the same NetApp storage. Hope this makes sense.

More Like This

  • Retrieving data ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points