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

Source: Internet
Author: User
Tags mssqlserver

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.