How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks