SqlServer modifies the storage location of database files and log files, and SQL server log files
-- View the current storage location select database_id, name, physical_name AS CurrentLocation, state_desc, size from sys. master_files where database_id = db_id (N 'database name'); -- modify the storage location of the file to take effect next time -- testDb indicates the Database Name, alter database name modify file (name = file name (excluding the suffix), filename = 'file storage path '); alter database name modify file (name = file name (excluding the suffix), filename = 'file storage path'); eg. alter database testDb modify file (name = testDb, filename = 'G: \ SQL _DATA \ testDb. mdf '); alter database testDb modify file (name = testDb_log, filename = 'G: \ SQL _DATA \ testDb \ testdb_log.ldf'); -- modify the default database file storage location (effective immediately) EXEC xp_instance_regwrite @ rootkey = 'HKEY _ LOCAL_MACHINE ', @ key = 'Software \ Microsoft \ MSSQLServer', @ value_name = 'defaultdata', @ type = REG_SZ, @ value = 'e: \ MSSQL_MDF \ data' GO -- modify the default log file storage location <span style = "font-family: Arial, Helvetica, sans-serif; "> (effective immediately) </span> EXEC master .. xp_instance_regwrite @ rootkey = 'HKEY _ LOCAL_MACHINE ', @ key = 'Software \ Microsoft \ MSSQLServer', @ value_name = 'defaultlog', @ type = REG_SZ, @ value = 'e: \ MSSQL_MDF \ log' GO
Which of the following folders stores the database files and log files when you create a new database:
The default value is \ MSSQL \ Data in the installation directory of your SQL server.
Of course, you can point to any other permitted path when you create a database.
Method 1: (Create a database using the Enterprise Manager)
Enterprise Manager -- expand -- database -- Right-click and choose "New Database" --- enter the name of the database you want to create in "general" and switch to "data ""file" --- "and then modify the storage location of your data files in" location "---" and switch to "transaction log" --- "and then modify the storage of your log files in" location" location.
Method 2: (Create a database using commands)
Example:
Create database Sales
ON
(NAME = Sales_dat,
FILENAME = 'C: \ program files \ microsoft SQL server \ mssql \ data \ saledat. mdf ',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON
(NAME = 'sales _ log ',
FILENAME = 'C: \ program files \ microsoft SQL server \ mssql \ data \ salelog. ldf ',
SIZE = 5 MB,
MAXSIZE = 25 MB,
FILEGROWTH = 5 MB)
-- "FILENAME" indicates the path for storing data files or log files. You only need to modify the file according to your needs.
After you have created a database, you only need to use the system table to check the file location of the database:
Use Database Name
Go
Select * from sysfiles
Go
Which one can tell me where the database files and their logs of SQL server 2005 are stored by default?
C: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ DATA