SQL Express backup & maintenance

support@ascassociates.biz , Sales Enquiries

SQL Express backup & maintenance

Access databases need to be compacted and repaired on a regular basis, whilst SQL Server offers a level of stability and performance beyond Access, SQL Server databases still need maintenance.

If your application uses a full version of the product, then you already have the abilitity to create and schedule maintenance and backups, but if you have SQL Express you will need to find a way to do this. This article provides assitance in formulating your own backup and maintenance procedures.

You will need a) a method to backup the database, and b) a method to performance maintenance on the database.

Creating A Backup Device

One simple solution is to create a backup device, these are held in the sys.backup_devices table. The example script below creates a backup device.

USE master
GO
--To Remove Device if required
sp_dropdevice 'northwinddata'
--To Create Devices
EXEC sp_addumpdevice 'disk', 'northwinddata',
'C:\SQLBackups\northwinddata.bak'
-- To See Devices
SELECT * FROM sys.backup_devices

Rebuild The Indexes

Indexes in a databse eventually become fragmented and rebuilding the indexes creates space for growth as new data gets added, allowing the data to be entered into the index in the correct place. the CLUSTERED index governs the physical distribution of data, and again this fragments and must be rebuilt. the code below scans all the indexes and rebuilds them, allowing for growth (data entry) of new records controlled by the fill factor.

-- >>>>>>>>>>>>>>>>>>>CHANGE THIS TO USE YOUR DATABASE
USE northwind
-- find all the tables in the database
-- and rebuild all the indexes on each table
-- also run the database consistency checker on each table
DECLARE @TargetDatabase AS VARCHAR(50)
DECLARE @FillFactor AS INT
DECLARE @TableName AS sysname
DECLARE @Command AS VARCHAR(200)
-- Set the table fill factor
SET @FillFactor = 80
PRINT 'REBUILDING ALL INDEXES AND RUNNING CHECK ON TABLES'
PRINT '-------------------------------------------------------------------------'
DECLARE reb_cursor CURSOR
FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN reb_cursor
FETCH NEXT FROM reb_cursor INTO @TableName
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
PRINT @TableName
SET @Command = 'ALTER INDEX ALL ON [' + @TableName + '] REBUILD WITH (FILLFACTOR =' + CAST(@Fillfactor AS VARCHAR(10)) + ');'
EXEC (@Command);
SET @Command = 'DBCC CHECKTABLE ([' + @TableName + '])'
EXEC (@Command);
END
FETCH NEXT FROM reb_cursor INTO @TableName
END
CLOSE reb_cursor
DEALLOCATE reb_cursor
PRINT '-------------------------------------------------------------------------'
GO

Shrink The Database Files

This step in the maintenance shrinks the physical files.

--SHRINK DATABASE
DECLARE @fileid as INT
DECLARE @Command AS VARCHAR(200)
PRINT 'SHRINKING ALL FILES'
PRINT '-------------------------------------------------------------------------'
DECLARE file_cursor CURSOR
FOR
SELECT file_id FROM sys.database_files
OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @fileid
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
PRINT @fileid
SET @Command = 'DBCC SHRINKFILE (' + CAST(@fileid AS VARCHAR(10)) + ')'
EXEC (@Command);
END
FETCH NEXT FROM file_cursor INTO @fileid
END
CLOSE file_cursor
DEALLOCATE file_cursor
PRINT '-------------------------------------------------------------------------'
GO

Backup Database Files

This step backs up the database files copying them to an appropriate location as specified in the backup device.

-- BACKUP DATABASE & TRUNCATE THE LOGFILE
-- This assumes simple recovery model and just truncates the log file
PRINT 'BACKING UP DATABASE AND LOG FILE'
PRINT '-------------------------------------------------------------------------'
DECLARE @DatabaseName AS VARCHAR(200)
SELECT @DatabaseName = [Name] from sys.database_files WHERE type_desc = 'rows'
-- >>>>>>>>>>>>>THEN NEXT 2 LINES NEED CHANGED, for northwinddata, substitute the name of your
-- backup device
BACKUP DATABASE @DatabaseName TO northwinddata
RESTORE FILELISTONLY FROM northwinddata
PRINT '-------------------------------------------------------------------------'
GO

Download Code

The SQL Express 2005 Script File can be downloaded from this link.

MUST Express Helper automates your backups, performance optimisation and execution of custom stored procedure when working with SQL Server Express. This windows service based product provides unattended execution intergrating seemlessly into your server.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz