Creating and Managing Databases and Physical Files

Next Post
By Admin at 5 Jan 2010, 17:18 PM
  • Database


    A database is a structured collection of records that is stored in a computer system. It contains a set of related database objects

    • Tables - Storages of structured data.
    • Views - Queries to present data from tables.
    • Indexes - Sorting indexes to speed up searches.
    • Stored Procedures - Predefined SQL programming units.
    • Users - Identifications used for data access control.
    • Other objects.

    System Databases


    System Databases will be created during the time of installation. They will help SQL Server to all the other databases and client execution sessions. There are four default system databases.

    • master - It is the main database of the SQL server – Server configuration details and runtime information will be stored.
    • model – It is a empty database. It is used to create the new databases
    • msdb - It used to background job processes
    • tempdb – For temporary operations

    Create New Database


    We create a Database using “Create Database” statement

    Syntax


    CREATE DATABASE

    Example

    1. CreateDatabase EmpDB
    2. GO

    The New database “EmpDB” created in the Sql Server. Now this database is empty because no table is there. We have to create a table in order to use the database.

    Set the Current Database


    When you entered into SQL Server, you have to select a database to work with. The “Use” statement will help you to set the current database.

    Syntax

    USE

    Example

    1. Use EmpDB

    Delete a Database


    Some time you might create the database incorrectly, or you don’t need that database. In that time you can use the “Delete Database” statement to remove the database from the SQL Server

    Syntax

    Drop Database

    Example

    1. DropDatabase EmpDB

    When you execute the command "Drop Database EmpDB", then the database will be deleted. Suppose you get an error message like "Cannot Drop Database EmpDB because it is currently in Use". This means the database "EmpDB" is in use. There are two ways to avoid this error message. The first way is that you can completely exit from Sql Server then again you can login to the server and can execute the drop database command. Another one is, you can switch to another database and can delete the EmpDB Database

    Example

    1. Use Master
    2. DropDatabase EmpDB

    List Database Names


    You can get a list of database names available in your SQL Server. “Sys.Databases” can be used to achieve this.

    Example

    1. select * from sys.databases

    undefined

    Primary Data File and Transaction Log


    Once the database is created then two files will be created on the hard disk. They are used for storing data and database configuration information. We have created the EmpDB database, so the following files should be created

    • EmpDB.mdf – Primary Data File
    • EmpDB_log.ldf - Transaction Log File

    We can check that with the following example

    1. Use EmpDB
    2. SELECT type_desc, physical_name, sizeFROM sys.database_files

    undefined

    sys.database_files view will help you to display the file names.

    Create Database Using Primary Data File and Transaction Log File


    We know that when you create a database file then the Primary Data File and Transaction Log File will be created. You can create a database by specifying .mdf and .ldf file name and location.

    Let us assume that we are going to create the Database Expenses, and will specify the two files Expenses.mdf and Expenses_log.ldf

    • Expenses.mdf – Primary Data File
    • Expenses.ldf - Transaction Log File

    Syntax


    CREATE DATABASE database_name
    ON (NAME = logical_data_name,
    FILENAME = physical_data_name,
    SIZE = n, MAXSIZE = n, FILEGROWTH = n)
    LOG ON (NAME = logical_log_name,
    FILENAME = physical_log_name,
    SIZE = n, MAXSIZE = n, FILEGROWTH = n)

    Example

    1. CREATEDATABASE Expenses
    2. ON (NAME = ExpensesData,
    3. FILENAME = 'C:\MyDb\Expenses.mdf',
    4. SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
    5. LOG ON (NAME = ExpensesLog,
    6. FILENAME = 'c:\MyDb\Expenses_log.LDF',
    7. SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)

    Above example, will create the database files located in the C:\MyDb directory:

    Rename a Database

    Sometimes you may like to change the database name. “Alter Database” statement will help you to change the database name.

    Syntax


    ALTER DATABASE
    Modify Name =

    Example

    1. use Expenses
    2. ALTERDATABASE Expenses MODIFYNAME = ExpensesDb

    Set Database state as Offline


    We set Database status to offline that means it will not available for some specific reasons (admin operations). If the change is done we can make it available.

    Syntax


    Alter Database Set Offline

    Example

    1. AlterDatabase Expenses Set Offline

    If Expenses is currently in use then the following error will be displayed.

    "Failed to restart the current database. The current database is switched to master."

    So you can change the current database to msdb and then execute the command

    Example

    1. use msdb
    2. AlterDatabase Expenses Set offline

    In the same manner you can set the database name to online

    Example

    1. AlterDatabase Expenses Set online

    Now the database will be available for transactions.

    List the Status of all the Databases


    sys.databases view will list all of the states of the databases in your system.

    Example

    1. SELECTname, state_desc from sys.databases

    undefined

    Move the Physical File


    As per our previous examples we are having the Expenses.mdf and Expenses_log.LDF' in the c:\MyDb location. Suppose you moved them to the d:\MyDb location you can use alter database command. First you can set the database status to offline then you can change the physical location then can set to online

    Example

    1. AlterDatabase Expenses Set offline
    2. Go
    3. ALTERDATABASE Expenses
    4. MODIFY FILE (NAME = ExpensesData,
    5. FILENAME = 'd:\MyDb\Expenses.mdf')
    6. GO

    The file "ExpensesData" has been modified in the system catalog. The new path will be used the next time the database is started.

    1. ALTERDATABASE Expenses
    2. MODIFY FILE (NAME = ExpensesLog,
    3. FILENAME = 'd:\MyDb\Expenses_Log.ldf')

    The file "ExpensesLog" has been modified in the system catalog. The new path will be used the next time the database is started.

    Now you can use the sys.database_files view to view the physical location of the database

    1. AlterDatabase Expenses Set online
    2. Go
    3. SELECT type_desc, name, physical_name, size
    4. FROM sys.database_files

    undefined

    Set Database to Readonly


    There are two options in database. They are READ_WRITE and READ_ONLY. READ_WRITE is the default option and you can do write and read operations. In READ_ONLY state you are allowed to write query buy you cannot do any modification operations.

    Alter Database is used to set the Update options. First we are going to set the database state to readonly

    Syntax

    ALTER DATABASE Expenses SET READ_ONLY

    Example

    1. use Expenses
    2. go
    3. ALTERDATABASE Expenses SET READ_ONLY
    4. go
    5. insertinto tblExp values('Travel',250)

    Result


    Msg 3906, Level 16, State 1, Line 1
    Failed to update database "Expenses" because the database is read-only
    .

    Here after setting the Expenses database to read only, then tried to insert a row in the tblExp. Hence we get an error.

    So you can set the database status to READ_WRITE and then try to insert the rows

    1. use Expenses
    2. go
    3. ALTERDATABASE Expenses SET READ_WRITE
    4. go
    5. insertinto tblExp values('Travel',250)

    Result


    (1 row(s) affected)

    Now one row will be inserted.

    User Access Options


    There are three options for users.

    • MULTI_USER – All users can access the database. It is the default option.
    • SINGLE_USER – Only one user at a time allowed to use the database.
    • RESTRICTED_USER –Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

    You can use the Alter Database statement to set the User access option

    1. use Expenses
    2. GO
    3. ALTERDATABASE Expenses SET SINGLE_USER
    4. GO

    Now connect to server with another client session and try:

    1. USE Expenses
    2. O

    Result

    Msg 924, Level 14, State 1, Line 1
    Database 'Expenses' is already open and can only have one user at a time.

    Go back to the first session and re-set the database to MULTI_USER:

    1. ALTERDATABASE Expenses SET MULTI_USER
    2. GO

    Now you can access the database in multiple client sessions.


    Comments

     

    Post a comment

    Please correct the following: