Create Backups (including unattended) of the Database

SQL is an extremely powerful database engine that utilizes advanced technology to provide unmatched performance. The SQL data files (example: ExpressMaintenance_Data.MDF & ExpressMaintenance_Log.LDF) normally reside in the MSSQL7\\\\Data folder and are in use at all times the SQL database engine is running. Therefore, they cannot be backed up without using software to perform the sql backup to a single file. Express Maintenance includes such an option.

How SQL Backups Work

Backups with an SQL database engine are different in the sense that you cannot just backup the data files. You must run an SQL statement that backs up the database and log file to a single file. The resulting file can then be archived like any other file. Express Maintenance and Express SQL provide a convenient interface for performing backups. The examples below assume you are at the server. Otherwise, you will need to use UNC paths. You may also need to adjust your SQL Server startup settings, see Special Network Instructions below.

Steps To Backup

1. Run Express Maintenance
2. Click on Administration / Backup & Restore
3. Click on the Backup button
4. Select the folder where the backup file is to be created (example: C:\\\\MSSQL\\\\Backup)
5. Enter the file name of the destination backup file (example: ExpressMaintenance.zip) Do not include spaces.
6. Click the Save button and then click Yes to proceed

Express Maintenance will execute the proper SQL statement to backup the database to a backup file with the same name but .bak extension (example: ExpressMaintenance.bak). Once the backup is completed, the file will be compressed into the specified file (example: ExpressMaintenance.zip). You can now copy the file to another computer, CD or other removable media.

Note: If you have trouble backing up across the network you will likely need to change the SQL setting to allow file creation via the network. See Special Network Instructions below.  If you receive a timeout error, see Timeout Error Notes below.

Steps To Restore

1. Get the file (example: ExpressMaintenance.zip) on the hard drive of the destination computer.
2. Make sure that all other users are not using the database that resides on the destination server. The database cannot be restored if it is in use.
3. Run Express Maintenance at the computer where the restore is to be placed. You must run the restore at the destination sql server computer and the file must be on that computer.
4. Click on Administration / Backup & Restore
5. Click on the Restore button
6. Select the folder where the backup file resides (example: C:\\\\MSSQL\\\\Backup). Note, the file must be on the hard drive and not removable media as it will need to be decompressed.
7. Click Yes button to proceed.
8. Click Yes button to acknowledge overwrite warning and proceed.
9. The application will close and the Restore dialog box will appear.
10. Make sure the server, database, sa password and file name is correct. Note that the “sa” password is not the password in Express Maintenance but rather the “sa” password in SQL server. By default, this password is blank (no password). You may need to get this password from your system administrator.
11. Click the Restore button to perform the restore.

Express Maintenance will decompress the origin file into a temporary file. Once the decompression is completed, it will execute the proper sql statement to restore the database using the temporary file.

Common Problems

The most common problems experienced are:

• Users often attempt to manually copy the database files. This cannot be done since they are always in use by the SQL server.
• Backups fail when users are backing up to and from a workstation other than where the sql data resides. This can be done but special settings must be made at the server to allow for this. See Special Network Instructions below.
• Users attempt to backup or restore directly to a removable media such as a CD. You must backup to the hard drive and then copy the file from the hard drive to the removable media. This is because the backup file is created and then compressed / decompressed and the original file is removed.
• Users attempt to backup and restore databases from differing version of SQL Server. Databases are not backwards compatible from SQL 2000 to SQL 7. Databases are upwards compatible from SQL 7 to SQL 2000.
• Users often attempt to restore the database to a server while other users are connected and using the database.
• Timeout error is received because database takes longer than the normal 30 second query time.  See Timeout Error Notes below for details.

The normal setup for SQL Server should work fine for most local backup needs. However, if you need to have the database backup created on another computer on the local network other than the server, you will need to make some manual adjustments. You will only need to make these adjustments one time. The problem is that the SQL Server starts up using a regular login without any network access. Therefore, it is unable to write the backup file to a remote computer on the network.

Special Network Instructions

The following steps must be taken at the server to have the SQL Server startup using the special Network Service account when starting up. This enables the SQL Server to write backup files across the network. Please follow these steps to have the SQL Server service start with network access:

1. Have all users exit programs that use the SQL Server.
2. At the SQL Server computer, open the Service Manager by double clicking or through the menu.
3. Click the Stop button to stop the SQL Server.
4. On the Start menu, point to Settings, and then click Control Panel.
5. Double click on the Services icon or you may need to go to Administrative Tools / Services.
6. This opens the Service Management screen.
7. Locate the MSSQL Server item and Double click to open the properties.
8. In the MSSQL Server Properties dialog box, click on the Log On tab.
9. Select the This account for the Login as option.
10. Click the Browse button.
11. Click the Advanced button.
12. Click the Find Now button.
13. Scroll through user list and click on the NETWORK SERVICE user.
14. Click OK to close the Advanced Select User window. If prompted for a password, obtain this from you system administrator.
15. Click OK to close the Select User window.
16. Click OK to close the Properties window.
17. Close the Services Management window.
18. Back in the SQL Service Manager, Start the SQL Service.

Also, ensure that you have proper read/write access to the file system on the remote machine you will be backing up to. (Refer to windows documentation on how to set directory/file permissions or see your system administrator).

Timeout Error Notes

You may receive a SQL timeout error when attempting a backup.  This error indicates that the data backup time exceeds the normal timeout allowance of 30 seconds for the execution of the SQL statement.  This is normally the result of a slower computer or a very large database.  To work around this error, increase the Timeout Seconds in the upper panel or in the server registration.  Try setting the seconds to 180 and attempt the backup over.  Also, you might consider a faster server computer.  Also, if you are running SQL 7 or MSDE, you should consider upgrading to the latest service pack which is available free.

Unattended Backups

It may be desirable to have unattended backups for the purpose of writing to tape drives and other removable media after hours. If you are running the full version of SQL Server, you should refer to your SQL Server documentation for information on backing up databases. SQL Server includes the ability to setup routine maintenance tasks including backups. Likewise, most commercial backup software includes options for backing up SQL databases.

Finally, you can use our Express SQL to perform backups including unattended scheduled backups. For complete details on Express SQL, contact the sales team.