Frequently Asked Questions

Frequently Asked Questions

SQL Server Backup

How can I backup my Microsoft SQL Server?
What is interactive backup?
From where can I view the status of my backups?
How can I edit the MS SQL Server scheduled backup job using IDrive Vault?
Where will the backed up files be stored on the IDrive Vault device?
On IDrive Vault, under what name and extension are the MS SQL Server backup files stored?
Backup of databases from multiple instances of MS SQL Server is possible?
Why am I not able to view the databases in the instance of MS SQL Server that I am logged into?
Can I backup Microsoft SQL Server 'tempdb' database?
Can I restore my database backup file to my MS SQL Server?
Is it possible to back up the entire SQL Server database to IDrive Vault?
I want to restore my database file to a different MS SQL Server database? Can I?
Under what circumstance should I restore the MS SQL Server 'master' database?
How can I restore the MS SQL Server master database?
When should I restore the 'model', 'msdb' and 'distribution' databases?
Why am I receiving an error message stating “VDS::Create Fails : 0x80770005” during SQL Server Backup?
Why am I receiving an error message stating 'Check registration of SQLVDI.DLL and value of IID' during SQL Server Backup?
Can I restore database files to multiple SQL Server instances?
How can I set 'sysadmin' role to the "NT AUTHORITY\SYSTEM" or the "BUILTIN\Administrators group" in my SQL Server?
   
   

How can I backup my Microsoft SQL Server?

To backup your MS SQL Server,

  1. Log into IDrive Vault desktop application.
  2. Click IDrive Vault on the Server Backup section.


  3. On the expanded section, mouse over over MS SQL Server and click 'Schedule Now'.
    The SQL Backup Status window appears.
  4. Click 'Add Backup Set'. The SQL login window appears.


  5. IDrive Vault provides two modes of MS SQL Server authentication. You can connect to your database using:

    • Windows Authentication Mode: When you connect through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that your identity is confirmed by Windows. SQL Server does not ask for the password and does not perform the identity validation. Windows Authentication is the default authentication mode, and is more secure than SQL Server Authentication.

    • SQL Server Authentication Mode: When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts. Both the username and the password are created by using SQL Server and stored in SQL Server. While connecting using SQL Server Authentication, you must provide your credentials (login and password) every time. When using SQL Server Authentication, you should set strong passwords for all SQL Server accounts.

      Note: If you attempt to connect to an instance of MS SQL Server by providing a blank login name, the SQL Server uses the Windows Authentication. Additionally, if you connect to an instance of SQL Server configured for Windows Authentication Mode using a specific login, the login is ignored and the Windows Authentication is used.

  6. Click ‘Connect’. The Scheduler window appears.
  7. Select the databases to be backed up and select the time and days for a scheduled backup.
    For an immediate backup, check the box against 'Start the backup immediately'.
  8. Click 'Update'.


The next time you log in, the created backup set will appear in the SQL Backup Status window. If the backup set name is 'SQL_BackupSet_yyyymmddhhmmss, right-click on the backup set name. The context menu will open. Click ‘Backup’ to start an interactive backup. Click ‘Restore’ to restore the particular backup set. Click ‘Delete’ to delete the backup set.

Note: Selected databases from the SQL Server would get copied in the local IDrive Vault cache (C:\IDriveVaultCache). From there, the databases would be backed up to the Vault device using rsync.

 

What is interactive backup?

Interactive backup option allows the user to start an immediate backup of the selected databases.

To start interactive backup,

  1. Log into IDrive Vault desktop application.?
  2. Click IDrive Vault on the Server Backup section.
  3. On the expanded section, click ‘MS SQL Server’.
    The SQL Backup Status window appears.?
  4. From ‘SQL Backup Status’ window, right-click on the particular backup set, and click ‘Backup’

User can view the status of the interactive backup in the SQL Backup Status window.



 

From where can I view the status of my backups?

You can view the status of your SQL server backups in the SQL Backup Status window.

IDrive Vault against the backup set name indicates that the backup has been completed successfully

IDrive Vault against the backup set name indicates that the backup has failed

IDrive Vault against the backup set name indicates that the backup has not started yet

 

How can I edit the MS SQL Server scheduled backup job using IDrive Vault?

You can easily edit and update the scheduled backup job using IDrive Vault.

  1. Log into IDrive Vault desktop application.
  2. Click IDrive Vault on the Server Backup section.
  3. On the expanded section, click 'MS SQL Server'.
  4. The SQL Backup Status window appears. The created backup set will appear in the SQL Backup Status window.


  5. Double click on the backup set name. The Scheduler window will appear.
  6. Modify the databases, time and days for the scheduled backup

 

Where will the backed up files be stored on the IDrive Vault device?

All the backed up databases will be saved under the path: user account, machine name and advanced backup folder.
Example: /Username/ <machine name>/Advanced Backup/<Backupsetname>.

 

On IDrive Vault, under what name and extension are the MS SQL Server backup files stored?

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

 

Backup of databases from multiple instances of MS SQL Server is possible?

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.

 

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

You may not be able 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 Microsoft SQL Server 'tempdb' database?

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

 

Can I restore my database backup file to my MS SQL Server?

Yes. IDrive Vault lets you restore your database backup file (*.dmp) to a different database and perform point-in-time database recovery.

You can restore data from your IDrive Vault account to MS SQL Server using the IDrive Vault - SQl Server Restore option, in three ways.

  1. From progress status window, 'Server Backup' -> 'MS SQL Server' -> 'Restore', during first time login.
  2. From 'SQL Backup Status' window, click .
  3. From 'SQL Backup Status' window, right-click on the particular backup set, and click 'Restore'.

To restore from progress status window,

  1. Log into IDrive Vault desktop application.
  2. Click IDrive Vault on the Server Backup section.
  3. On the expanded section, mouse over over MS SQL Server and click 'Restore'.
    The Restore window will appear.
  4. In the Advanced Backup folder, all the backup sets restored under the account, would appear.
  5. Select the backup set folder you wish to restore and click 'Proceed'.

All databases from the cloud would get stored in the local IDrive Vault cache. From there, all databases would deploy into the SQL server.
Note: User can also select a single database for restore.

 

Is it possible to back up the entire SQL Server database to IDrive Vault?

Yes. You 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 IDrive Vault account.

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

 

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

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 can 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'.
  3. Click the 'Startup Parameters' button under the 'General' tab.
  4. Select and add the required parameters as shown below.
  5. Restart the Microsoft SQL Server. From the SQL Server Enterprise Manager, right-click 'Microsoft SQL Servers', select 'Stop' and then 'Start'.
  6. Use the IDrive Vault – 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/.

 

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

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.

 

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

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 can 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,

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

B. 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;