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