Skip to main content
pdf?stylesheet=default
Blackboard Help

Restore the Database in a SQL Environment

If you are a Managed Hosting customer, this topic doesn't apply to you.
 

The database restore will fail if you do not stop the Blackboard services and any other applications connecting to SQL during the restore procedure. The restore of any data should be scheduled for a time when you can turn off access to the Blackboard website.


Stop the services

  1. Launch a command prompt from the Windows Start menu.
  2. Navigate to blackboard_home\tools\admin.
  3. Stop the iisadmin, bb-collab, and bb-tomcat services by typing the following command and pressing the ENTER key:

    servicecontroller.bat services.stop

More on Blackboard Services


Restore databases to existing hardware

  1. Select Start > All Programs > Microsoft SQL Server 2014 > SQL Server Management Studio.
  2. Connect to the database and select the expanded Databases folder to expand the list of databases.
  3. Highlight the BBLEARN database and right-click on the Database.
  4. Select Tasks > Restore > Database to open the SQL Restore Utility. The default settings of the restore database window shows the last known verified backup checked. Accept the default settings for this restore if it is the correct backup. Confirm the date/time stamp by selecting the timeline icon.
  5. On the General page, use the Source section to specify the source and location of the backup sets to restore. Select one of the following options:
    1. Database: Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history. Note: If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. In this case, select Device to manually specify the file or device to restore.
    2. Device: Select the browse (...) icon to open the Select backup devices dialog box.
      1. Select a media type from the Backup media type drop-down list.

        The Tape option appears only if a tape drive is mounted on the computer, and the Backup Device option appears, only if at least one backup device exists.

      2. Depending on the type of media you select, selecting Add opens one of the following dialog boxes. If the list in the Backup media list box is full, the Add button is unavailable.
        Media type Dialog box Description
        File Locate Backup File Select a local file from the tree or specify a remote file using its fully qualified universal naming convention (UNC) name. For more information, see Backup Devices (SQL Server).
        Device Select Backup Device Select from a list of the logical backup devices defined on the server instance.
        Tape Select Backup Tape Select from a list of the tape drives that are physically connected to the computer running the instance of SQL Server.
        UR Select a Backup File Location Select an existing SQL Server credential/Azure storage container, add a new Azure storage container with a shared access signature, or generate a shared access signature and SQL Server credential for an existing storage container. See also, Connect to a Microsoft Azure Subscription.
  6. Browse to the backup directory and select the correct data files. The file name will show in the window once highlighted.
  7. Select OK to accept the file name chosen.
  8. Select OK again to close the window and return to the Restore database window.
  9. Confirm that the backup data file is correct.
  10. In the Destination section, the Database box is automatically populated with the name of the database to be restored. To change the name of the database, type the new name in the Database box.
  11. In the Restore to box, leave the default as To the last backup taken or select Timeline to access the Backup Timeline dialog box to manually select a point in time to stop the recovery action.
  12. In the Backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected.
  13. Optionally, select Files in the Select a page pane to access the Files dialog box. From here, you can restore the database to a new location by specifying a new restore destination for each file in the Restore the database files as grid.
  14. Select Options and check Overwrite all existing database (with replace) under Restore options. To view or select the advanced options on the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:
    1. WITH options (not required):
      • Overwrite the existing database (WITH REPLACE)
      • Preserve the replication settings (WITH KEEP_REPLICATION)
      • Restrict access to the restored database (WITH RESTRICTED_USER)
    2. Select an option for the Recovery state box. This box determines the state of the database after the restore operation.
      • RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs can't be restored. Select this option if you are restoring all of the necessary backups now.
      • RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database can't be used until it is recovered.
      • RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.
    3. Take tail-log backup before restore. Not all restore scenarios require a tail-log backup. For more information, see Scenarios That Require a Tail-Log Backup from Tail-Log Backups (SQL Server).
    4. Restore operations may fail if there are active connections to the database. Check the Close existing connections option to ensure that all active connections between Management Studio and the database are closed. This check box sets the database to single user mode before performing the restore operations, and sets the database to multi-user mode when complete.
    5. Select Prompt before restoring each backup if you wish to be prompted between each restore operation. This is not usually necessary unless the database is large and you wish to monitor the status of the restore operation.
    6. For more information about these restore options, see Restore Database (Options Page).
  15. Select OK to start the restore of the database. Wait for the message that the restore has completed.

Restore databases to new hardware

  1. Restore the Blackboard Learn databases and content management databases using the steps to restore databases on existing hardware as detailed previously.
  2. Check the ownership of tables and stored procedures in the restored databases before proceeding to the restore script. For detailed instruction, see Setting Up Target System in a SQL Environment.
  3. Select Start > All Programs > Microsoft SQL Server 2014 > SQL Server Management Studio.
  4. Expand the Databases folder to expand down to the BBLEARN database.
  5. Highlight the BBLEARN database.
  6. Select New Query.
  7. Copy one of the following statements into the query window. There are separate queries for Blackboard Learn and content management. Make sure to run the right query.
  8. Select Run or F5 to execute script after loading it into the query window.

Restore script for Blackboard Learn only (non-legacy)

--If SQL Server 2012 then
--EXEC sp_dbcmptlevel 'bbadmin', '110';
--If SQL Server 2014 then
--EXEC sp_dbcmptlevel 'bbadmin', '120';

USE BBLEARN;
GO
sp_dropuser 'BBLEARN';
GO
sp_dropuser 'BBLEARN_report';
GO
sp_dropuser 'BBLEARN_stats';
GO

USE BBLEARN_admin;
GO
sp_dropuser 'BBLEARN_admin';
GO
sp_dropuser 'BBLEARN';
GO
sp_dropuser 'BBLEARN_stats';
GO

USE BBLEARN_cms;
GO
sp_dropuser 'BBLEARN_cms';
GO

USE BBLEARN_cms_doc;
GO
sp_dropuser 'BBLEARN_cms';
GO

USE BBLEARN_stats;
GO
sp_dropuser 'BBLEARN_stats';
GO
sp_dropuser 'BBLEARN';
GO

USE BBLEARN;
GO
sp_grantdbaccess 'BBLEARN';
GO
sp_grantdbaccess 'BBLEARN_report';
GO
sp_grantdbaccess 'BBLEARN_stats';
GO
sp_addrolemember 'db_owner', 'BBLEARN';
GO
sp_addrolemember 'db_datareader', 'BBLEARN_stats';
GO
ALTER USER BBLEARN WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER BBLEARN_report WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER BBLEARN_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE BBLEARN_admin;
GO
sp_grantdbaccess 'BBLEARN'
GO
sp_grantdbaccess 'BBLEARN_admin'
GO
sp_grantdbaccess 'BBLEARN_stats'
GO
sp_addrolemember 'db_datareader', 'BBLEARN';
GO
sp_addrolemember 'db_owner', 'BBLEARN_admin';
GO
sp_addrolemember 'db_datareader', 'BBLEARN_stats';
GO
ALTER USER BBLEARN_admin WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER BBLEARN WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER BBLEARN_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE BBLEARN_cms;
GO
sp_grantdbaccess 'BBLEARN_cms'
GO
sp_addrolemember 'db_datareader', 'BBLEARN_cms';
GO
sp_addrolemember 'db_datawriter', 'BBLEARN_cms';
GO
sp_addrolemember 'db_ddladmin', 'BBLEARN_cms';
GO
ALTER USER BBLEARN_cms WITH DEFAULT_SCHEMA = dbo;
GO

USE BBLEARN_cms_doc;
GO
sp_grantdbaccess 'BBLEARN_cms'
GO
sp_addrolemember 'db_datareader', 'BBLEARN_cms';
GO
sp_addrolemember 'db_datawriter', 'BBLEARN_cms';
GO
sp_addrolemember 'db_ddladmin', 'BBLEARN_cms';
GO
ALTER USER BBLEARN_cms WITH DEFAULT_SCHEMA = dbo;
GO

USE BBLEARN_stats;
GO
sp_grantdbaccess 'BBLEARN'
GO
sp_grantdbaccess 'BBLEARN_stats';
GO
sp_addrolemember 'db_owner', 'BBLEARN';
GO
sp_addrolemember 'db_owner', 'BBLEARN_stats';
GO
ALTER USER BBLEARN WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER BBLEARN_stats WITH DEFAULT_SCHEMA = dbo;
GO

--SQL Server database compatible level to SQL Server 2014
--If SQL Server 2012 use 110 instead of 120
EXEC sp_dbcmptlevel 'BBLEARN', '120';
GO
EXEC sp_dbcmptlevel 'BBLEARN_admin', '120';
GO
EXEC sp_dbcmptlevel 'BBLEARN_cms', '120';
GO
EXEC sp_dbcmptlevel 'BBLEARN_cms_doc', '120';
GO
EXEC sp_dbcmptlevel 'BBLEARN_stats', '120';
GO


Combined restore script for both Blackboard Learn and content management (containing legacy databases)

--If SQL Server 2012 then
--EXEC sp_dbcmptlevel 'bbadmin', '110';
--If SQL Server 2014 then
--EXEC sp_dbcmptlevel 'bbadmin', '120';

USE bbadmin;
GO
sp_dropuser 'bbadmin';
GO
sp_dropuser 'bb_bb60';
GO
sp_dropuser 'bb_bb60_stats';
GO

USE bb_bb60;
GO
sp_dropuser 'bb_bb60';
GO
sp_dropuser 'bb_bb60_report';
GO
sp_dropuser 'bb_bb60_stats';
GO

USE bb_bb60_stats;
GO
sp_dropuser 'bb_bb60_stats';
GO
sp_dropuser 'bb_bb60';
GO

USE cms;
GO
sp_dropuser 'cms_user';
GO

USE cms_doc;
GO
sp_dropuser 'cms_user';
GO

USE cms_files_courses;
GO
sp_dropuser 'cms_user';
GO

USE cms_files_inst;
GO
sp_dropuser 'cms_user';
GO

USE cms_files_library;
GO
sp_dropuser 'cms_user';
GO

USE cms_files_orgs;
GO
sp_dropuser 'cms_user';
GO

USE cms_files_users;
GO
sp_dropuser 'cms_user';
GO

USE bbadmin;
GO
sp_grantdbaccess 'bb_bb60'
GO
sp_grantdbaccess 'bbadmin'
GO
sp_grantdbaccess 'bb_bb60_stats'
GO
sp_addrolemember 'db_datareader', 'bb_bb60';
GO
sp_addrolemember 'db_owner', 'bbadmin';
GO
sp_addrolemember 'db_datareader', 'bb_bb60_stats';
GO
ALTER USER bbadmin WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE bb_bb60;
GO
sp_grantdbaccess 'bb_bb60';
GO
sp_grantdbaccess 'bb_bb60_report';
GO
sp_grantdbaccess 'bb_bb60_stats';
GO
sp_addrolemember 'db_owner', 'bb_bb60';
GO
sp_addrolemember 'db_datareader', 'bb_bb60_stats';
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_report WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE bb_bb60_stats;
GO
sp_grantdbaccess 'bb_bb60'
GO
sp_grantdbaccess 'bb_bb60_stats';
GO
sp_addrolemember 'db_owner', 'bb_bb60';
GO
sp_addrolemember 'db_owner', 'bb_bb60_stats';
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE cms;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO

USE cms_doc;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO

USE cms_files_courses;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO
GRANT EXEC ON XYF_PERMISSIONS TO cms_user;
GO
GRANT EXEC ON XYF_BLOB_WRITE TO cms_user;
GO

USE cms_files_inst;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO
GRANT EXEC ON XYF_PERMISSIONS TO cms_user;
GO
GRANT EXEC ON XYF_BLOB_WRITE TO cms_user;
GO

USE cms_files_library;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO
GRANT EXEC ON XYF_PERMISSIONS TO cms_user;
GO
GRANT EXEC ON XYF_BLOB_WRITE TO cms_user;
GO

USE cms_files_orgs;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO
GRANT EXEC ON XYF_PERMISSIONS TO cms_user;
GO
GRANT EXEC ON XYF_BLOB_WRITE TO cms_user;
GO

USE cms_files_users;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO
GRANT EXEC ON XYF_PERMISSIONS TO cms_user;
GO
GRANT EXEC ON XYF_BLOB_WRITE TO cms_user;
GO

--SQL Server database compatible level to SQL Server 2014
--If SQL Server 2012 use 110 instead of 120
EXEC sp_dbcmptlevel 'bbadmin', '120';
GO
EXEC sp_dbcmptlevel 'bb_bb60', '120';
GO
EXEC sp_dbcmptlevel 'bb_bb60_stats', '120';
GO
EXEC sp_dbcmptlevel 'cms', '120';
GO
EXEC sp_dbcmptlevel 'cms_doc', '120';
GO
EXEC sp_dbcmptlevel 'cms_files_courses', '120';
GO
EXEC sp_dbcmptlevel 'cms_files_inst', '120';
GO
EXEC sp_dbcmptlevel 'cms_files_library', '120';
GO
EXEC sp_dbcmptlevel 'cms_files_orgs', '120';
GO
EXEC sp_dbcmptlevel 'cms_files_users', '120';
GO


Restore script for Blackboard Learn only (legacy)

--If SQL Server 2012 then
--EXEC sp_dbcmptlevel 'bbadmin', '110';
--If SQL Server 2014 then
--EXEC sp_dbcmptlevel 'bbadmin', '120';

USE bbadmin;
GO
sp_dropuser 'bbadmin';
GO
sp_dropuser 'bb_bb60';
GO
sp_dropuser 'bb_bb60_stats';
GO

USE bb_bb60;
GO
sp_dropuser 'bb_bb60';
GO
sp_dropuser 'bb_bb60_report';
GO
sp_dropuser 'bb_bb60_stats';
GO

USE bb_bb60_stats;
GO
sp_dropuser 'bb_bb60_stats';
GO
sp_dropuser 'bb_bb60';
GO

USE cms;
GO
sp_dropuser 'cms_user';
GO

USE cms_doc;
GO
sp_dropuser 'cms_user';
GO

USE bbadmin;
GO
sp_grantdbaccess 'bb_bb60'
GO
sp_grantdbaccess 'bbadmin'
GO
sp_grantdbaccess 'bb_bb60_stats'
GO
sp_addrolemember 'db_datareader', 'bb_bb60';
GO
sp_addrolemember 'db_owner', 'bbadmin';
GO
sp_addrolemember 'db_datareader', 'bb_bb60_stats';
GO
ALTER USER bbadmin WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE bb_bb60;
GO
sp_grantdbaccess 'bb_bb60';
GO
sp_grantdbaccess 'bb_bb60_report';
GO
sp_grantdbaccess 'bb_bb60_stats';
GO
sp_addrolemember 'db_owner', 'bb_bb60';
GO
sp_addrolemember 'db_datareader', 'bb_bb60_stats';
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_report WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE bb_bb60_stats;
GO
sp_grantdbaccess 'bb_bb60'
GO
sp_grantdbaccess 'bb_bb60_stats';
GO
sp_addrolemember 'db_owner', 'bb_bb60';
GO
sp_addrolemember 'db_owner', 'bb_bb60_stats';
GO
ALTER USER bb_bb60 WITH DEFAULT_SCHEMA = dbo;
GO
ALTER USER bb_bb60_stats WITH DEFAULT_SCHEMA = dbo;
GO

USE cms;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO

USE cms_doc;
GO
sp_grantdbaccess 'cms_user'
GO
sp_addrolemember 'db_datareader', 'cms_user';
GO
sp_addrolemember 'db_datawriter', 'cms_user';
GO
sp_addrolemember 'db_ddladmin', 'cms_user';
GO
ALTER USER cms_user WITH DEFAULT_SCHEMA = dbo;
GO

--SQL Server database compatible level to SQL Server 2014
--If SQL Server 2012 use 110 instead of 120
EXEC sp_dbcmptlevel 'bbadmin', '120';
GO
EXEC sp_dbcmptlevel 'bb_bb60', '120';
GO
EXEC sp_dbcmptlevel 'bb_bb60_stats', '120';
GO
EXEC sp_dbcmptlevel 'cms', '120';
GO
EXEC sp_dbcmptlevel 'cms_doc', '120';
GO


Troubleshoot

If you encounter the following error type Unable to drop user BBLEARN from database because user owns an object in database during the execution of one of the above restore scripts, check and change ownership of objects. This error may be caused by either a table or stored procedure being owned by BBLEARN. Clients who have updated from earlier builds may experience this problem. To correct this problem, the administrator must find the stored procedure or table and change the ownership to dbo before the restore will run without errors. Ignoring the error will cause an unsuccessful database restore.