Just Learned - Change location of database file + correct logical file name of the database file

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

Change location of database file + correct logical file name of the database file

Mar 2 2012 12:00AM by vanne040

I was verifying a server setup and found that the tempdb files were residing in the root location created during initial sql server installation. As a part of good practices I always separate out the tempdb files to a separate drive. To achieve this I had to use to the below sql.

use master
go

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go

Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go

In general, To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

For user databases, before using the sql above, you need to move the database files to the required location. Sql above does not actually move the database files. It just updates the system catalog.

When the sql is executed successfully. you should see the messages below,

The file "dev1" has been modified in the system catalog. The new path will be used the next time the database is started.

The file "dev1_log" has been modified in the system catalog. The new path will be used the next time the database is started.

In order to connect to the database successfully, restart the sql server service.

Later, I had to restore a dev database to a staging server and wanted to keep the logical file names consistent. Dev database had the logical name dev1_aw and dev_aw_log for the mdf and ldf files respectively.

I used the below sql to change the logical name after the restore.

ALTER DATABASE aw_qa MODIFY FILE(NAME = dev1_aw, NEWNAME = qa_aw) 
ALTER DATABASE aw_qa MODIFY FILE(NAME = dev1_aw_log, NEWNAME = qa_aw_log)

To modify the logical name of a data file or log file, specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. For example:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

2 Comments

Hi vanne,

I run the code to Modify File of database, but Only database path has changed. Files(.mdf and .ldf) did not move to the new path specified in the command.

Now Whenever i am running any command on my database, I am recieving this error msg:

Msg 945, Level 14, State 2, Line 1 Database 'MYTEST' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

commented on Mar 2 2012 11:50PM

Ashish Jain

440 · 0% · 88

Thanks for pointing out Ashish. For user databases, we need to use the below syntax after moving the files to the required location.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

This will update the system catalog of SQL Server. In order to resolve your error, please perform the following steps.

Move the database files to the directory you specified in sql code.

After you move the files, restart the sql server service.

After the restart you should be able to connect to database.

commented on Mar 5 2012 9:27AM

vanne040

83 · 2% · 657

Your Comment

Sign Up or Login to post a comment.

"Change location of database file + correct logical file name of the database file" rated 5 out of 5 by 8 readers