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 do I backup my Microsoft 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?
How to perform SQL backup/restore to the mapped drive?

How do I backup my Microsoft SQL Server?

To backup your MS SQL Server,

  1. Log into IDrive desktop 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. 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.



  1. Select the database(s) from the list which appears and specify the local backup location.
  2. Select Online Backup or Local Backup radio button, and click Schedule Now.


  1. 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: IDrive 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)'

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;

How to perform SQL backup/restore to the mapped drive?

To perform backup and restore of SQL server, run the SQL server instance and IDriveService in the administrator mode.


To run IDrive service,

  1. Go to the services wizard, right click ‘IDriveService’ 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'.

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

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


Top