Discussion in 'Getting Started' started by lara99, Apr 14, 2008.
is there a way to rename the physical .mdf and .ldf files
Why do you need that?.
BTW: Yes there is a way.
I am writing an automation process which auto creates databases with the same names.
Would be thankful if you guid me through.
Assuming you are using SQL Server 2005, using Alter Database statement you can achieve this. But better to be cautious on production system.
I am using sql 2k and trying to renmae the .mdf and .ldf .
for example \filepathabc.mdf i was to rename it to \filepathxyz.mdf
do u think this is possible.
No it is no possible at all.
You have to use Alter Database for that.
I think Alter database only works when we are trying to rename the logical files not the physical files right?
Yeap. To change physical names you can dettach and attach or backup and restore with move option.
what you can do is use sp_detach_db and then you can rename or move file.
you can use this method in both sql 2k and sql 2005
and then you can cerate new blanck database
Yes, we can very much change the physical file name as well,
ALTER DATABASE testdb
NAME = test,
Server: Msg 5037, Level 16, State 1, Line 1
MODIFY FILE failed. Do not specify physical name.
I end up getting this error.
In sql 2000 and SQL 2005 you can run following satpes
Detaching the database.
Moving the database files to the other server or disk.
Attaching the database by specifying the new location of the moved file or files.
IN sql 2005 follow these stapes
Run ALTER DATABASE database_name SET OFFLINE.
Move the file to the new location
Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name'.
Run ALTER DATABASE database_name SET ONLINE.
Separate names with a comma.