change name of file of a database 2

[armm1396]

hello
by ALTER DATABASE Modify Name, we can change name of a database. but the name of
phisical file don't be renamed. how could it be do?
thanks.

 

[bborissov]

You can't

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

You can't do this easily, but it can be done.

This will be a multi-step process, and could possibly take a long time to run depending on the size of your database.

1. Backup your database.
2. Drop your database.
3. Restore the database using the "With Move" option.

You can skip step #2 if you want to change the database name (as you refer to it in tSQL).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

yes you can do it - and easy enough to do.

requires following steps
[ol 1]
[li]set database offline[/li]
[li]alter database modify filename to new name[/li]
[li]physically rename file (through xp_cmdshell or manually outside tsql)[/li]
[li]set database online[/li]
[/ol]

demo example below

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
go
RECONFIGURE;  
GO  

CREATE DATABASE [demo_rename]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'demo_rename', FILENAME = N'C:\sql_server_data\MSSQL12.SQL2014\MSSQL\DATA\demo_rename.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'demo_rename_log', FILENAME = N'C:\sql_server_data\MSSQL12.SQL2014\MSSQL\DATA\demo_rename_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [demo_rename] SET  MULTI_USER 
GO
select name, mf.physical_name
from sys.master_files mf
where db_name(mf.database_id) = 'demo_rename'

alter database demo_rename set single_user with rollback immediate
alter database demo_rename set offline

alter database demo_rename modify file (name=N'demo_rename', filename=N'C:\sql_server_data\MSSQL12.SQL2014\MSSQL\DATA\demo_newname.mdf')
exec sys.xp_cmdshell 'move C:\sql_server_data\MSSQL12.SQL2014\MSSQL\DATA\demo_rename.mdf C:\sql_server_data\MSSQL12.SQL2014\MSSQL\DATA\demo_newname.mdf'

alter database demo_rename set online


select name, mf.physical_name
from sys.master_files mf
where db_name(mf.database_id) = 'demo_rename'

EXEC sp_configure 'xp_cmdshell', 0;  
go
RECONFIGURE;  
GO  
drop database demo_rename

the physical renaming of the datafiles can be done either outside or through a xp_cmdshell command to rename them.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886