|
Question: How
do I make backups (including unattended) of the database?
Answer:
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
-
Run
ExpressMaintenance
-
Click
on Administration / Backup & Restore
-
Click
on the Backup button
-
Select
the folder where the backup file is to be created (example:
C:\MSSQL\Backup)
-
Enter
the file name of the destination backup file (example:
ExpressMaintenance.zip) Do not include spaces.
-
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. If you receive
a timeout error, see Timeout
Error Notes below.
Steps To Restore
-
Get the
file (example: ExpressMaintenance.zip) on the hard drive of the
destination computer.
-
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.
-
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.
-
Click
on Administration / Backup & Restore
-
Click
on the Restore button
-
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.
-
Click
Yes button to proceed.
-
Click
Yes button to acknowledge overwrite warning and proceed.
-
The
application will close and the Restore dialog box will appear.
-
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.
-
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.
-
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:
-
Have
all users exit programs that use the SQL Server.
-
At the
SQL Server computer, open the Service Manager by double clicking or
through the menu.
-
Click
the Stop button to stop the SQL Server.
-
On the
Start menu, point to Settings, and then click Control Panel.
-
Double
click on the Services icon or you may need to go to Administrative Tools
/ Services.
-
This
opens the Service Management screen.
-
Locate
the MSSQL Server item and Double click to open the properties.
-
In the
MSSQL Server Properties dialog box, click on the Log On tab.
-
Select
the This account for the Login as option.
-
Click
the Browse button.
-
Click
the Advanced button.
-
Click
the Find Now button.
-
Scroll
through user list and click on the NETWORK SERVICE user.
-
Click
OK to close the Advanced Select User window. If prompted for a password,
obtain this from you system administrator.
-
Click
OK to close the Select User window.
-
Click
OK to close the Properties window.
-
Close
the Services Management window.
-
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. For more details, visit the following tech bulletin:
MSDESvcPack.htm Also,
consider upgrading to the MSDE2000 which is available for download at
the following link:
Downloads.
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 ExpressUtility to perform backups including unattended
backups. The ExpressUtility can be run from a batch or scheduler to run
unattended. For more details on using ExpressUtility for the purpose of
unattended backups, please see the ExpressUtility Help. To download
the latest releases of
ExpressUtility -
Click Here. |
