Wednesday 8 June 2011

Change logical and physical file names of SQL Server database

The easy way of renaming a SQL Server database is to open SQL Server Management Studio, select the database name on the tree on the left handside, hit F2 and type in the new name you want. It’s like renaming a file in a folder. But when you do that, the logical name and physical filenames of the database won’t change. In order to verify this, right click on the database name, select properties. Select Files sction on the left hand side. You can see the logical name and file name for data and log files on the right hand side.
Before attempting to change the names, you should take the database offline. You can do that using SQL Management Studio (right click the database and select Tasks > Take Offiline) or using the following SQL steatement.
ALTER DATABASE database_name SET OFFLINE
In order to change the logical name, execute the following T-SQL statement in a new query window for the database in SQL Server Management Studio. You can use the same statement for both data and log files by changing current_logical_name and new_logical_name.
ALTER DATABASE database_name MODIFY FILE (NAME=“current_logical_name”, NEWNAME=“new_logical_name”)
In order to change the physical file names, execute the following T-SQL statement. You can use the same statement for both data and log files by changing the logical_name and new_file_name.
ALTER DATABASE database name MODIFY FILE (NAME=“logical_name”, FILENAME=“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\new_file_name_you_want.mdf”)
The data and log file location may change depending upon your SQL Server installation.
After executing the SQL statements and before bringing back the database online, go ahead and change the file names for data and log files (.mdf and .ldf) to new_file_name in the file system. Now you are good to go. Go ahead and bring the database online.
You can do that using SQL Management Studio (right click the database and select Tasks > Bring Online) or using the following SQL steatement.
ALTER DATABASE database_name SET ONLINE

0 comments:

Post a Comment