Create a database using T SQL on a specified location

Question

How to create a database using T SQL script on a specified location? Let's say, I want to create a SQL server database on D:\temp\dbFolder. How to do this?

Answer 1

When you create the new database you specify the location. For example:

USE [master]
GO

    CREATE DATABASE [AdventureWorks] ON  PRIMARY 
    ( NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
     LOG ON 
    ( NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
    GO

Answer 2

From the SQL Server Books an example where database filenames are explicitely defined:

USE master
GO
CREATE DATABASE Sales
ON 
( NAME = Sales_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

Answer 3

1. Create folder on your file system: D:\temp\dbFolder\

2. Run the script:

   USE master; 
   GO 
   CREATE DATABASE TestDB1  
   ON      ( NAME = Sales_dat, FILENAME = 'D:\temp\dbFolder\TestDB1.mdf') 
   LOG ON  ( NAME = Sales_log, FILENAME = 'D:\temp\dbFolder\TestDB1.ldf'); 
   GO