How do you backup an unknown number of SQL server databases

How do you backup an unknown number of SQL server databases?

Consider a scenario where an application you don’t control is using your SQL server as a place to store data, and this application is creating databases on the fly.

This can be the case for several types of applications.

Now, using the built-in maintenance-plan wizard will only get you so far, since it will only apply to the databases already existing on the server at time of creation, not to databases created after the plan, so it will have to be continuously maintained.

That’s a bad solution, and if forgotten, some databases will not be backed up.

I will choose to give you two alternatives to solve this problem, the first is using a cursor to accomplish what we want, - and yes, i can already hear you mumbling about “cursor” and words like “ never”, “devils work”, “performance” and “must be some other way” and so on.. – Well, there are several ways to accomplish what we want, and a cursor is one of them, so don’t sharpen the prongs of your pitchfork just yet, i will explain later. Another way is using 2005’s new row_number() function and a in-memory table variable, and a standard execute (@sqlcommand) and this is also a very effective way.

Using a cursor for a backup task that is to run maybe once every night, or every Sunday after midnight is quite ok, the performance problem with cursors is widely agreed upon and I will not argue that they are not, but this is not a procedure that will be called by sixty thousand web users every minute, if that was the case, I would also abandon cursors, but in this case, it’s not such a bad idea.
So to the first code snippet: we will call this: DoDatabaseBackup1

create proc DoDatabaseBackup1
as
declare @DBname varchar(254)
declare @Fixedpath varchar(254)
declare @FileName varchar(254)
set @Fixedpath = 'c:\sqlbackup\'

declare getDBName cursor for
select [name] from sys.databases
where [name] NOT IN ('master','model','msdb','tempdb')

open getDBName
fetch next from getDBName into @DBname
while @@fetch_status = 0
begin
set @fileName = @Fixedpath + @DBname + '_' + convert(varchar(8),getdate(),112) + '.bak'
backup database @DBname to disk = @fileName
fetch next from getDBName into @DBname
end

close getDBName
deallocate getDBName

This procedure will loop through any user-databases there is on the server and place a backup file of each on the location of your choice, simple as that. Not big, not difficult to understand and works like a charm. Create a scheduled job under sql-server agent and you should be home free. (and the cursor performance problem is not what will take time here, the backup of each database will take considerably more out of the server…)

But, since cursor IS the work of the devil :), to solution number two:

This one is called (who would guess): DoDatabaseBackup2

The logic is more or less the same, but instead of a cursor we use a table-variable and then use this to build a sql-command-string.

create proc DoDatabaseBackup2
as
declare @sqlstr varchar(max)
declare @userdbs int
declare @counter int
declare @fixedpath varchar(254)

declare @dbnames table (dbrow int,dbname varchar(254))

set @counter=1
set @sqlstr=''

set @fixedpath='c:\sqlbackup\'

insert into @dbnames
select row_number() over(order by [name]) as dbrow,[name]
from sys.databases
where [name] not in ('master','tempdb','model','msdb')

set @userdbs=@@rowcount

if @userdbs>0
begin
while @counter<=@userdbs begin select @sqlstr=@sqlstr+(select 'backup database '+dbname+' to disk = '''+@fixedpath+dbname+'_'+Convert(char(8),getdate(),112)+'.bak'';' from @dbnames where dbrow=@counter)
set @counter=@counter+1
end
end
exec (@sqlstr)

Both of this procedures will result in the same, a backup of all userdatabases regardless of name and numbers, and you don’t even have to know anything exept that there is space available on the receiving location..

Other solutions also exist, but this will do for now….

/Leo