Objective: Let us create database using sqlcmd
Refer: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16&tabs=sqlpool
command:
USE master;
GO
CREATE DATABASE tsqltestdb
ON PRIMARY
( NAME = tsqltestdbpff1,
FILENAME = 'C:\SQLfiles\data\tsqltestdbf1.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
( NAME = tsqltestdbpff2,
FILENAME = 'C:\SQLfiles\data\tsqltestdbf2.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
FILEGROUP tsqltestdbfg1
( NAME = tsqltestdbfg1f1,
FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f1.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
( NAME = tsqltestdbfg1f2,
FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f2.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = tsqltestdblg1,
FILENAME = 'C:\SQLfiles\log\tsqltestdblg1.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
GO
CREATE DATABASE tsqltestdb
ON PRIMARY
( NAME = tsqltestdbpff1,
FILENAME = 'C:\SQLfiles\data\tsqltestdbf1.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
( NAME = tsqltestdbpff2,
FILENAME = 'C:\SQLfiles\data\tsqltestdbf2.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
FILEGROUP tsqltestdbfg1
( NAME = tsqltestdbfg1f1,
FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f1.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB ),
( NAME = tsqltestdbfg1f2,
FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f2.ndf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = tsqltestdblg1,
FILENAME = 'C:\SQLfiles\log\tsqltestdblg1.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
Actual Result:
1> USE master;
2> GO
Changed database context to 'master'.
1> CREATE DATABASE tsqltestdb
2> ON PRIMARY
3> ( NAME = tsqltestdbpff1,
4> FILENAME = 'C:\SQLfiles\data\tsqltestdbf1.mdf',
5> SIZE = 10MB,
6> MAXSIZE = 50MB,
7> FILEGROWTH = 10MB ),
8> ( NAME = tsqltestdbpff2,
9> FILENAME = 'C:\SQLfiles\data\tsqltestdbf2.ndf',
10> SIZE = 10MB,
11> MAXSIZE = 50MB,
12> FILEGROWTH = 10MB ),
13> FILEGROUP tsqltestdbfg1
14> ( NAME = tsqltestdbfg1f1,
15> FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f1.ndf',
16> SIZE = 10MB,
17> MAXSIZE = 50MB,
18> FILEGROWTH = 10MB ),
19> ( NAME = tsqltestdbfg1f2,
20> FILENAME = 'C:\SQLfiles\data\tsqltestdbfg1f2.ndf',
21> SIZE = 10MB,
22> MAXSIZE = 50MB,
23> FILEGROWTH = 10MB )
24> LOG ON
25> ( NAME = tsqltestdblg1,
26> FILENAME = 'C:\SQLfiles\log\tsqltestdblg1.ldf',
27> SIZE = 5MB,
28> MAXSIZE = 25MB,
29> FILEGROWTH = 5MB ) ;
30> GO
1>
Let us examine the result:
Command:
select database_id,file_id,file_guid,name,physical_name,size,state,state_desc
from sys.master_files
order by 1,2,4;
go
select database_id,file_id,file_guid,name,physical_name,size,state,state_desc
from sys.master_files
order by 1,2,4;
go
Output:
1> select database_id,file_id,file_guid,name,physical_name,size,state,state_desc
2> from sys.master_files
3> order by 1,2,4;
4> go
2> from sys.master_files
3> order by 1,2,4;
4> go
database_id file_id file_guid name physical_name size state state_desc
----------- ----------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------------------------------------------------------------
1 1 NULL master C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\master.mdf 760 0 ONLINE
1 2 NULL mastlog C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\mastlog.ldf 288 0 ONLINE
2 1 NULL tempdev C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb.mdf 1024 0 ONLINE
2 2 NULL templog C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\templog.ldf 1024 0 ONLINE
2 3 33D5E8A0-F77C-4DBA-BB31-28C35A4A8D5F temp2 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf 1024 0 ONLINE
2 4 FB371C7D-9FCA-47D9-A956-B937895C5D19 temp3 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf 1024 0 ONLINE
2 5 546E68BA-7266-4C9D-8832-F5CE32903494 temp4 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf 1024 0 ONLINE
3 1 NULL modeldev C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\model.mdf 1024 0 ONLINE
3 2 NULL modellog C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\modellog.ldf 1024 0 ONLINE
4 1 EC9EBB67-5637-4A78-85AB-00E55029EF7A MSDBData C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf 2376 0 ONLINE
4 2 E80E2A4F-0889-444F-999B-2D360E30B547 MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf 2768 0 ONLINE
5 1 D8DB363B-0224-4408-80AF-D98F9D109443 tsqltestdbpff1 C:\SQLfiles\data\tsqltestdbf1.mdf 1280 0 ONLINE
5 2 814FDF6C-9DA1-46B4-AB67-9A99569C8462 tsqltestdblg1 C:\SQLfiles\log\tsqltestdblg1.ldf 640 0 ONLINE
5 3 1F3EACDC-40A4-4C9D-A3A2-650D26584D66 tsqltestdbpff2 C:\SQLfiles\data\tsqltestdbf2.ndf 1280 0 ONLINE
5 4 A7BAC1EA-CBA2-4F41-B502-207038E7C97C tsqltestdbfg1f1 C:\SQLfiles\data\tsqltestdbfg1f1.ndf 1280 0 ONLINE
5 5 2B0532C0-E96E-477C-9E9C-C7012AEC5BCF tsqltestdbfg1f2 C:\SQLfiles\data\tsqltestdbfg1f2.ndf 1280 0 ONLINE
(16 rows affected)
We got the results now.The actual files are placed like below
C:\SQLfiles>tree /F .
Folder PATH listing
Volume serial number is 00000012 1C72:F915
C:\SQLFILES
├───data
│ tsqltestdbf1.mdf
│ tsqltestdbf2.ndf
│ tsqltestdbfg1f1.ndf
│ tsqltestdbfg1f2.ndf
│
└───log
tsqltestdblg1.ldf
C:\SQLfiles>
Folder PATH listing
Volume serial number is 00000012 1C72:F915
C:\SQLFILES
├───data
│ tsqltestdbf1.mdf
│ tsqltestdbf2.ndf
│ tsqltestdbfg1f1.ndf
│ tsqltestdbfg1f2.ndf
│
└───log
tsqltestdblg1.ldf
C:\SQLfiles>
I used SSMS to create a script from the database to see how does this manual command differs from a create database command ran using GUI.
Only the file definations and database names shown as difference. Rest of the db properties matched!!!
The actual script looks like below...
USE [master]
GO
/****** Object: Database [tsqltestdb2] Script Date: 5/7/2023 4:37:23 PM ******/
CREATE DATABASE [tsqltestdb2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'tsqltestdb2pff1', FILENAME = N'C:\SQLfiles\data\tsqltestdb2f1.mdf' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 10240KB ),
( NAME = N'tsqltestdb2pff2', FILENAME = N'C:\SQLfiles\data\tsqltestdb2f2.ndf' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 10240KB ),
FILEGROUP [tsqltestdb2fg1]
( NAME = N'tsqltestdb2fg1f1', FILENAME = N'C:\SQLfiles\data\tsqltestdb2fg1f1.ndf' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 10240KB ),
( NAME = N'tsqltestdb2fg1f2', FILENAME = N'C:\SQLfiles\data\tsqltestdb2fg1f2.ndf' , SIZE = 10240KB , MAXSIZE = 51200KB , FILEGROWTH = 10240KB )
LOG ON
( NAME = N'tsqltestdb2lg1', FILENAME = N'C:\SQLfiles\log\tsqltestdb2lg1.ldf' , SIZE = 5120KB , MAXSIZE = 25600KB , FILEGROWTH = 5120KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [tsqltestdb2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [tsqltestdb2] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [tsqltestdb2] SET ANSI_NULLS OFF
GO
ALTER DATABASE [tsqltestdb2] SET ANSI_PADDING OFF
GO
ALTER DATABASE [tsqltestdb2] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [tsqltestdb2] SET ARITHABORT OFF
GO
ALTER DATABASE [tsqltestdb2] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [tsqltestdb2] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [tsqltestdb2] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [tsqltestdb2] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [tsqltestdb2] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [tsqltestdb2] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [tsqltestdb2] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [tsqltestdb2] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [tsqltestdb2] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [tsqltestdb2] SET ENABLE_BROKER
GO
ALTER DATABASE [tsqltestdb2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [tsqltestdb2] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [tsqltestdb2] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [tsqltestdb2] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [tsqltestdb2] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [tsqltestdb2] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [tsqltestdb2] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [tsqltestdb2] SET RECOVERY FULL
GO
ALTER DATABASE [tsqltestdb2] SET MULTI_USER
GO
ALTER DATABASE [tsqltestdb2] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [tsqltestdb2] SET DB_CHAINING OFF
GO
ALTER DATABASE [tsqltestdb2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [tsqltestdb2] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [tsqltestdb2] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [tsqltestdb2] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
ALTER DATABASE [tsqltestdb2] SET QUERY_STORE = OFF
GO
ALTER DATABASE [tsqltestdb2] SET READ_WRITE
GO
Thank you!
No comments:
Post a Comment