SqlServer series: Database Operations

1. CREATEDATABASE creation Syntax: CREATEDATABASEdatabase_name [ON [PRIMARY] filespec [,... n] [, filegroup [,... n] [LOGONfilespec [,... n] [COLLATEcollation_name] filespec: {(NAMElogical_file_name, FILENAME

1. create database Syntax: create database database_name [ON [PRIMARY] filespec [,... n] [, filegroup [,... n] [log on filespec [,... n] [COLLATE collation_name] filespec ::={ (NAME = logical_file_name, FILENAME

1. Create a database

　　Create database Syntax:

CREATE DATABASE database_name [ ON       [ PRIMARY ] 
 
   [ ,...n ]       [ , 
  
    [ ,...n ] ]       [ LOG ON 
   
     [ ,...n ] ] ] [ COLLATE collation_name ]
    
      ::= {(    NAME = logical_file_name ,    FILENAME = { 'os_file_name' | 'filestream_path' }     [ , SIZE = size [ KB | MB | GB | TB ] ]     [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]     [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ])}
    
   
  
 

ON: defines database data files. PRIMARY points out that the defined file is the master data file. If it is omitted, the first defined file is the master data file.

Log on: the LOG file used to define the database. If no log on exists, SQL Server automatically creates a LOG file.

File Types and recommended extensions in databases: primary data file. mdf, secondary data file. ndf, transaction log. ldf.

Create a database with no specified files:

-- Drop the database if it already existsIF  EXISTS (    SELECT name         FROM sys.databases         WHERE name = N'Portal')DROP DATABASE PortalGOCREATE DATABASE PortalGO

Create a database with the specified data file and transaction log file:

CREATE DATABASE [Portal] ON  PRIMARY (    NAME = N'Portal',    FILENAME = N'F:\Database\Portal.mdf' ,    SIZE = 5MB ,    FILEGROWTH = 1MB ) LOG ON(    NAME = N'Portal_log',    FILENAME = N'F:\Database\Portal_log.ldf' ,    SIZE = 2MB ,    FILEGROWTH = 10%)

Create a database to specify multiple data and transaction log files:

CREATE DATABASE [Portal] ON  PRIMARY (    NAME = N'Portal',    FILENAME = N'F:\Database\Portal.mdf' ,    SIZE = 5MB ,    FILEGROWTH = 1MB ), (    NAME = N'Portal_Data_2014',    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,    SIZE = 5MB ,    FILEGROWTH = 1MB ) LOG ON(    NAME = N'Portal_log',    FILENAME = N'F:\Database\Portal_log.ldf' ,    SIZE = 2MB ,    FILEGROWTH = 10%),(    NAME = N'Portal_log_2014',    FILENAME = N'F:\Database\Portal_log_2014.ldf' ,    SIZE = 2MB ,    FILEGROWTH = 10%)

Create a database with a file group:

CREATE DATABASE [Portal] ON  PRIMARY (    NAME = N'Portal',    FILENAME = N'F:\Database\Portal.mdf' ,    SIZE = 10MB ,    FILEGROWTH = 1MB ), FILEGROUP [p2014]  (    NAME = N'Portal_Data_2014',    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,    SIZE = 5MB ,    FILEGROWTH = 1MB ) LOG ON(    NAME = N'Portal_log',    FILENAME = N'F:\Database\Portal_log.ldf' ,    SIZE = 2MB ,    FILEGROWTH = 10%)

2. Modify the database

Modify database Syntax:

ALTER DATABASE database_name {    
 
    | 
  
   }[;]
   
    ::={    ADD FILE 
    
      [ ,...n ]         [ TO FILEGROUP { filegroup_name } ]  | ADD LOG FILE 
     
       [ ,...n ]   | REMOVE FILE logical_file_name   | MODIFY FILE 
      
       }
       
        ::= ( NAME = logical_file_name [ , NEWNAME = new_logical_name ] [ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ] [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] [ , OFFLINE ]) 
        
         ::={ | ADD FILEGROUP filegroup_name [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ] | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name { 
         
           | DEFAULT | NAME = new_filegroup_name }}
          
           ::={ { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }}
          
         
        
       
      
     
    
   
  
 

Add a file group:

ALTER DATABASE [Portal] ADD FILEGROUP [p2014]

Add a specified file group:

ALTER DATABASE [Portal] ADD FILE(    NAME = N'Portal_Data_2014',    FILENAME = N'F:\Database\Portal_Data_2014.ndf' ,    SIZE = 5MB ,    FILEGROWTH = 1MB)TO FILEGROUP [p2014]

　　Delete database files:

ALTER DATABASE [Portal] REMOVE FILE Portal_Data_2014

　　Modify data Name:

ALTER DATABASE [Portal] MODIFY NAME = [Portal_2014]

EXEC sp_renamedb [Portal], [Portal_2014]

Modify and set the default file group:

ALTER DATABASE [Portal] MODIFY FILEGROUP [PRIMARY] DEFAULT

3. delete a database

Delete database Syntax:

DROP DATABASE { database_name | database_snapshot_name } [ ,...n ] [;]

Example:

DROP DATABASE [Portal]

4. Database Separation

　　Use the system stored procedure sp_detach_db to separate the database.

sp_detach_db [ @dbname= ] 'database_name'     [ , [ @skipchecks= ] 'skipchecks' ]     [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ] 

EXEC sp_detach_db [Portal]

If you directly run the SQL statement for database separation, you may be prompted that a process (User) is in use and the separation fails. To solve this problem, first check which processes (users) are using the database.

View users and processes:

USE [master]sp_who

Terminate the process that occupies the database and then detach the database:

USE [master]KILL 55KILL 56KILL 57EXEC sp_detach_db [Portal]

5. Attach a database

Use create database to attach a DATABASE:

CREATE DATABASE [Portal]ON(    FILENAME = 'F:\Database\Portal.mdf')FOR ATTACH

CREATE DATABASE [Portal]ON(    FILENAME = 'F:\Database\Portal.mdf'),(    FILENAME = 'F:\Database\Portal_log.ldf')FOR ATTACH

Attach a database using the system stored procedure:

EXEC sp_attach_db [Portal], 'F:\Database\Portal.mdf'

EXEC sp_attach_db [Portal], 'F:\Database\Portal.mdf', 'F:\Database\Portal_log.ldf'

6. view database information

SQL Server allows you to view database information in multiple ways, such as using directory views, functions, and stored procedures.

6.1> use directory View

Use the directory view to view basic database information:

Using sys. databse_files: view the database file information;

Using sys. filegroups: view the database group information;

Using sys. master_files: view the basic information and status information of database files;

◊ Sys. database: displays the basic information of a database in the database and file directory view.

SELECT * FROM sys.databases WHERE name = 'Northwind'