SQL Server Backup , Job with embeded SQL

Question

I have a SQL Server 2008 R2 instance containing lots of databases that come and go. Each time a database leaves the server or another one pops in, I need to go through a killer routine:

I run this script in SQL Server Management Studio

SET NOCOUNT ON

SELECT 'BACKUP DATABASE [' +
name +
'] TO  DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.HART\MSSQL\Backup\' + 'BCK\' +name + 
'.bak'' WITH NOFORMAT, INIT,  NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master','tempdb','model','msdb','blah','blah2')

SET NOCOUNT OFF

,I get the result to text and I paste it inside the SQL Agent Job Step window.

Is there a chance that I could put this script inside the SQL Server Agent Jobs so every time a database leaves or gets into my server, I won't have to manually change my Job Step definition?

Thank you!

Answer 1

In the sense, that script creates one backup and you are overwriting it each time it is executed....then you can most definitely just throw it into one step of a single job to run on your schedule. Just understand that you only have one full backup for each database, if you have requirement to go back in history to restore you cannot do it.

One tweak you can make to that script to get additional backups for each day is just throw in a date value.

A little bit more robust solution is offered by Ola's SQL Server Backup scripts that handles gracefully when databases are dropped and created. Just setting the parameter @database='USER_DATABASES' will handle this for you, along with the other required parameters of course.

Answer 2

Agree with above answer. I would highly suggest to use Ola's backup and index maintenance solution along with integrity check solution as well.

To address your situation, you just need to use dynamic sql that can be run as sql agent job as shown below :

SET NOCOUNT ON
declare @sqltext nvarchar(max) = N''
select @sqltext = @sqltext+ N'BACKUP DATABASE [' +
name +
'] TO  DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.HART\MSSQL\Backup\' + 'BCK\' +name + 
'.bak'' WITH INIT,  NAME = N''Full Database Backup'', STATS = 10 ;'+char(10) + 'GO'+char(10) 
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master','tempdb','model','msdb','blah','blah2')

print @sqltext

-- review the output and then uncomment out below 
-- This will actually run the sql 
-- exec sp_executesql @sqltext
SET NOCOUNT OFF