I’m using SQL Express 2008 for development on my development machines, I’m saving the full version for the servers and not on development.

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.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DatabaseBackup](
	[Name] [varchar](128) NOT NULL,
	[BackupFlagFull] [varchar](1) NOT NULL,
	[BackupFlagLog] [varchar](1) NOT NULL,
	[RetentionPeriodFull] [datetime] NOT NULL,
	[RetentionPeriodLog] [datetime] 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

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

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

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.