Create database based on a mdf file and specify location to store new database?

Question

I create a database based on a mdf file. However I want to be able to store the database in a different directory.

I have,

CREATE DATABASE dbname ON (FILENAME = N'C:\sql_data\dbname.mdf')

Thanks in advance.

JodyT · Accepted Answer · 2014-06-16 11:29:24Z

1

How you can specifiy a different locations for your database files is explained on the Technet page CREATE DATABASE .

CREATE DATABASE dbname 
ON 
(NAME = dbname_data, FILENAME = 'C:\temp\dbname.mdf')

If you want to do this with an already existing mdf file then you probably need to use FOR ATTACH.

CREATE DATABASE dbname 
ON (FILENAME = 'C:\temp\test\dbname.mdf')
FOR ATTACH;

It will create a new log file for you if you don't specifiy a location for the log file.

answered Jun 16, 2014 at 11:29

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1