Backup and Restore SQL Server and MSDE Databases
 The
backup and restore features provide an important safeguard for protecting
data stored in Microsoft SQL Server databases.
Compress SQL Server backups and create individual table
backups
Compare & synchronize SQL Server schemas & data
Schedule SQL Server jobs with e-mail alerts
Tune slow SQL Server code and eliminate blocks and
deadlocks
Database Backup
To backup a database, select the Databases node in the Server Explorer pane
then, in the Items pane, right click the database you want to backup. In the
Backup File box, enter the path and file name of your backup file then click
Backup to perform the backup. Note that the path and file name you enter in this
box are relative to the server. If you are connecting to the local server, the
backup file will be created on the computer you are working on. If you are
connecting to a remote server (LAN, WAN, or Internet), the backup file will be
created on the remote server (for example, C:\MyBackups\DB1.bkp would be created
on the C:\ drive of the remote server). To perform a backup from one computer
and leave the backup file on another computer, you need to use mapped network
drives.
For example, if you want to perform a backup of a database on computer A
and leave the backup file on computer B, then computer A has to be able to
access computer B’s hard drive via a mapped network drive (for example, X:\). The
account used to run the SQL Server service on computer A needs to have write
privileges to X:\ . The default “system” account does not
have privileges to other computers.
If your database is hosted on the Internet and the use mapped network drives are not
allowed by your database hosting provider, ask you hosting provider to create a
folder for your backups on the server and give you the path of that folder so
you can use it in your backup. Your provider should also be able to give you FTP
access to that folder so you can download your backup files to your local
computer.
Backup Files Containing Multiple Backup Devices
SQL Server backup files can contain more than one backup device. Backup files
generated by Teratrax Database Manager can contain one backup device only. When
restoring a database from a backup file that contains multiple backup devices,
Teratrax Database Manager restores the first backup device in the file.
Database Restore
To restore a database, right
click the Databases node in the Server Explorer pane and select "Restore..." to
open the Restore Database dialog box. The dialog box contains the following:
-
Backup File: Enter the path
of the backup file you want to restore. If you are connected to the "(local)"
server, you can click the browse button next to this edit box to locate the
backup file. If you are connected to any other server, the browse button will
be disabled, in which case you have to type the path and file name of the
backup file manually. The path and file name you enter are relative to the
remote server (for example, C:\Backup\MyBackupFile.bkp references the C: drive
on the server machine not the local computer).
-
Restore Database As:
Enter the name that you want to give to the database that will be restored. If
you are restoring a database with the same name as a currently existing
database, Teratrax Database Manager will overwrite the current database. If
the name you enter in the "Restore Database As" box is not the same as any
database that currently exists, the restored database will be created with the
new name irrelevant of the original name (useful if you are creating another
copy of a database).
-
Move Files To: This edit box
is equivalent to the WITH MOVE option in the RESTORE DATABASE command. When
you perform a database backup, the location (folder) of the physical database
files are saved in the backup file itself. When you do a restore, the server
reads this information and attempts to recreate the physical database files in
the same folder it came from. This can cause a problem if the original folder
does not exists on the server anymore or if the backup file is from a
different server with a folder path that does not exist on the current server.
To override the original folder, specify a new path in the "Move Files To"
edit box. Leaving this edit box empty will default to the original folder
path. The "Move Files To" edit box is crucial if you want to create a copy of
a database using backup and restore. If you do not specify a valid folder path
in the "Move Files To" edit box, the server will generate an error saying that
the files could not be created (because they are already in use by the
original database).
Restoring a Database from a Backup File on a Remote Server to the Local
Server
To restore a database from a file that
is located on a different computer (for example, restoring from a mapped network
drive), the Windows account used to start the SQL Server service has to be a
non-system account with full control privileges to the backup file.
Restoring a Database from a Local File to a Remote Server
If you are restoring from a local file to a remote server, use a mapped
network drive that points to your local drive from the remote server. The
Windows account used to start the remote SQL Server service has to be a
non-system account with full control privileges to the backup file.
Restoring Backup Files on Older Versions of SQL Server
You cannot restore a SQL Server 2000 backup file on a SQL Server 7 instance.
Likewise, you cannot restore a SQL Server 2005 file on a SQL Server 2000 or SQL
Server 7 instance.
Zipped Backup and Zipped Restore
The Zipped Backup and Zipped Restore options work in almost the same way
as a regular backup and restore except for compression. The backup file in a
Zipped Backup or Zipped Restore operation is compressed using the gzip
compression algorithm in order to save disk space. The Zipped Backup
operation compresses the backup file by up to 90% allowing you to create
more backups using the same disk space. Zipped backups and restores are
supported on local servers only and cannot be scheduled. The name of the
server must start with "(Local)". The compression algorithm does
not support backup files larger than 4GB.
|
