change name of file of a database change name of file of a database armm1396 (Systems Engineer) (OP) 15 Nov 17 16:34 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. RE: change name of file of a database bborissov (Programmer) 15 Nov 17 16:35 You can't Borislav Borissov VFP9 SP2, SQL Server RE: change name of file of a database gmmastros (Programmer) 15 Nov 17 21:23 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 RE: change name of file of a database fredericofonseca (IS/IT--Management) 15 Nov 17 21:52 yes you can do it - and easy enough to do. requires following steps set database offlinealter database modify filename to new namephysically rename file (through xp_cmdshell or manually outside tsql)set database onlinedemo example below CODEEXEC 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 www.syssoft-int.com FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions? FAQ181-2886: How can I maximize my chances of getting an answer?