Database Management in SQL Server

1. Create a database

 

Database_name: name of the database, which cannot exceed 128 characters

On: Specifies the explicit definitions of the database file box file group.

Primary: Specifies the master database file or master file group. The first file in the primary file group is considered as the primary database file, which contains the logical starting point of the database and the system table of the database. If there is no primary item, the first file listed in the vreate DATABASE Command will be the master file by default.

Filespec: file description. These words will be used for the purpose of the file. When many people develop database application systems, these words will be of great help to others to understand the purpose of the file.

Filegroupspec: File Group description, used to identify the purpose of a file group, mainly used for collaborative development by multiple people

Log on: a clear definition of the transaction log file. If the log on option is not available, the system will automatically generate a transaction log file with the same file name prefix as the database name, with a capacity of 1/4 of the size of all database files

Name: Specifies the logical name of the file in SQL Server.

Filename: Specifies the path and name of the file stored in the operating system.

Size: Specifies the initial capacity of the database.

Maxsize: specifies the maximum file capacity.

For load: This option is set to be compatible with earlier versions of SL Server7.0.

For attach: used to attach existing database files to a new database, instead of re-creating database files.

 

For example, create a database named "mingri. The primary data file name is mingridat. MDF. The initial size is 10 MB, the maximum storage space is 50 MB, and the increase is 5 MB. The log file name is mingrilog. LDF. The initial size is 5 MB, the maximum storage space is 25 MB, and the increase size is 5 MB.

create database mingrion(name=mingridat,filename='d:\mingridat.mdf',size=10,maxsize=50,filegrowth=5)log on(name='mingrilog',filename='d:\mingrilog.ldf',size=5mb,maxsize=25mb,filegrowth=5mb)

 

 

2. view database information

 

 

 

Use the system stored procedure sp_helpdb

Sp_helpdb [[@ dbname =] 'name']

 

[@ Dbname =] 'name': name of the database to be viewed

Name: the data type is sysname, with no default value.

 

 

3. Modify the database

Add file: Specifies the database file to be added.

To filegroup: Specifies the file group to be added.

Add Log File: Specify the transaction log file to be added.

Remove File: Delete the definition of a specified file from the database system table and delete its physical parts. The object can be deleted only when it is null.

Add filegroup: Specifies the file group to be added.

 

For example, add the file group "loving" to the "mingri" database ".

alter database mingriadd file(name=loving,filename='d:\loving.ndf',size=5mb,maxsize=25mb,filegrowth=5mb)

 

 

Iv. Database rename

Sp_renamedb [@ dbname =] 'old _ name ',

[@ Newname =] 'new _ name'

[@ Dbname =] 'old _ name': indicates the current name of the database.

[@ Newname =] 'new _ name': indicates the new name of the database.

 

For example, change the database name "mingri" to "love" 

exec sp_renamedb 'mingri','love'

5. delete a database

Drop database database_name [,... n]

 

Database_name: indicates the name of the database to be deleted.