Backing up the database at regular intervals is vital to minimize recovery time in the event of hardware failure or other catastrophic event. Although MS SQL Server Standard and Enterprise Editions provide an automatic backup mechanism through the SQL Agent Service, MS SQL Server Express 2005 users must either manually backup the database, create a backup script and add a new task to Windows Task Scheduler, or use a 3rd party backup application (recommended) as described below.

Select the topic to continue:

Backup the database

Restore an existing backup

Backup

Method 1: 3rd Party Application (recommended)

A very easy to use and reliable method is a free application named SQLBackupAndFTP. SQLBackupAndFTP is SQL Server backup software that runs scheduled backups of SQL Server or SQL Server Express databases, zips the backups, stores them on a network or on a FTP server, removes old backups, and sends an e-mail confirmation on job's success or failure. SQL Backup and FTP must be installed on the PC running SQL Server. Download here.

Method 2: Scheduled Task (for advanced users)

1. Advanced users may create a SQL script by opening a new query editor window SQL Server Management Studio Express and saving it is a file. This script contains commands to backup the database, similar to:

--AmigoPOSBackup.sql
 
DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)
SET @Path = '\\POS1\E$\SQLServer\2008\Backups\'
SET @FileName = 'AmigoPointOfSale_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName
 
BACKUP DATABASE AmigoPointOfSale
TO DISK = @FullPath
WITH INIT

 

2. Then create a batch file to run the script as shown below:

sqlcmd -S POS2\SQL2008 -u -i E:\SQLExpressBackup\AmigoPOSBackup.sql

3. Finally, create a scheduled task to run the batch file in the Accessories - System Tools folder in your Start Menu

Method 3: Manual Backup

To manually backup the database, open SQL Server Management Studio Express, connect to the server, right-click on the database and select Tasks - Back Up... as shown below.

Enter a user-friendly name for the backup. You may also wish to set an expiration date for the backup file if backups are routinely created.

Click Add... to browse for a destination. You may wish to use an external device for added protection.

Restore

To restore a previously backed up database, open SQL Server Management Studio Express, connect to the server and right-click on the database and select Tasks - Restore - Database..., as shown below.

Select the database to restore to from the drop-down list and click From device under Source for restore. Click browse to locate the backup and add it to the list. Ensure that the box under the Restore column is checked and click OK to complete the restoration.