How To: Maintain a Database (Visual Studio)
Maintaining a database involves shrinking, compacting, and repairing the database. By using Visual Studio, you can perform all these tasks from within the same dialog box.
To shrink a database
In Visual Studio 2010 SP1, open Server Explorer.
In Server Explorer, click Connect to Database, and then choose Microsoft SQL Server Compact 4.0.
In Server Explorer, right-click the SQL Server Compact 4.0 database that you added and choose Database Properties.
The Database Properties dialog box opens. Select Shrink and Repair from the left pane.
Click Shrink database by deleting free pages, and then click OK.
To compact a database
In Visual Studio, open Server Explorer.
In Server Explorer, click Connect to Database, and then choose Microsoft SQL Server Compact 4.0.
In Server Explorer, right-click the SQL Server Compact 4.0 database that you added and choose Database Properties.
The Database Properties dialog box opens. Select Shrink and Repair from the left pane.
Select Perform full database compaction.
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.
To repair a database
In Visual Studio, open Server Explorer.
In Server Explorer, click Connect to Database, and then choose Microsoft SQL Server Compact 4.0.
In Server Explorer, right-click the SQL Server Compact 4.0 database that you added and choose Database Properties.
The Database Properties dialog box opens. Select Shrink and Repair from the left pane.
Select Repair physically corrupted database.
If you select the Recover all possible corrupted rows check box, you can recover the corrupted rows.
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.
To create a case-sensitive database
In Visual Studio, open Server Explorer.
In Server Explorer, right-click Database Connections, and then choose Add Connection.
In the Add Connection, click Create.
In the Create New SQL Server Compact Database dialog box, type the name of the database file you wish to create. You may also precede the filename with a file path.
If the case-sensitive database you are creating has the same filename as an existing database, you may check Overwrite existing database file to replace the existing file.
Select the Collation that matches the sorting preferences for your language.
Check the Case sensitive box.
If you want to encrypt your database you must type a password in the New password textbox, and confirm it in the Confirm password text box. If your password does not match the minimum complexity requirements, you will be given an option to change it.
If you chose a password, the encryption mode will change from the default value UNENCRYPTED to Platform Default. You may choose another encryption mode from the Encryption mode drop-down list.
Click OK.