Home | Firestreamer | Downloads | Support | About Us My Account | Site Map | Search | Cristalink RSS Feed  
Firestreamer-RM Getting Started Guide Firestreamer-RM Home

Microsoft SQL Server Backup

Firestreamer-RM allows Microsoft SQL Server Backup (SQLBackup) to back up and restore SQL databases directly to and from supported media.

IMPORTANT:

  • After performing a backup or restore, Microsoft SQL Server Backup may leave the Firestreamer-RM virtual tape drive locked. This effectively prevents the Microsoft Backup Utility and other software from accessing the drive, which results in the media not being recognized and other issues. If this is the case, you need to restart Microsoft SQL Server to release the tape drive. Note that the locked tape drive is not a problem in itself, as long as you use Firestreamer-RM with SQLBackup only.
  • SQLBackup does not support unattended backups that span multiple pieces of media.
  • To overcome SQLBackup limitations, we recommend backing up SQL databases with SQLBackup to .BAK files, as opposed to the virtual tape drive. Then you can back up these files with the Microsoft Backup Utility and Firestreamer-RM. The Prolog script is specifically intended to handle such a scenario.

The information below applies to the use of SQLBackup directly the Firestreamer-RM virtual tape drive. Before using Firestreamer-RM with SQLBackup, you need to perform the following steps:

  1. Enable support for SQLBackup in Firestreamer-RM
  2. Register Firestreamer-RM as a SQL backup device

You can use several different methods to back up and restore Microsoft SQL Server databases with SQLBackup. For your convenience, we have provided a few examples below. For more information, please refer to the Microsoft SQL Server documentation.

IMPORTANT: If you select Overwrite existing media on the General tab of SQLBackup, you also need to select Initialize and label media on the Options tab, otherwise SQLBackup will not work as expected.

Enabling Support for SQLBackup in Firestreamer-RM

SQLBackup uses a legacy method to detect tape drivers, which is not supported by Firestreamer-RM by default. You need to explicitly enable support for SQLBackup in Firestreamer-RM.

Furthermore, SQLBackup is not a separate executable file, but an integral part of Microsoft SQL Server. Because of this, Firestreamer-RM cannot automatically enable itself, as it has no way to detect SQLBackup running. You need to either enable Firestreamer-RM immediately prior to using SQLBackup, or keep Firestreamer-RM enabled at all times with the Enable on system startup option.

To enable support for SQLBackup in Firestreamer-RM, follow the steps below.

  1. Open the Firestreamer-RM Control Panel.
  2. On the Backup tab, select Enable on system startup.
  3. On the Settings tab, select Legacy software support.
  4. Click OK to save the changes and close the Firestreamer-RM Control Panel.
  5. Restart your computer.

Registering Firestreamer-RM as a SQL Backup Device

To register Firestreamer-RM with SQLBackup, follow the steps below.

  1. Open Windows Device Manager.
  2. Expand Tape drives, right-click Firestreamer-RM Drive, and then click Properties.
  3. Click the Tape symbolic name tab. Write down the tape symbolic name, for example, Tape0.
  4. Open SQL Server Enterprise Manager.
  5. On the left pane, expand the SQL Server instance, expand Management, and then click Backup.
  6. Right-click the right pane, and then click New Backup Device.
  7. Type FirestreamerRM in Name.
  8. Select the tape symbolic name you wrote down in step 3 (Tape0) as Tape drive name.
  9. Click OK.

Scheduling an unattended differential backup that appends to the media

By following the steps below, you can schedule an unattended differential backup that appends to the current media, provided the data that you are backing up can fit on a single piece of media.

First, you need to create a complete backup of your database.

  1. Make sure Firestreamer-RM is enabled.
  2. Open SQL Server Enterprise Manager.
  3. Right-click the database you want to back up (for example, MyDatabase), click All Tasks, and then click Backup Database.
  4. On the General tab, select Database-complete, select Tape, click Add to select either the tape symbolic name (for example, Tape0) or the FirestreamerRM backup device, and then select Overwrite existing media.
  5. On the Options tab, select Initialize and label media, and then fill in Media set name (for example, MyDatabase Media).
  6. Click OK to start the backup.

Once the complete backup of your database is done, you can schedule unattended differential backups.

  1. In SQL Server Enterprise Manager, right-click the database you want to back up (for example, MyDatabase), click All Tasks, and then click Backup Database.
  2. On the General tab, select Database-differential, select Tape, click Add to select either the tape symbolic name (for example, Tape0) or the FirestreamerRM backup device, select Append to media, and then select and set up Schedule.
  3. On the Options tab, select Check media set name and backup set expiration, and then fill in Media set name with the same name you used for the complete backup (MyDatabase Media).
  4. Click OK to submit the backup job to SQL Server Agent.

Restoring a differential backup

To restore a differential backup, follow the steps below.

  1. Make sure Firestreamer-RM is enabled.
  2. Insert the media with the backup you want to restore into your media drive.
  3. Open SQL Server Enterprise Manager.
  4. Right-click the database you want to restore (for example, MyDatabase), click All Tasks, and then click Take Offline.
  5. Right-click the database again, click All Tasks, and then click Restore Database.
  6. Select the backup sets to restore, and then click OK to start the restore.

Interactive backup with media spanning

Due to SQLBackup limitations, you cannot schedule an unattended backup that spans multiple media. However, you can use media spanning if you run SQLBackup interactively. By following the steps below, you can back up a large database that requires more than one piece of media.

  1. Make sure Firestreamer-RM is enabled.
  2. Open SQL Server Enterprise Manager.
  3. Right-click the database you want to back up (for example, Northwind), click All Tasks, and then click Backup Database.
  4. On the General tab, select Database-complete, select Tape, click Add to select either the tape symbolic name (for example, Tape0) or the FirestreamerRM backup device, and then select Overwrite existing media.
  5. On the Options tab, select Initialize and label media, then fill in Media set name (for example, Full Backup 1).
  6. Click OK to start the backup.
  7. When prompted, insert additional media. Do not click OK immediately after you inserted the new media. Allow a minute or two for the media to be recognized, and then click OK.

Using VBScript to automate SQL backups

You can use VBScript or any other programming language that supports SQL-DMO objects to automate SQL backups. Below is a sample script that performs a full backup of the Northwind database.

   dim srv
   set srv = CreateObject("SQLDMO.SQLServer")
   srv.LoginSecure = true
   srv.Connect "."

   dim bkp
   set bkp = CreateObject("SQLDMO.Backup")
   bkp.Action = 0 'SQLDMOBackup_Database
   bkp.Database = "Northwind"
   bkp.Devices = "[FirestreamerRM]"
   bkp.BackupSetName = "Northwind_Full"
   bkp.BackupSetDescription = "Full backup of Northwind database."

   bkp.SQLBackup srv

See also

 

Terms of Use | Privacy Policy This page is protected by Secure Sockets Layer (SSL) © Cristalink Pty Ltd, 2023. ABN 97615040027