How to: Maintain a Database (SQL Server Management Studio)

  • Article

Maintaining a database involves shrinking, compacting, and repairing the database. By using SQL Server Management Studio, you can perform all these tasks from within the same dialog box.

How to shrink a database

  1. In Management Studio, open Object Explorer.

  2. In Object Explorer, click Connect, and then choose SQL Server Compact Edition.

  3. In the Connect to Server dialog box, choose the SQL Server 2005 Compact Edition (SQL Server Compact Edition)database that you want to shrink, and then click Connect.

  4. In Object Explorer, right-click the SQL Server Compact Edition database that you added and choose Properties.

  5. The Database Properties dialog box opens. Select Shrink and Repair from the left pane.

  6. Click Shrink database by deleting free pages, and then click OK.

How to compact a database

  1. In Management Studio, open Object Explorer.

  2. In Object Explorer, click Connect, and then choose SQL Server Compact Edition.

  3. In the Connect to Server dialog box, choose the SQL Server Compact Edition database that you want to compact, and then click Connect.

  4. In Object Explorer, right-click the SQL Server Compact Edition database that you added and choose Properties.

  5. The Database Properties dialog box opens. Select Perform full database compaction.

  6. Under File Options, you can either replace the existing database after compacting, or create a new database with a new file name. By selecting the Overwrite Existing Database File check box, you can modify the name and location of the existing database and then replace it.

How to repair a database

  1. In Management Studio, open Object Explorer.

  2. In Object Explorer, click Connect, and then choose SQL Server Compact Edition.

  3. In the Connect to Server dialog box, choose the SQL Server Compact Edition database that you want to repair, and then click Connect.

  4. In Object Explorer, right-click the SQL Server Compact Edition database that you added and choose Properties.

  5. The Database Properties dialog box opens. Select Repair physically corrupted database.

  6. If you select the Recover corrupted rows check box, you can recover the corrupted rows.

  7. Under File Options, you can either replace the existing database after repairing, or create a new database with a new file name. By selecting the Overwrite Existing Database File check box, you can modify the name and location of the existing database and then replace it.

See Also

Concepts

Using and Maintaining Databases (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance