SQL Server Backups

Requirements

1. BackupAssist™ v2 or later
2. Windows NT, 2000,, XP or 2003 Server
3. Microsoft SQL Server 7.0 or later

BackupAssist™ will perform live, online backups of your SQL Server databases and include these backups in your overall backup job (together with your files, system state, and/or Exchange server).

These SQL Server backups are in the standard SQL Server Backup Format ? meaning that you will be able to perform a restoration on any SQL Server, without any additional software.

You may also restore individual databases to different SQL Servers as well.

This design gives you maximum flexibility when you need to do a restoration, because your SQL Server does not need access to your tape drive (or other backup device). All you need to restore a database is the SQL Database Backup File. With the SQL Database Backup File, you can also perform advanced operations like recovery using the SQL Server transaction logs for point-in-time restorations (Advanced SQL Server database administrators will be familiar with this).

Important ? If You Are Backing Up A Remote SQL Server

Note: BackupAssist™ accepts blank passwords

If in doubt, please refer to the online HTML Help file that is installed with BackupAssist™. Detailed instructions are given on how to setup a valid SQL Server logon to perform the backups.

Simply click the 'Add SQL Job' [See Below Screenshot] button and fill in the SQL server details for each instance of SQL server that you would like to have BackupAssist™ backup

All of these SQL server instances will then be appended to your overall backup job in BackupAssist™

Step one: Install the SQL server client tools on both the SQL server machine and the Backup server machine

• Install the client tools from the SQL Server Installation CD
• Choose to install the database server
• You may receive an alert message if your Operating System does not support the SQL Server installation - just click 'OK'
• Choose to install the client server tools
• Select the type of installation - if you are installing on the backup server, you only need to install the client tools

Step two: Create a network share that both the SQL server and Backup server have access to

• It is best to create the network share directory on the actual SQL server itself to avoid any authentication issues between the SQL server and the Backup server. For example: create a new directory on your SQL Server in C:\SQLBackup, and then share that directory with the share name of SQLBackup. Assign your backup user full permissions for that share, and full NTFS file permissions as well.
• Since a Backup User Identity is defined with BackupAssist™ which is used to logon and perform the backup, as long as that user has permissions/access rights to write and read from the SQL shared directory you will have no problems backing up to and from this directory.

Tip: If you're concerned about security, and creating an extra share for your SQL server backups, then you can make the share hidden by adding a $ to the end of the share name. For example, a share called SQLBackup$ will be hidden, meaning that people viewing your server in the Windows Explorer won't be able to see that share.

Step three: Now setup BackupAssist™ to backup your remote SQL server

• Access the SQL server tab of BackupAssist™ (below screenshot)
• Enter in the server details and authentication information of your SQL server (eg. the name of the remote server you are backing up from)
• Select the databases you wish to backup
• Enter in the network share directory you defined earlier that both your Backup server and SQL server have access to into the SQL Backup Directory field.
• Ensure that you enter the directory in UNC format as follows:
  \\ServerName\ShareName
  In this case: \\TEST-2000-SVR\SQLBackup

============================================================ SQL Server Backup report

Backup of database Northwind succeeded:
Processed 320 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 321 pages in 1.283 seconds (2.044 MB/sec).
Backup of database ABC-DEF succeeded:
Processed 80 pages for database 'ABC-DEF', file 'ABC-DEF_Data' on file 1.
Processed 1 pages for database 'ABC-DEF', file 'ABC-DEF_Log' on file 1.
BACKUP DATABASE successfully processed 81 pages in 0.520 seconds (1.262 MB/sec).

============================================================

1. Retrieving your SQL Database Backup File from your backup media
2. Restoring your database from the backup file.

Step One: Retrieving Your SQL Database Backup File

Please note: detailed instructions on how to use Windows Backup are beyond the scope of this White Paper. Please refer to the Restoring Files Using Windows Backup video training and White Paper documentation.

The SQL Database Backup File will be listed under the directory that you selected as the SQL Backup Directory. If you are restoring the database on a remote server, then copy this file over to your SQL Server.

Please note: the screenshots shown here come from SQL Server 2000. They may vary for different versions of SQL Server.

Now right-click below the Databases branch, and select All Tasks > Restore Database?

Just below it, choose to restore From Device, and then click the ?Select Devices?? button.

To locate your file, click the ??? button. Then navigate to find your SQL Database Backup File. This is the file that you restored in Step 1.

Simply click ?OK? until you get back to the Restore Database window.

If you are restoring over an existing database, you should check the ?Force restore over existing database? checkbox

If you are restoring the database to a new database name, then you should also explicitly name your database files. You should review and modify the entries under the ?Restore As? heading as displayed in the screenshot above.

For advanced administrators: you may want to roll forward your database using the transaction logs - set the appropriate Recovery completion state to suit your needs.