MS SQL Server Concepts and Programming Question: Download Questions PDF
How to create database with physical files specified in MS SQL Server?
Answer:
If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement:
CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
For example, the following statement will create a database with database files located in the C: emp directory:
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
CREATE DATABASE GlobalGuideLineDatabase
ON (NAME = GlobalGuideLineDatabase,
FILENAME = 'C: empGlobalGuideLineDatabase.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C: empGlobalGuideLineDatabase.ldf',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
GO
SELECT type_desc, name, physical_name, size
FROM sys.database_files
GO
type_desc name physical_name size
ROWS GlobalGuideLineDatabase C: empGlobalGuideLineDatabase.mdf 1280
LOG GlobalGuidelineLog C: empGlobalGuideLineDatabase.ldf 128
CREATE DATABASE database_name
ON (NAME = logical_data_name,
FILENAME = physical_data_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
LOG ON (NAME = logical_log_name,
FILENAME = physical_log_name,
SIZE = x, MAXSIZE = y, FILEGROWTH = z)
For example, the following statement will create a database with database files located in the C: emp directory:
USE master
GO
DROP DATABASE GlobalGuideLineDatabase
GO
CREATE DATABASE GlobalGuideLineDatabase
ON (NAME = GlobalGuideLineDatabase,
FILENAME = 'C: empGlobalGuideLineDatabase.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = GlobalGuidelineLog,
FILENAME = 'C: empGlobalGuideLineDatabase.ldf',
SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)
GO
SELECT type_desc, name, physical_name, size
FROM sys.database_files
GO
type_desc name physical_name size
ROWS GlobalGuideLineDatabase C: empGlobalGuideLineDatabase.mdf 1280
LOG GlobalGuidelineLog C: empGlobalGuideLineDatabase.ldf 128
Download MS SQL Server Interview Questions And Answers
PDF
Previous Question | Next Question |
Where is my database stored on the hard disk in MS SQL Server? | How to rename databases in MS SQL Server? |