I recently had a customer that needed a database to serve as the backend for a small Document Management system. SQL Server Standard or Enterprise would have been overkill for such a small application.
I decided to use the free SQL Server 2005 Express Edition. The problem is that it is free for a reason. See some of the limitations listed here.
http://www.dotnetspider.com/tutorials/SqlServer-Tutorial-158.aspx
In order to make this work I had to come up with a way to schedule backups. The following stored procedure takes as parameter the type of backup (db OR Log) and will back up all databases on the target server to the specified target folder.
Create PROCEDURE usp_Backup@Type as varchar(10),
@Path as varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dbs as TABLE
(
id int identity,
name varchar(50),
recovery_model varchar(25)
)
Insert into @dbs Select name, recovery_model_desc from sys.databases where name <> 'tempdb'
Declare @dest as varchar(max)
Declare @sql as varchar(max)
Declare @dbname as varchar(50)
Declare @recovery_model as varchar(50)
Declare @retention_days as int
Declare @cnt as int
Declare @id as int
Select @cnt = count(*) from @dbs
set @id = 1
while @id < @cnt
Begin
Select @dbname = name from @dbs where id = @id
Select @recovery_model = recovery_model from @dbs where id = @id
set @dest = @path + '\' + @dbname + '_' + @type + '_'
+ convert(varchar(8),getdate(),112) + '_'
+ replace(convert(varchar(8),getdate(),108),':','') + '.bak'
if @type = 'DB'
Backup Database @dbname to disk = @dest
else
If @recovery_model <> 'simple'
Backup Log @dbname to disk = @dest
set @id = @id + 1
End
END
GO
To schedule it I simply created a text file like so.
Go
Exec usp_backup @Type='Log', @Path='C:\SQL_Backups'
Go
And scheduled the following to execute using Windows Task Scheduler.
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S Server -E -i C:\SQL_Scripts\AllDBBackups.sql
Works like a charm so far.
