Backup all databases (SQL Express) with a single T-Sql

One of the things I often see when people talk about the express disadvantages is the lack of support for JOBS so you cannot create a custom backup for your database.

Well, in this post I will show you how you can create a single database on your development machine that will be responsible for all of the backups, create a table that will log the backups and a stored procedure that will do the work.

In a later post, I will also show how you can integrate this into a command line and then into your nightly backup strategy.

The most beautiful part of this solution is that it is totally free of charge and uses custom T-Sql, and the command line is using a custom tool supplied by the EXPRESS package.

So, without talking too much, let’s dive into the solution.

First, we will create the database.

sql SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.DatabaseBackupName] varchar NOT NULL, BackupFlagFull(1) NOT NULL, BackupFlagLog(1) NOT NULL, RetentionPeriodFull NOT NULL, RetentionPeriodLog NOT NULL, PRIMARY KEY NONCLUSTERED ( Name ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY GO SET ANSI_PADDING OFF GO /sql

Now, we have the database all set-up, let’s create the stored procedure

sql SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Procedure dbo.s_BackupAllDatabases @Path varchar(128) , @Type varchar(4) – Full / Log as set nocount on declare @sql varchar(1000)

-- Get all database names
create table #DBName
	(
	ID int identity (1,1) ,
	Name varchar(128) not null ,
	RetentionPeriod datetime null
	)

insert #DBName
	(Name)
select name
from master..sysdatabases

-- Include any new databases in the backup
insert DatabaseBackup
	(
	Name ,
	BackupFlagFull ,
	BackupFlagLog ,
	RetentionPeriodFull ,
	RetentionPeriodLog
	)
select #DBName.Name ,
	'Y' ,
	'N' ,
	'2 jan 1900' , -- default 2 days
	'1 jan 1900'
from #DBName
	left outer join DatabaseBackup
		on DatabaseBackup.Name = #DBName.Name
where DatabaseBackup.Name is null
and lower(#DBName.Name) <> 'tempdb'

-- Remove any non-existant databases
delete DatabaseBackup
where not exists
	(
	select *
	from #DBName
	where #DBName.Name = DatabaseBackup.Name
	)

delete #DBName

create table #ExistingBackups
	(
	Name varchar(128) ,
	ID int identity (1,1)
	)

-- loop through databases

declare @Name varchar(128) , @RetentionPeriod datetime , @LastBackupToKeep varchar(8) , @ID int , @MaxID int

insert #DBName
	(Name, RetentionPeriod)
select Name, case when @Type = 'Full' then RetentionPeriodFull else RetentionPeriodLog end
from DatabaseBackup
where (@Type = 'Full' and BackupFlagFull = 'Y')
or (@Type = 'Log' and BackupFlagLog = 'Y')

select @MaxID = max(ID) ,
	@ID = 0
from #DBName

while @ID < @MaxID
begin
	-- get next database to backup
	select @ID = min(ID) from #DBName where ID > @ID

	select @Name = Name ,
		@RetentionPeriod = RetentionPeriod
	from #DBName
	where ID = @ID

	-- Delete old backups
	delete #ExistingBackups
	select @sql = 'dir /B ' + @Path
	select @sql = @sql + '"' + @Name + '_' + @Type + '*.*"'

	insert #ExistingBackups exec master..xp_cmdshell @sql

	if exists (select * from #ExistingBackups where Name like '%File Not Found%')
		delete #ExistingBackups

	select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
	delete #ExistingBackups where Name > @Name + '_' + @Type + '_' + @LastBackupToKeep

declare @eID int , @eMaxID int , @eName varchar(128)

	-- loop round all the out of date backups
	select 	@eID = 0 ,
		@eMaxID = coalesce(max(ID), 0)
	from 	#ExistingBackups

	while @eID < @eMaxID
	begin
		select @eID = min(ID) from #ExistingBackups where ID > @eID
		select @eName = Name from #ExistingBackups where ID = @eID

		select @sql = 'del ' + @Path + '"' + @eName + '"'
		exec master..xp_cmdshell @sql, no_output
	end
	delete #ExistingBackups

	-- now do the backup
	select @sql = @Path + @Name + '_' + @Type + '_'
			+ convert(varchar(8),getdate(),112) + '_'
			+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
	if @Type = 'Full'
		backup database @Name
			to disk = @sql
	else
		backup log @Name
			to disk = @sql
end

GO /****** Object: Check CK__DatabaseB__Backu__023D5A04 Script Date: 04/05/2010 16:49:17 ***/ ALTER TABLE dbo.DatabaseBackup WITH CHECK ADD CHECK ((BackupFlagFull=’N’ OR BackupFlagFull=’Y’)) GO /*** Object: Check CK__DatabaseB__Backu__03317E3D Script Date: 04/05/2010 16:49:17 ***/ ALTER TABLE dbo.DatabaseBackup WITH CHECK ADD CHECK ((BackupFlagLog=’N’ OR BackupFlagLog=’Y’)) GO /sql

Now, we have our database, we have our stored procedure.

Let’s execute the stored procedure.

Right click on the stored procedure and click on “Execute…”

Now, set the parameters

The first parameter is the library and the second is the backup type, you can use full, log

That’s it, you have a backup up and running.

in the next post I will show how you can do it from command line and integrate it into your backup program post-backup action.

<img src="http://feeds.feedburner.com/~r/KensoDev-en/~4/frsukHqUtNg" height="1" width="1"/>


Share this article: