Menu

Frequently Asked Questions

MS SQL Server Backup

How can I perform differential backup of my MS SQL Server?

To perform differential backup of your MS SQL Server,

  1. Log in to the IBackup application and click the ‘Server Backup’ tab.
  2. Under the ‘MS SQL Server’ section, click 'Backup'.
  1. Provide the relevant authentication information when prompted. IBackup provides two modes of MS SQL Server authentication.
  1. Select the 'Local Backup + Cloud Backup' or the 'Local Backup Only' radio button.
  2. Select the database(s) from the list displayed, specify the temporary local backup path.
  3. Click 'Schedule Now'.
  4. The ‘Schedule backup’ screen is displayed where you can schedule the backup for any future day and time or perform an immediate backup of the selected databases.

  1. Note: IBackup allows you to schedule the SQL Server backup for different instances. For easy identification of the SQL Server Backup set, the name of the backup set will be 'IBSQLServerBkset' followed by the instance name scheduled for backup.

    Example: If the instance name is 'MJOHN\INSTMJOHN' then the name of the backup job is 'IBSQLServerBkset (MJOHN##INSTMJOHN)'

How can I perform incremental backup of my MS SQL Server?

To perform incremental backup of your MS SQL Server,

  1. Log in to the IBackup application and click the ‘Server Backup’ tab.
  2. Under the ‘MS SQL Server’ section, click 'Backup'.
  1. Provide the relevant authentication information when prompted. IBackup provides two modes of MS SQL Server authentication.
  1. Select the 'Local Backup + Cloud Backup' or the 'Local Backup Only' radio button.
  2. Select the database(s) from the list which appears and select the 'Incremental’ option.

    Note: To perform the incremental backup, database should be in Full / Bulk-logged recovery model.

  3. Specify the local backup location and click 'Schedule Now'.
  4. In the 'Schedule backup' screen that appears, schedule the backup for any future day and time or perform an immediate backup of the selected databases.
  5. Click ‘Save Changes’.

Under what name and extension are the MS SQL Server backup files stored on IBackup?

The backed up files are stored in your IBackup account with the extension '.dmp', file name enclosed within square brackets.

Format of the file name: [ ].dmp

Examples: [model].dmp, [pubs].dmp, [order list].dmp.

Can I backup databases from multiple instances of MS SQL Server?

Yes. You can backup databases from multiple instances of the MS SQL Server running on your local computer. However, you may be unable to backup databases from multiple instances of MS SQL Server hosted at multiple locations on your network.

I am unable to view the databases in the instance of MS SQL Server that I am logged into. Why?

You may be unable to view all the databases under the instance, due to lack of proper access permissions/privileges for the username that you are logged in with. We recommend you to contact your database administrator to obtain the privileges to access the databases.

Can I backup the MS SQL Server in the 'Mirror Path' mode?

No, even if you opt to backup using the 'Mirror Path' option, they will take place in the 'Relative Path'.

Can I backup Microsoft SQL Server 'tempdb' database?

No. You cannot backup your Microsoft SQL Server 'tempdb' database.

How do I restore my database backup file to my MS SQL Server?

IBackup lets you restore your database backup file (IBSQLBackup) to a different database and perform point-in-time database recovery.

To restore,

  1. Log into IBackup desktop application and click the 'Restore' tab.
  2. Locate and select the database backup file (IBSQLBackup) in your IBackup account.
  3. Click the 'Restore Now' button, to restore the database backup file (IBSQLBackup) to the local computer.
  4. Once the IBSQLBackup file has been successfully restored, click the 'Server Backup' tab.
  5. Click 'Restore', under MS SQL Server section.
  1. Provide the relevant MS SQL Server authentication information when prompted.
  2. Browse and select the database backup file (IBSQLBackup) from your local computer (the location where the database backup file (IBSQLBackup) was restored earlier from your IBackup account).
  1. The 'SQL Server backup/restore' screen is displayed.
  1. Click the 'Restore' button to restore selected database backup file (*.dmp) to your MS SQL Server.

Can IBackup backup the entire SQL Server database?

Yes. IBackup can backup the entire SQL Server database along with the corresponding database log file, containing the entire structure and components of the database, to your online account.

You can also backup multiple databases wherein each database has its own corresponding '[ <database name>].dmp' file.

Can I restore my database file to a different MS SQL Server database?

Yes. You can restore your database backup file (.dmp) to a different database by providing a new database name. Further, it is possible to change the data and transaction log file location as desired.

Under what circumstance should I restore the MS SQL Server 'master' database?

You need to restore the master database if you are:

  • Rebuilding all your databases from scratch.
  • Changing any server-wide or database configuration options.
  • Adding logins or other login security-related operations.
  • Creating or removing logical backup devices.
  • Configuring the server for distributed queries and remote procedure calls such as adding linked servers or remote logins.

Note: If you just want to restore a user database, there is no need to restore your master database. For details on Microsoft SQL Server master database, visit http://www.microsoft.com/sql/.

How do I restore the MS SQL Server master database?

To restore the MS SQL Server master database:

  1. Start Microsoft SQL Server in the 'Single User Mode'.
  2. Right-click the Microsoft SQL Server and select 'Properties'.
  1. Click the 'Startup Parameters' button under the 'General' tab.
  1. Select and add the required parameters as shown below.
  1. Restart the Microsoft SQL Server. From the SQL Server Enterprise Manager, right-click 'Microsoft SQL Servers', select 'Stop' and then 'Start'.
  2. Use the IBackup – MS SQL Server 'Restore' option to restore the master database.

When should I restore the 'model', 'msdb' and 'distribution' databases?

You should restore the 'model' database, if you have changed the database template of your MS SQL Server.

Restore the 'msdb' database, if you have changed the scheduling information or want to restore the backup and restore history of your databases.

If you are running the replication components of MS SQL Server, then restore your 'distribution' database.

Note: You need not restore these databases, if you just want to restore a user database. For more information on Microsoft SQL Server model, msdb and distribution database, visit http://www.microsoft.com/sql/.

I am receiving an error message stating “VDS::Create Fails : 0x80770005” during SQL Server Backup. Why?

The error message implies that your SQL Server service on your computer is running under a start up account with the format .\UserName.

To resolve this issue, contact your system administrator to configure the startup account of the SQL Server service to use the LocalSystem account. Alternately, use a start up account that has the full name of the domain account instead of a period (.) to start the service. For example, use the startup account DomainName\UserName to start the service.

I am receiving an error message stating 'Check registration of SQLVDI.DLL and value of IID' during SQL Server Backup. Why?

This message appears when the application tries to use a sqlvdi.dll file that has not been registered.

To resolve this problem, register the sqlvdi.dll file again using the following steps:

  1. Stop the SQL Server.
  2. Click Start, click Run, type Regsvr32 \SQLVDI.DLL in the Open box and click 'OK'. The default path of the Sqlvdi.dll file is 'C:\Program Files\Microsoft SQL Server\80\COM.'
  3. Restart the SQL Server.

Can I restore database files to multiple SQL Server instances?

Yes. You can restore multiple database files to the MS SQL Server instances one by one.

How do I set 'sysadmin' role to the "NT AUTHORITY\SYSTEM" or the "BUILTIN\Administrators group" in my SQL Server?

The 'sysadmin' role is automatically applied to the NT AUTHORITY\SYSTEM and BUILTIN\Administrators groups for SQL Server 2008 and earlier versions. But, for SQL Server 2012 and higher versions, you must first apply the 'sysadmin' role manually to the NT AUTHORITY\SYSTEM or the BUILTIN\Administrators group.

Set the 'sysadmin' role using the following techniques,

  1. SQL Server Management Studio:
    1. Launch 'Microsoft SQL Management Studio'.
    2. Go to Security > Logins > NT AUTHORITY\SYSTEM.
    3. Right-click NT AUTHORITY\SYSTEM and select Properties.
    1. In the 'Login Properties- NT AUTHORITY\SYSTEM' window, click Server Roles.
    2. Select the sysadmin check box, and click OK.
  2. SQL Server Command Line Utility:
    1. Open the Command Prompt with an administrator privilege.
    2. Use the following command to connect to a named instance by using Windows Authentication and provide a sysadmin role.

      sqlcmd -S <ComputerName>\<InstanceName> or sqlcmd -S <ComputerName> (For default instance)

      exec sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';

      go;

How to run the SQL Server instance from the administrator or local account?

Run the SQL server instance in the administrator mode in order to perform backup and restore of an SQL server.

To run SQL server instance,

  1. Go to the services wizard, right click 'SQL Server Instance' and select 'Properties'.
  2. Go to the 'Log On' tab and select 'This account'.
  3. Browse the administrator account, enter your account credentials and click 'Apply'.
  4. Restart the service.

You will be prompted with a message to restart the service. You can perform backup and restore operations, once you restart the service.

What are the recovery models that IBackup supports for MS SQL Server database backup and restore?

A recovery model is a database property that handles how all SQL transactions are logged. Furthermore, it decides whether a transaction log requires (and allows) backing up and the different types of restore operations that are available.

SQL server database backup, restore, and recovery operations are based on these recovery models:

  1. Simple Recovery Model
    • Supports full and differential backups
    • The simplest among all recovery models
    • Doesn't support transaction log backups
  2. Full Recovery Model
    • All transactions are fully recorded in the transaction log file
    • Supports full backup, differential backup and incremental (log) backups
    • Incremental (log) backups are the transactions that have changed since the last backup
  3. Bulk-Logged Recovery Model
    • A unique purpose database configuration option that works similar to the full recovery model, except that certain bulk operations can be minimally logged
    • Supports full backup, differential backup and incremental (log) backups
    • Incremental (log) backups are the transactions that have changed since the last backup

How can I backup SQL server database to a NAS / mapped drive?

In order to perform SQL server database backup to a network mapped drive / NAS drive, you have to map your network location to your system.

To backup,

  1. Click 'Server Backup' and then click 'MS SQL Server' on the IBackup application.
  2. Click the 'Backup' button.
  3. Log in to SQL server in the IBackup application using SQL credentials.
  4. Select 'Local Backup + Cloud Backup' or 'Local Backup Only'.
  5. Select the database that you want to backup to your account.
  6. Select backup type to 'Differential' or 'Incremental'.
  7. Select the network mapped drive / NAS drive as the location for creating the SQL database backup. Click 'OK' and then click 'Schedule Now'.
  8. Enter your username and password to authenticate network mapped drive / NAS drive.
  9. Schedule SQL database backup to perform the backup operation to a network mapped drive / NAS drive.
  10. Click ‘Save Changes’.

How can I restore SQL server database backup from a NAS / mapped drive?

To perform the SQL server database restore from a NAS / mapped drive, you have to map your network location to your system.

To restore,

  1. Click 'Server Backup' and then click 'MS SQL Server' on the IBackup application.
  2. Click the 'Restore' button.
  3. Log in to SQL server in the IBackup application using SQL credentials.
  4. Browse the ‘IBSQLBackup’ backup folder from the NAS / mapped drive to locate the SQL database backup file. Download the ‘IBSQLBackup’ folder from your IBackup account to a NAS / mapped drive. Click ‘OK’ to proceed.
  5. Network authentication window will appear. Enter your username and password for network drive authentication.
  6. Authenticate to proceed.
  7. Click 'Restore' to restore the database.
  8. Click ‘Save Changes’.

Note: It is recommended to run SQL server instance service from the administrator / local account in order to restore the database backup from NAS or mapped drive.

How to change the database model from simple to full or bulk-logged in order to perform incremental backup?

To change from simple to full or bulk-logged database model,

  1. Log in to Microsoft SQL management studio.
  2. Click the 'Databases' folder. A list of databases will appear.
  3. Right click on the required database and click 'Properties'. The database properties page appears.
  4. In the menu that appears on the left-hand side, click 'Options'.
  5. Change the recovery model from 'Simple' to 'Full' or 'Bulk-logged'.
  6. Click 'OK'.

Once this is completed, you can go to the IBackup application and backup the database using the incremental backup option.

How do I remove the databases that are not required for restore?

To remove the databases,

  1. log in to the IBackup desktop application and click the 'Restore' under the MS SQL Server section.
  2. Provide the relevant MS SQL Server authentication information when prompted.
  3. Browse and select the database backup file (IBSQLBackup) from your local computer (the location where the database backup file (IBSQLBackup) was restored earlier from your IBackup account).
    The 'SQL Server backup/restore' wizard appears.
  4. Right click on the database you wish to remove and click 'Delete'.
  5. Click 'Yes' in the confirmation window.