Move database files

Possible reasons for moving database files include: you accidentally put the log file of a database on a drive you didn’t want to, you just had a hardware failure that you are trying to recover from, or you may just be bored and want to spice up your Friday with some file moves. Doubt there are many takers on that last reason.

Moving the files – Non-Panic Mode
In order to move the files in a Non-panic mode follow theses steps. This will require downtime but at least you are not in panic mode.

1. Verify where the files are located

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'database name');

2. Craft an ALTER DATABASE statement to move the file

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name , FILENAME = 'x:\path\filename.xxx' )

3. Bounce the SQL Service (the change will not take effect until the bounce).

Moving the files – Panic Mode Engage!
So your SQL Server will not start because you are missing some log files.

1. Start the SQL Server in master-only recovery mode

NET START MSSQLSERVER /f /T3608

2. Verify where the files are located

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'database name');

3. Craft an ALTER DATABASE statement to move the file

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name , FILENAME = 'x:\path\filename.xxx' )

4. Stop the service

NET STOP MSSQLSERVER

5. Start the service normally, using Services MMC, SSMS, or Command prompt

6. Verify your files moved where you wanted.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s