Menu

MS SQL Server

MS SQL Server

If your question is not addressed below, please contact us through our feedback form and we'll get back to you shortly.

How can I perform differential backup of my MS SQL Server?
How can I perform incremental backup of my MS SQL Server?
Under what name and extension are the MS SQL Server backup files stored on IDrive?
Can I backup databases from multiple instances of MS SQL Server?
I am unable to view the databases in the instance of MS SQL Server that I am logged into. Why?
Can I backup the MS SQL Server in the 'Mirror Path' mode?
Can I backup Microsoft SQL Server 'tempdb' database?
How do I restore my database backup file to my MS SQL Server?
Can IDrive backup the entire SQL Server database?
Can I restore my database file to a different MS SQL Server database?
Under what circumstance should I restore the MS SQL Server 'master' database?
How do I restore the MS SQL Server master database?
When should I restore the 'model', 'msdb' and 'distribution' databases?
I am receiving an error message stating “VDS::Create Fails : 0x80770005” during SQL Server Backup. Why?
I am receiving an error message stating 'Check registration of SQLVDI.DLL and value of IID' during SQL Server Backup. Why?
Can I restore database files to multiple SQL Server instances?
How do I set 'sysadmin' role to the "NT AUTHORITY\SYSTEM" or the "BUILTIN\Administrators group" in my SQL Server?
What are the recovery models that IDrive supports for MS SQL Server database backup and restore?
How to run the SQL Server instance from the administrator or local account?
How can I backup SQL server database backup from a NAS / mapped drive?
How can I restore SQL server database backup from a NAS / mapped drive?
How to change the database model from simple to full or bulk-logged in order to perform incremental backup?
How do I remove the SQL Server databases that are not required for restore?

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

To perform differential backup,

  1. Log in to the IDrive application and click the 'Server Backup' tab.
  2. Under MS SQL Server section, click 'Backup'.

  1. Provide the relevant authentication information when prompted. IDrive 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 specify SQL backup temporary path for local backup.
  3. Click 'Schedule Now'.

  1. The 'Schedule backup' screen appears, from where you can schedule the backup for any future day and time or perform an immediate backup of the selected databases.

  1. Click 'Save Changes'.
  1. Note: IDrive for Windows 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 'IDSQLServerBkset' followed by the instance name scheduled for backup. Example: If the instance name is 'MJOHN\INSTMJOHN' then the name of the backup job is 'IDSQLServerBkset (MJOHN##INSTMJOHN)'

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

To perform incremental backup,

  1. Log in to the IDrive application and click the 'Server Backup' tab.
  2. Under MS SQL Server section, click 'Backup'.

  1. Provide the relevant authentication information when prompted. IDrive 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'.

  1. 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.

  1. Click 'Save Changes'.

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

The backed up files are stored in your IDrive 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?

IDrive lets you restore your database backup file to a different database and perform point-in-time database recovery.

To restore,

  1. Log into IDrive desktop application and click the 'Restore' tab.
  2. Locate and select the database backup file (IDSQLBackup) in your IDrive account.
  3. Click the 'Restore Now' button, to restore the database backup file (IDSQLBackup) to the local computer.
  4. Once the IDSQLBackup 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 choose the database backup (IDSQLBackup) file (restored earlier from your IDrive 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 IDrive backup the entire SQL Server database?

Yes. IDrive 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 IDrive – 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?

For SQL Server 2008 and earlier, the 'sysadmin' role is automatically applied to the NT AUTHORITY\SYSTEM and BUILTIN\Administrators groups. However, 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.

You can set the 'sysadmin' role using the following techniques,

  1. SQL Server Management Studio:
    1. Launch 'Microsoft SQL Management Studio'.
    2. Navigate to Security > Logins > NT AUTHORITY\SYSTEM.
    3. Right-click NT AUTHORITY\SYSTEM and select Properties.


    4. In the 'Login Properties- NT AUTHORITY\SYSTEM' window, click Server Roles.
    5. 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;

What are the recovery models that IDrive 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 to run the SQL Server instance from the administrator or local account?

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.

How can I backup SQL server database backup from 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 IDrive application.
  2. Click the 'Backup' button.

  3. Log in to SQL server in the IDrive 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 IDrive application.
  2. Click the 'Restore' button.
  3. Log in to SQL server in the IDrive application using SQL credentials.
  4. Browse the ‘IDSQLBackup’ backup folder from the NAS / mapped drive to locate the SQL database backup file. Download the ‘IDSQLBackup’ folder from your IDrive 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.

Note: It is recommended to run SQL server instance service from the administrator / local account 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 IDrive application and backup the database using the incremental backup option.

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

To remove the databases,

  1. Log in to the IDrive application and click the 'Server Backup' tab.
  2. Click 'Restore', under MS SQL Server section.
  3. Provide the relevant MS SQL Server authentication information when prompted.
  4. Browse and choose the database backup (IDSQLBackup) file (restored earlier from your IDrive account).
    After loading IDSQLBackup file the 'SQL Server backup/restore wizard' appears.
  5. Right click on the database you wish to remove and click 'Delete'.
  6. Click 'Yes' in the confirmation window.
Top