Backing Up and Restoring Databases by Using the SQL Server 2000 Tools

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

With Microsoft , you can back up and restore information about your server configuration and about each site you host on your server or server farm. You can back up and restore the configuration database and content databases for your server or server farm. This backup and restore method allows you to recover from a server failure. You must be running Microsoft SQL Server 2000 to perform a database backup and restore.

Note: You can also back up and restore individual Web sites hosted on your server or server farm. This backup and restore method is not dependent on the type of database you are using. You can perform this backup and restore even if you are running Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE) instead of SQL Server 2000. For more information, see Backing Up and Restoring Web Sites .

In , all server and site configuration information is stored in the configuration database, and all site content is stored in content databases. If you want to back up all the information on your server or server farm, you must back up these databases by using the SQL Server 2000 backup and restore tools.

Note: You must be running SQL Server 2000 SP3 to back up and restore the databases used by . If you are running WMSDE, you can use the client tools for SQL Server 2000 to back up and restore WMSDE databases, but there is no provision for backing up and restoring from WMSDE itself. If you want to upgrade from WMSDE to SQL Server so that you can use the backup and restore tools for SQL Server and perform other database server tasks, see Migrating from WMSDE to SQL Server .

Backing Up the Databases

If you installed with SQL Server on the same computer, the configuration and content databases are stored under \Program Files\Microsoft SQL Server\MSSQL\Data by default. If you are in a server farm or remote SQL Server environment, the databases are stored on another server or multiple servers. There is always one configuration database for the entire server or server farm, and there is at least one content database.

Before you can back up the databases, you must identify which databases you need. By default, the databases are named as follows:

  • The configuration database is named sts_config.mdf by default. Note that this is only the default name. When you created the configuration database, you had the option to specify a different name.

  • The content databases are created with names based on the server name by default. For example, STS_server_name_1.mdf, STS_server_name_12.mdf, and so on. The database names are not sequential. Again, you may have chosen a different naming scheme for the content databases when you created them.

You use the SQL Server 2000 backup and restore tools to back up these databases. Be sure to back up both the configuration database and all of the content databases used by . To find out how to back up databases in SQL Server, see the SQL Server documentation.

Note: When you back up the database using the SQL Server 2000 tools, the backup file includes personalizations made by site users and personal data about site users. This data is also included when you restore from a backup. As part of your own internal privacy policies, you may want to inform users that this data is collected and stored during database backups.

Restoring from a Backup

Creating regular backups allows you to restore your servers and sites in case they happen to fail. To restore a server or server farm from a database backup, you must perform the following steps.

  1. On your server, or on the front-end Web servers in your server farm, in Internet Information Services (IIS), create the virtual servers to host your Web site content.

    For more information about creating a virtual server, see Extending Virtual Servers .

  2. Using the SQL Server restore tools, restore the databases from the backups.

    For more information about restoring databases in SQL Server, see the SQL Server documentation.

  3. In IIS, create the application pools for the content virtual servers.

    Be sure that you use domain accounts for the application pools, and that these accounts are members of the Security Administrators and Database Creators roles in SQL Server. For more information about creating application pools, see the Help system for Internet Information Services.

  4. On your server or front-end Web servers, install , and connect to the restored configuration database.

    For more information about installation, see the appropriate deployment scenario:

  5. Set the default content database server to the restored database server.

    You can use the Set Default Content Database Server page in SharePoint Central Administration. For more information, see Managing content databases.

  6. Extend each virtual server for your server or server farm using the Extend and map to another virtual server option on the Extend Virtual Server page, or by using the extendvsinwebfarm command-line operation.

    This option allows you to connect a new virtual server to a restored content database. Repeat this step for each new virtual server. For more information about mapping one virtual server to another virtual server, see Extending Virtual Servers .

  7. Add any additional content databases that have been restored. Use the Manage Content Databases page in SharePoint Central Administration to add content database to a virtual server. Repeat this step for each virtual server. For more information, see Managing content databases.

    Note: As you reconnect the content databases to your virtual servers, the Web sites for those content databases are restored. Note that only Web sites contained in the list of included paths for the virtual server are restored.

When you have completed these steps, your restoration is complete. All sites included in your backup should be functioning again, complete with the site content, users, and settings as they were when the sites were backed up.