SQL University: SQL Admin Part 1 – Database Files and Management

We are going to start our week off by taking a look at where the database files are stored and how to work with them.

Two Types of Files
First, there are two types of files which make up your database:
mydatabasename.mdf -- These are the data files (you may also have .ndf files)
mydatabasename.ldf – These are your transaction log files

Data files store all of the, well, data and metadata associated with your database. Things like data structures, stored procedures, views, etc.

Log files are a transactional history of activity in your database. These should not be confused with other available log files for SQL server operations, however (errors, setup, etc.)

Great – where are these files anyway? There is a lot of information out on the web about determining the architecture for your database storage. Instead of rehashing that here, I will show you where to find existing files.

Default Location
In SQL Server Management Studio (SSMS), right-click on the servername and choose “Properties”. In the left hand navigation bar, click “Database Settings”. Near the bottom, you will see “Database default locations”. There will be a listing for Data files and one for Log files. Most companies have these in separate file directories, if not separate hard drives.

This is where any new database created will store its files by default.

Specific Location
To determine where a specific set of database files is located, right-click on the name of any database, choose “Properties” and then click “Files”. You will need to scroll to the right to see the Path. The File Name will also be shown. Note: there may be more than one .mdf and/or .ldf file for your database.

Now, you can also do this without resorting to drilling through GUI screens. Just use the following command:

select * from sys.master_files

Moving Files
On occasion, there is a need to move your files to another location, or perhaps you need to make sure a database has been taken offline. Using the Sp_detach_db and ATTACH commands are a great tool to have in your arsenal.

Sp_detach_db is used to first disassociate the database from the underlying files. This does NOT delete your database, even though it will no longer show up under Databases in SSMS.

sp_detach_db [ @dbname= ] 'database_name'

Once a database is Detached, you can move the .mdf and .ldf files anywhere you need them. This is also great for copying databases.

Once you have the files in the appropriate locations, use the ATTACH command to re-attach the database:

CREATE DATABASE database_name
ON [ ,...n ]
FOR { ATTACH [ WITH ]
| ATTACH_REBUILD_LOG }

Refer to the MSDN page if you would like to get more information about additional parameters or dealing with replicated, mirrored, or other database scenarios before Detaching. http://msdn.microsoft.com/en-us/library/ms190794.aspx

Of course, SSMS also has these features availabe. Right-click any database and choose “Tasks” then “Detach”.

To Attach the database, right-click on the heading “Databases” and choose “Attach”. You can then browse to the file location by clicking the “Add” button. The appropriate .mdf and .ldf files should appear and then you can click “OK”.

Refreshing your list of Databases will confirm if your Attach was successful.

A Word of Caution
SQL Server comes with a set of System Databases which are used internally by SQL Server to keep track of data and processes necessary to make the engine function. Here is a short description of each for you: http://www.mssqltips.com/tip.asp?tip=1420

If you find you need to move any System Databases (master, model, etc), Microsoft’s detailed instructions can be found here:
http://msdn.microsoft.com/en-us/library/ms345408.aspx

Go back to the SQL University home page.