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

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.

[sql]

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

[/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 + ‘_’

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.

Submit to Hacker News

[NOTE] This post has been imported to Hugo from a Wordpress blog. It may be broken, missing images, code snippets or other information.

[NOTE] updated April 5, 2010. It's 3220 days old . This article may have outdated content.