CODEWITHSUNDEEP
sqlsql-servert-sql
Red Devil
Red Devil

Reputation: 2403

Creating database with only ldf file created 2 .ldf file

When I run this query without specifying the ldf file it automatically creates the ldf file.

Declare @DBname nvarchar(50) = 'Test'
Declare @path nvarchar(50) = 'D:\DB'
Declare @query nvarchar(max) 

set @query = '

CREATE DATABASE ' + @DBname + '
ON
( NAME = ' +@DBname+',  
    FILENAME = '''+@path+'\'+@DBname+'.mdf'+''',
    SIZE = 1024,
    MAXSIZE = unlimited,
    FILEGROWTH = 500 )  
'
exec (@query)

But when I run this query:

Declare @DBname nvarchar(50) = 'Test'
Declare @path nvarchar(50) = 'D:\DB'
Declare @query nvarchar(max) 

set @query = '

CREATE DATABASE ' + @DBname + '
ON 
( NAME = '+@DBname+'_log'+',  
    FILENAME = ''' +@path+'\'+@DBname+'_log.ldf'+''',
    SIZE = 100,
    MAXSIZE = unlimited,
    FILEGROWTH = 100 );
'
exec (@query)

It creates 2 .ldf file without .mdf file. I don't understand why it creates 2 .ldf file and not mdf file automatically as it did in the above query:

Upvotes: 0

Views: 604

Answers (2)

SAS
SAS

Reputation: 4045

You have specified ldf extension to the mdf file. It is still a data file. The log file is specified with 'LOG ON', and the data file with 'ON', just like you did.

The actual log file specification is optional, and it is created by default.

Look at the syntax: https://learn.microsoft.com/en-us/sql/relational-databases/databases/create-a-database?view=sql-server-2017

Upvotes: 4

Thom A
Thom A

Reputation: 96004

The file you are naming in your SQL is the database file, not the Log file. Therefore you are creating an mdf file and giving it an ldf extension. Much like if you were to create a xlsx file but give it an extension of csv.

If you want to specify the details of the log file, you need to do so using the LOG ON syntax; as shown in the documentation Creating a database that specifies the data and transaction log files:

USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB ) ;
GO

In your case, this would likely result in:

DECLARE @DBname sysname = N'Test';
DECLARE @path sysname = N'D:\DB';
DECLARE @query nvarchar(MAX);

SET @query = N'CREATE DATABASE ' + QUOTENAME(@DBname) + N'
ON 
( NAME = N'  + QUOTENAME(@DBname, '''') + N',  
    FILENAME = N' + QUOTENAME(@path + N'\' + @DBname + N'.mdf', '''') + N',
    SIZE = 1024,
    MAXSIZE = unlimited,
    FILEGROWTH = 500 )
LOG ON
( NAME = N'  + QUOTENAME(@DBname + '_log', '''') + ',  
    FILENAME = N' + QUOTENAME(@path + N'\' + @DBname + N'_log.ldf', '''') + ',
    SIZE = 100,
    MAXSIZE = unlimited,
    FILEGROWTH = 100 );';

EXEC sys.sp_executesql @query;

Note that I have changed your data types to sysname and used QUOTENAME to avoid injection. If you are going to have a path longer than 128 characters this won't work; but I assume that is unlikely based on the example.

If you do have more than 128 characters use the syntax like the below for the path expressions.

N'...N''' + REPLACE({Your Path  Expression}, '''', '''''') + N''' ...'

Upvotes: 0

Related Questions