SqlServer modifies the storage location of database files and log files, and SQL server log files

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