Tuesday, August 08, 2006

Automating SQL 2005 Express Edition Backups

SQL2005 Express Edition w/ Management Studio Express looks like it is a very nice DB and management console, it falls down in one place (that I can see) and that is it is missing the maintenance Wizard that was found on SQL 2000.

Now I cant complain as it is a free application and a small stored procedure can restore most of the functionality as regards backups.

Not all the procedures that are required are activated by default and it is very simple to re-activate them. simply copy and paste this code into a text editor and save it as c:/enableprocs.sql

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now execute this command from a "run" prompt
sqlcmd -S .\SQLExpress -i c:\enableprocs.sql

this enables the Ole Automation Procedures and the XP_CMDshell.

Get the expressmaint.sql script here

extract the script and save the .sql file to c:\expressmaint.sql

execute this command

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

This installs the expressmaint stored procedure which is necessary for this backup method.

PARAMETERS

@database Y NONE
The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases and ALL_SYSTEM which will process all system databases
@optype Y NONE

The type of maintenance operation to be performed. Valid values are

  • DB - Full Database Backup
  • DIFF - Differential Database Backup
  • LOG - Log Backup
  • CHECKDB - Database Integrity Check
  • REINDEX - Rebuild all indexes
  • REORG - Reorganize all indexes
@backupwith N NULL
Specify additional backup options as documented in BOL for the BACKUP WITH command
@backupfldr N NULL
The base folder to write the backups to. Sub folders will be created for each database
@verify N 1
Indicates whether to verify the backup file.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@verifywith N NULL
Specify additional verify options as documented in BOL for the VERIFY WITH command
@dbretainunit N NULL
The unit of measure for the @dbretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old backup files are kept
@dbretainval N 1
The time period or number of copies of old backups to keep
@report N 1
Indicates whether to produce a report of the maintenance carried out.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@reportfldr N NULL
The folder where maintenance reports are written to if @report = 1
@rptretainunit N NULL
The unit of measure for the @rptretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old reports are kept
@rptretainval N 1
The time period or number of copies of old reports to keep
@checkattrib N 0
Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent deletion of files that have not been backed up onto tape.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@delfirst N 0
Indicates whether to delete old backups prior to doing the current backup. This is not advisable but can be useful if disk space is limited.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@debug N 0
Indicates whether print out debug information such as the commands generated and the contents of the temporary tables used in the procedure.
Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
This is the script that I use to backup all user databases, save it as  fullbackup.sql in c:\
exec expressmaint
@database = 'ALL_USER',
@optype = 'DB',
@backupfldr = 'd:\backups',
@reportfldr = 'd:\reports',
@verify = 1,
@dbretainunit = 'weeks',
@dbretainval = 1,
@rptretainunit = 'weeks',
@rptretainval = 1,
@report = 1

If you look at the parameters you will see that it backups all user databases, it puts the backups in d:/backups and the reports in d:/reports
it also retains reports and backups for 1 week.

Then just set up a scheduled task in windows to run
sqlcmd -S .\SQLExpress -i c:\fullbackup.sql
on whatever schedule you like. I recommend daily backups if not every 12hrs.

4 comments:

dcox@sd68.bc.ca said...

I can follow most of this except this:
"save it as fullbackup.sql in c:exec expressmaint"

I assume this is not a location, since later you suggest running it as:
"sqlcmd -S .\SQLExpress -i c:\fullbackup.sql"

When I try running the fullbackup.sql script I get:
"Msg 137, Level 15, State 2, Server MUSICDB\ACP, Line 1
Must declare the scalar variable "@database".", which makes sense as the script does not seem to call the expressmaint stored procedure.

Daniel said...

hi dcox, there's actually a line break missing, the expressmaint gets executed right at the top of fullbackup.sql.
So instead of
save it as fullbackup.sql in c:exec expressmaint
it should be
save it as fullbackup.sql in c:\
exec expressmaint
....

hope this helps!

Anonymous said...

Hi,

I tried to do this but I got:

Changed database context to 'master'.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
Stored Procedure created successfully.

Using MS SQL Server 2005 Express (named instance) & MS Windows Server 2003 Std.

Doen anybody have any ideas?

regs.
Lakend

daspeac said...

I have heard about another pst password recovery tool. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues