<< Click to Display Table of Contents >> Navigation: Administration > System > Back & Restore |
Access
Administration / Databases / Backup & Restore
Explanation
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. ExpressMaintenance 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. ExpressMaintenance and ExpressUtility 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 ExpressMaintenance |
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 |
ExpressMaintenance 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.
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 ExpressMaintenance 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 ExpressMaintenance 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. |
ExpressMaintenance 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.
Special Network Instructions
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.
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).
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.
Other commercial backup software is available which includes options for backing up SQL databases. We have developed ExpressSQL for unattended backups and other SQL database management options including shrinking and optimizing the database. ExpressSQL allows you to schedule unattended backups and email confirmation of the backup. For more details on ExpressSQL and to download a free evaluation copy, please visit the website at www.ExpressSQL.com.