Data Backup and Recovery

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

jeffbennett
4,697 Views

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

2 REPLIES 2

jeffbennett
4,697 Views

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

ivan_huter
4,697 Views

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.

Public