T-SQL manage database and database files

1. view the database status
Directory view: Select name, state_desc from SYS. databases where name = 'stusystem'
Function: Select databasepropertyex ('stusystem ', 'status ')
Stored Procedure: sp_helpdb stusystem
2. Modify the database
2-1 modify Database Name
Alter database database_name modify name = new_name
Exec sp_renamedb 'studentsystem', 'stusystem'
2-2 expand the database
Alter database stusystem
Add File
(Name, filename, size, maxsize, filegrowth)
2-3: scale down the database. The size of the database cannot be smaller than the minimum size of the database.
Automatic database contraction: In the alter table statement, you can set the auto_shrink option to on
Manual database shrinking:
Desc shrinkdatabase
(Database_name | database_id | 0 name or ID of the database to be reduced. If it is set to 0, the current database is used.
[, Target_percent] percentage of available space required in the database file after shrinking
)
2-4 shrink database files
Manual database file shrinking
DBCC shrinkfile
(
{File_name | file_id}
{[, Emptyfile]
| [[, Target_size] [, {notruncate | truncateonly}]
}
)
[With no_infomsgs]
2-5 mobile database files (applicable to SQL Server instances)
Alter database database_name
Modify file (name = logical_name, filename = 'new _ path \ OS _file_name ')
2-6 delete a database
Drop database database_name [,... n]
2-7 separate databases
Database separation means to delete a database from an SQL server instance, but keep the database unchanged in its data files and transaction logs.
If any of the following conditions exists, the database cannot be detached.
This database is a system database.
The database has been copied and released. To detach a database, you must first execute sp_replicationdboption
A database snapshot exists in the database. You must first Delete All Database snapshots and then detach the database.
The database is mirroring in a database image session. The session must be terminated before the database is detached.
The database is in a suspicious state. You must set the database to emergency mode and then detach the database.
Exec sp_detach_db stusystem
2-8 additional database
Create Database stusystem
On
(
Name = stusystem_dat,
Filename = 'e: \ SQL \ stusystem \ stusystem_dat.mdf ',
Size = 20 mb,
Maxsize = 40 MB,
Filegrowth = 5%
),
(
Name = stusystem_dat1,
Filename = 'e: \ SQL \ stusystem \ stusystem_dat1.ndf ',
Size = 20 mb,
Maxsize = 40 MB,
Filegrowth = 5%
),
(
Name = stusystem_dat2,
Filename = 'e: \ SQL \ stusystem \ stusystem_dat2.ndf ',
Size = 20 mb,
Maxsize = 40 MB,
Filegrowth = 5%
)
Log On
(
Name = stusystem_log,
Filename = 'e: \ SQL \ stusystem \ stusystem_log.ldf ',
Size = 10 MB,
Maxsize = 20 mb,
Filegrowth = 1 MB
)
For attach
3. Database snapshot (not supported by Express, which must be stored in the partition of the NTFS file system)
3-1 Working Principle
Database snapshots are related to the source database. The database snapshot must be on the same server instance as the database.
Database snapshots run on Data Pages
3-2 create a database Snapshot
Create Database database_snapshot_name
On
(
Name = logical_file_name,
Filename = 'OS _ file_name ',
) [,... N]
As snapshot of source_database_name
3-3 restore a database to a database Snapshot
Resotre database database_name from database_snapshot = database_snapshot_name