I'm trying to restore a database backed up from MainBoss, but I'm getting errors. What should I do?

To begin with, it helps to log in to Windows under a login name with Windows Administration privileges. This lets the programs you run perform operations that might otherwise be impossible. It's also very helpful if the login name has SQL Server Administration privileges. (In some versions of Windows, people with Windows Administration privileges automatically have SQL Server Administration privileges, but in other versions of Windows, this isn't the case.)

Also in the window that tells you that errors occurred, click Details to gather more information about the problem. The rest of this web page discusses some Details messages you might see.

Unable to gain exclusive access to the database

This error is displayed because there is still a "user" logged into the database. This may be a normal MainBoss user, but it might also be one of the following:

To correct this, you can use SQL Server Management Studio or Management Studio Express.

  1. Login to an account that has SQL Server Administration privileges on the computer where SQL Server is running.
  2. Start SQL Server Management Studio or Management Studio express.
  3. As the program starts up, specify the instance of SQL Server that manages the MainBoss database.
  4. In the left-hand panel, expand the entry for Databases.
  5. In the resulting list of databases, right-click on the entry for the MainBoss database, and then click Properties.
  6. In the resulting window, click on Options (under "Select a page").
  7. In the "State" section of "Other Options", click the drop-down arrow associated with "Restrict Access" and change the value to SINGLE_USER. This will give your current login name exclusive access to the MainBoss database.
  8. Click OK.
  9. Start MainBoss and do the desired restore.
  10. Once the restore process is finished, follow the above steps again and set "Restrict Access" back to MULTI_USER.

The media family on device 'C:...file.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE HEADERONLY is terminating abnormally (Microsoft SQL Server, Error: 3241)

This error occurs if the backup was made by one version of SQL Server, but you are trying to open it in a different (older) version. In general, older versions of SQL Server cannot restore backup files made by newer versions.

User does not have permission to restore the database

This error is displayed because the user account was created on the SQL server by a system administrator or by a user who isn't the user attempting the restore. The error is because they are not the database owner (dbo) and they do not have the dbcreator role. Depending on what the IT department will accept, you can correct this in one of the three following ways:

  1. Add the user to SQL Server's dbcreator role. You can do this by executing the following query in SQL Server Management Studio:
    EXEC sp_addsrvrolemember 'DOMAIN\LOGINNAME','dbcreator'
    
    where DOMAIN\LOGINNAME is the domain and login name of the person who'll be doing the restore. Once you have entered this query, press F5. This will add the user to the dbcreator role for all databases managed by this instance of SQL Server. This permission allows the user to create any database and will also enable the user to drop or alter any database across the entire server. This is therefore an extremely powerful permission that affects the entire SQL Server.
  2. Add the user as the database owner (dbo) for the MainBoss database. You can do this by logging into to an account with SQL Server Administration privileges and executing the following query in SQL Server Management Studio:
    USE MainBossDatabase
    EXEC sp_changedbowner 'DOMAIN\LOGINNAME'
    
    where MainBossDatabase is the name of the MainBoss database and DOMAIN\LOGINNAME is the domain and login name of the person who'll be the new owner. Once you've typed in the above, press F5. This will add the user to the database owner role for the MainBoss database. The new owner will be able to create, delete, and alter the database.
  3. Grant the user CREATE status. You can do this by logging into to an account with SQL Server Administration privileges and executing the following in SQL Server Management Studio:
    USE master
    GRANT CREATE ANY DATABASE to 'DOMAIN\LOGINNAME'
    
    where DOMAIN\LOGINNAME is the domain and login name of the person to whom you want to grant the privilege. Once you've typed in the above, press F5. This gives the CREATE privilege to the specified user. The user will then be able to create databases, but will only be able to alter or delete the ones they create.

Back to FAQ index