Skip to main content
pdf?stylesheet=default
Blackboard Help

Restoring the Database in a SQL Environment

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

Restoring Databases to Existing Hardware

  1. Click Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
  2. Connect to the database and click on the expanded Databases folder to expand the list of databases.
  3. Highlight the BBLEARN database and right click on the Database.
  4. Click Properties > Tasks > Back Up to open the SQL Backup 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 or name of your backup before proceeding.
  5. Click Database to select your backups from the alternate databases if the default database listed is incorrect. This will navigate to the alternate backup directory on your server.
  6. Navigate to Destination. This will allow you to choose the location of your backup file.
    1. Click Add to open the Select Backup Destination window shown.
    2. Click Browse to open the Locate Database Files window shown.
    3. Browse to the backup directory and select the correct data files. The file name will show in the window once highlighted.
    4. Click OK to accept the file name chosen.
    5. Click OK again to close the window and return to the Restore database window.
  7. Confirm that the backup data file is correct. Click on the Options page to proceed to the next step.
  8. Check Overwrite all existing backup sets under Overwrite media.
  9. Click 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. Click Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
  4. Expand the Databases folder to expand down to the BBLEARN database.
  5. Highlight the BBLEARN database.
  6. Click 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. Click Run or F5 to execute script after loading it into the query window.

Restore Script for Blackboard Learn Only (Non-legacy)

--If SQL Server 2000/2005/2008/ set database compatible level to SQL Server 2012
--If SQL Server 2008  then
--EXEC sp_dbcmptlevel 'bbadmin', '100';
--If SQL Server 2000/2005 then
--EXEC sp_dbcmptlevel 'bbadmin', '110';

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 2000 database compatible level to SQL Server 2008
EXEC sp_dbcmptlevel 'BBLEARN', '100';
GO
EXEC sp_dbcmptlevel 'BBLEARN_admin', '100';
GO
EXEC sp_dbcmptlevel 'BBLEARN_cms', '100';
GO
EXEC sp_dbcmptlevel 'BBLEARN_cms_doc', '100';
GO
EXEC sp_dbcmptlevel 'BBLEARN_stats', '100';
GO

Combined Restore Script for Both Blackboard Learn and Content Management (Containing Legacy Databases)

--If SQL Server 2000/2005/2008/ database compatible level to SQL Server 2012
--If SQL Server 2008 
--EXEC sp_dbcmptlevel 'bbadmin', '100';
--If SQL Server 2000/2005 
--EXEC sp_dbcmptlevel 'bbadmin', '110';

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 2000 database compatible level to SQL Server 2008
EXEC sp_dbcmptlevel 'bbadmin', '100';
GO
EXEC sp_dbcmptlevel 'bb_bb60', '100';
GO
EXEC sp_dbcmptlevel 'bb_bb60_stats', '100';
GO
EXEC sp_dbcmptlevel 'cms', '100';
GO
EXEC sp_dbcmptlevel 'cms_doc', '100';
GO
EXEC sp_dbcmptlevel 'cms_files_courses', '100';
GO
EXEC sp_dbcmptlevel 'cms_files_inst', '100';
GO
EXEC sp_dbcmptlevel 'cms_files_library', '100';
GO
EXEC sp_dbcmptlevel 'cms_files_orgs', '100';
GO
EXEC sp_dbcmptlevel 'cms_files_users', '100';
GO

Restore Script for Blackboard Learn Only (Legacy)

--If SQL Server 2000/2005/2008/ database compatible level to SQL Server 2012
--If SQL Server 2008 
--EXEC sp_dbcmptlevel 'bbadmin', '100';
--If SQL Server 2000/2005 
--EXEC sp_dbcmptlevel 'bbadmin', '110';

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 2000 database compatible level to SQL Server 2008
EXEC sp_dbcmptlevel 'bbadmin', '100';
GO
EXEC sp_dbcmptlevel 'bb_bb60', '100';
GO
EXEC sp_dbcmptlevel 'bb_bb60_stats', '100';
GO
EXEC sp_dbcmptlevel 'cms', '100';
GO
EXEC sp_dbcmptlevel 'cms_doc', '100';
GO

Troubleshooting

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.