Create a SQL Server Compact Edition Database Using SQL Server 2005 Management Studio

Yesterday I showed you how to create a SQL Server Compact Edition database, using Visual Studio. Today I’ll show you how to create the same database using SQL Server 2005 Management Studio.

When you launch SQL Server Management Studio, you are shown the normal “Connect to Server” dialog. First, you need to change the Server Type to “SQL Server Compact Edition”. Now in the Database File area, click on the drop down and pick “<New Database…>”.

After picking the new database option, you should see a familiar looking dialog. Enter the name for your database, or browse for it. Like I said yesterday, I always encrypt and use a password, if you want to as well check Encrypt on and enter a password. Remember if you do choose to encrypt, you must enter a password.  

Like yesterday, if you use a weak password you are warned. Either click no and enter a stronger password or click Yes to continue.

Now your database has been created, and you are brought back to the original “Connect to Server” dialog you saw. SQL Server Management Studio has helpfully filled in the Database File name for you as well as the password. I clicked “Remember Password” on then clicked the Connect button.

You are now brought to the standard SQL Server Management Studio interface.

From here on, you’ll find things work almost identically to Visual Studio. If you right click on the Tables, you’ll see the same menu. If you pick “New Table” you will see the same dialog as yesterday. Rather than repeating myself, I’ll suggest you take a look at yesterday’s blog entry for more details on creating tables and adding rows.

I can hear the questions now: Why would you use SQL Server Management Studio over Visual Studio to create and manage your SSCE database?

It really comes down to a matter of your viewpoint. If you are a long time developer, you will be more comfortable with Visual Studio and thus want to use it to handle your SSCE database. On the other hand, if you are a DBA and have been assigned the task of managing the SSCE databases, you will be more familiar with management studio and thus want to use it as your tool of choice.

So there you go, yet another way to create a SQL Server Compact Edition database. Which tool you use is up to you!