Run and test a full backup in SQL Server

  • Article

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

In this article:

Back up a database

Test the backups

To ensure that you can recover your existing environment in case something goes wrong during the upgrade process, you must back up all of the databases that are used by Microsoft Office SharePoint Portal Server 2003 before you run the upgrade process. SharePoint Portal Server 2003 uses several databases, as listed in the following table.

Database type Database name Notes

Configuration database

ID_Config_db

Required — one per farm.

Site database

ID_SITE

Required — one per portal site.

Profile database

ID_PROF

Required — one per portal site.

Component settings database

ID_SERV

Required — one per portal site.

Single sign-on database

ID_SSO

Optional — one per farm.

Content databases

STS_database_server_name_ID

Optional — there can be several if you have many team sites in your environment.

Note

The database names above are examples of the default naming structure. The naming structure for your databases may vary.

Perform a full backup operation with either the Stsadm command-line tool or SQL Server. If you are using SQL Server, use the simple recovery model, so that your transaction log is truncated.

Back up a database

Follow the appropriate steps to back up your databases in either SQL Server 2000 or SQL Server 2005.

Back up a database in SQL Server 2000

  1. On your database server, click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In SQL Server Enterprise Manager, click the plus sign next to Microsoft SQL Servers.

  3. Click the plus sign next to SQL Server Group.

  4. Click the plus sign next to (local) (Windows NT).

  5. Click the plus sign next to Databases.

  6. Right-click the database you want to back up, point to All Tasks, and then click Backup Database.

  7. In the SQL Server Backup dialog box, in the Name box, specify a name for the backup, and then in the Backup area, select Database - complete.

  8. In the Destination area, either select an existing destination, or:

    1. Click Add.

    2. In the Select Backup Destination box, select File Name, and then next to the File Name box, click the browse button.

    3. In the Backup Device Location - (local) dialog box, in the File name box, type a file name, and then click OK.

    4. Click OK again to close the Select Backup Destination dialog box.

  9. Click OK to start the backup process.

  10. Click OK to acknowledge that the backup process is complete.

Repeat these steps to back up the configuration database plus all of the other databases that are used by Windows SharePoint Services in your environment.

Important

You should also back up any customizations (such as site definitions, Web Parts, and so on) and other files that you would need in case that you have to re-create your previous-version environment.

Back up a database in SQL Server 2005

  1. On your database server, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. In the Connect to Server box, fill in the connection information, and then click Connect.

  3. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  4. Expand Databases and select the database to back up.

  5. Right-click the database, point to Tasks, and then click Back Up.

    The Back Up Database dialog box appears.

  6. In the Source section, in the Database box, verify the database name.

  7. In the Backup type box, select Full.

  8. Under Backup component, click Database.

  9. In the Name text box, either accept the default backup set name suggested or enter a different name for the backup set.

  10. In the Destination section, choose the type of backup destination by clicking Disk or Tape, and then select a destination. To create a different destination, click Add.

  11. Click OK to start the backup.

Repeat these steps to back up the configuration database plus all of the other databases that are used by Windows SharePoint Services in your environment.

Important

You should also back up any customizations (such as site definitions, Web Parts, and so on) and other files that you would need in case that you have to re-create your previous-version environment.

After you have backed up all the databases, use the SQL Server DBCC shrinkfile command to free unused log space. This makes the logs as empty as possible. For more information, see Shrinking the Transaction Log (https://go.microsoft.com/fwlink/?LinkId=105233).

Test the backups

You have to be sure that these backups are valid so that you can recover if there is a hardware failure or data corruption during the upgrade process. To test your backups, set up a non-production front-end Web server computer and a computer that is running SQL Server, restore the backups and install any customizations (such as site definitions, Web Parts, and so on), and then verify that the restored backup is functional.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for Office SharePoint Server 2007.