Experiment 2: create and manage database
1. Create database
Assume that the SQL Server service has been started and log in to the computer as Administrator (the file name is customized); Please use the Management interface mode and T-SQL statement respectively to realize the following operations:
- It is required to create a student course database (named student) on local disk D, with only one data file and log file, and the file names are stu and stu respectively_ Log, physical name stu_data.mdf and stu_log.ldf, the initial size is 3MB, the growth mode is 10% and 1MB respectively, the maximum data file is 500MB, and the log file size is unlimited.
CREATE DATABASE student ON (NAME = stu, FILENAME = 'D:\stu_data.mdf', SIZE = 3MB, MAXSIZE = 500MB, FILEGROWTH = 10%) LOG ON (NAME = stu_log, FILENAME = 'D:\stu_log.ldf', SIZE = 3MB, MAXSIZE = unlimited, FILEGROWTH = 1MB)
- Create a Company database whose master data file logical name is Company_data, the physical file is Company.mdf, the initial size is 10MB, the maximum size is infinite, and the growth rate is 10%; The logical name of the log file for the database is Company_log, the physical file name is Company.ldf, the initial size is 1MB, the maximum size is 50MB, and the growth rate is 1MB.
CREATE DATABASE Company ON (NAME = Company_data, FILENAME = 'C:\Company.mdf', SIZE = 10MB, MAXSIZE = unlimited, FILEGROWTH = 10%) LOG ON (NAME = Company_log, FILENAME = 'C:\Company.ldf', SIZE = 1MB, MAXSIZE = 50MB, FILEGROWTH = 1MB)
- Create a database DB with 2 data files with logical names of DB_data1 and DB_data2, the initial size of the files is 5MB, and the maximum is 100MB, increasing by 10%; There is only one log file, with an initial size of 3MB, increasing by 10%; All files are stored in the D disk folder ceshi.
CREATE DATABASE DB ON (NAME = DB_data1, FILENAME = 'D:\ceshi\DB_data1.mdf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10%), (NAME = DB_data2, FILENAME = 'D:\ceshi\DB_data2.mdf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 10%) LOG ON (NAME = Company_log, FILENAME = 'D:\ceshi\DB_log.ldf', SIZE = 3MB, MAXSIZE = unlimited, FILEGROWTH = 10%)
2. Modify database
- Add the data file db2 in the database student, with the initial size of 10MB and the maximum size of 50MB, increasing by 10%;
ALTER DATABASE student ADD FILE ( NAME = db2, SIZE = 10MB, MAXSIZE = 50MB, FILENAME = 'D:\db2.mdf', FILEGROWTH = 10%)
- Add log files in the database student and save them in disk D. the initial size is 1MB, the maximum is unlimited, and the growth mode is 1MB;
ALTER DATABASE student ADD LOG FILE ( NAME = df, SIZE = 1MB, MAXSIZE = unlimited, FILEGROWTH = 1MB, FILENAME = 'D:\df.ldf')
- Modify the size of the master data file of the database student, and modify the initial size of the master data file to 10Mb, with a growth mode of 20%;
ALTER DATABASE student MODIFY FILE ( NAME = stu, SIZE = 10MB, FILEGROWTH = 20%)
3. Delete database
- Delete the database student auxiliary data file and the second log file;
ALTER DATABASE student remove FILE db2 go ALTER DATABASE student remove FILE df
- Delete the database company.
DROP DATABASE Company
4. Create and delete mode (framework)
- Create a stu schema in the student database for dbo users.
CREATE SCHEMA stu AUTHORIZATION dbo
- Create a new user test in the student database and create a default schema for the user.
CREATE SCHEMA AUTHORIZATION test
- Delete the stu schema from the student database.
DROP SCHEMA stu
Please describe in detail how to move all the physical files of the database student to'D:\xxx 'and ensure that the logical database is not affected.
First separate the database, then move the database file, and finally attach it.