Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Creating Databases with Specified Physical Files

By: FYIcenter.com

(Continued from previous topic...)

How to create database with physical files specified?

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:\temp directory:

USE master
GO

DROP DATABASE FyiCenterData
GO

CREATE DATABASE FyiCenterData
   ON (NAME = FyiCenterData,
      FILENAME = 'C:\temp\FyiCenterData.mdf',
      SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
   LOG ON (NAME = FyiCenterLog,
      FILENAME = 'C:\temp\FyiCenterLog.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      FyiCenterData   C:\temp\FyiCenterData.mdf   1280
LOG       FyiCenterLog    C:\temp\FyiCenterLog.ldf     128

(Continued on next topic...)

  1. What is a database?
  2. What is the simplest way to create a new database?
  3. How to set the current database?
  4. How to delete a database?
  5. Why I am getting this error when dropping a database?
  6. How to get a list all databases on the SQL server?
  7. Where is my database stored on the hard disk?
  8. How to create database with physical files specified?
  9. How to rename databases?
  10. Why I am getting this error when renaming a database?
  11. What are database states?
  12. How to set a database state to OFFLINE?
  13. How to move database physical files?
  14. How to set database to be READ_ONLY?
  15. How to set database to be SINGLE_USER?
  16. What are system databases?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...