 |
Rename database files both physical and logical, MS SQL SERVER 2005 |
Steps involved in renaming the database:
- Rename the logical files
- Detach the database
- Rename the physical files
- Attach the database as the new database, using the renamed files
Steps to rename the logical files:
a. Open the database properties, and select the “Files” tab from left:
b. Place the cursor in the cell, under the column “Logical names” and type the new name for both MDF and LDF files:
c. Press “ok” to close this screen. The logical files are now renamed.
Steps to detach the database:
a. Right click on the databse, from “Tasks” menu, select “Detach”
b. Press “Ok” on the screen that appears.
Steps to rename the physical files:
a. Check where the files are stored from the “properties”->”Files” tab and rename the files using windows explorer.
Steps to Attach the database as the new database, using the renamed files
a. Issue the command:
sp_attach_db <new_dbname>,<new physical MDF name with path>,<new physical LDF name with path>
That’s it, now check the databases list and you should have the new database ready with new physical file names.
Not necessarily you have to rename the databases, in case you have a mismatch between the database name and the physical file/logical file names, you can use the same steps to get this issue sorted.
If you are facing any major issues with SQL Server, please feel free to contact us: http://www.loginworks.com
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
there is an error occured during detech the database what is the problem?
Error like
TITLE: Microsoft SQL Server Management Studio Express
——————————
Detach database failed for Server ‘AVDHESH-PC\SQLEXPRESS’. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Detach+database+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
——————————
Cannot detach the database ‘avdhesh’ because it is currently in use. (Microsoft SQL Server, Error: 3703)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=3703&LinkId=20476
——————————
BUTTONS:
OK
——————————
Hi Avdesh,
From the error message it looks as if the database is in use while you are trying to detach it. A simple solution would be to restart the database server and then attempt to follow the given steps. However, if there is a limitation on restarting the database you can always go to: activity monitor and kill the connection which is using the database in question. If you need any more details on this, just drop in a comment and I shall write up a step by step process to freeing the database here for you.
Hope this helps.
title=”thanks”>
thanks alot
Desirable element to substance. I just stumbled upon your weblog and in accession capital to assert that I get in simple simple fact cherished account your weblog posts. Any way I will be subscribing for your feeds or even I fulfillment you accessibility continually swiftly.