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

Top of Page

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

Top of Page

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

Top of Page

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

Top of Page

Download Code

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

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

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