Rename database data file in SQL Server 2005

I need to rename a databases data file. Is this possible through SQL Server Management Studio?

FYI, I do not have permissions to the underlying box.

Edi开发者_如何学Pythont: I also need to change the location of the file.

Yes, you can do this, as long as you have the right to detach and re-attach the database, and as long as you find a way to physically rename the files on disk:

1) issues these commands

ALTER DATABASE yourdatabase
MODIFY FILE (NAME = logical_file_name, FILENAME = 'your-new-file-on-disk.mdf' )

(as DGGenuine pointed out in a comment: the 'your-new-file-on-disk.mdf' must be a full file name - including path - on your disk)

2) detach the database

3) rename the files on disk

4) re-attach the database again

1. Take a full backup of the database.
2. Drop it.
3. Restore it, specifying different file names and paths in the restore dialog.

You can do this with no permissions on the underlying file system, because you are not moving a physical file around, you are asking SQL Server to generate a new file on your behalf and copy data into it from the backup.

To set the new filenames and paths, go to the Options tab of the Restore dialog. You even get a folder browse dialog that shows you SQL Server's view of the file system, not yours.

Minimum permissions required for this procedure:

• db_backupoperator role in the database
• dbcreator role in the server

Try:

ALTER DATABASE <DBName> 
MODIFY FILE (NAME = logical_file_name, FILENAME = 'new-file-on-disk.mdf' )

This will only alter SQL Server's internal definition of the filename, it will not change the actual name of the file in the OS file system.

you can use sp_detach_db to take the database offline. find the files and rename them, move them whatever. then use sp_attach_db to reattach them from a new location. that's how i do it anyway.

best regards, don

Yes and No. You can change the database file object filename:

ALTER DATABASE <dbname> MODIFY FILE (NAME=<logicalname>, FILENAME=<newfilename>);

This will update the master catalog so that at first next database open event the new filename will be looked up. But there is no direct way to rename/move the file in Transact-SQL.

You can though use xp_cmdshell to rename the file, or you can deploy a CLR assembly with EXTERNAL_ACCESS enabled that can do the file rename/move operation.

Run this in SSMS in text mode to get a T-SQL script and DOS commands that will rename all your .ndf files. It assumes you want your files named the same as your filegroups minus a 'FG_' prefix that I like to use on my filegroups.

Cheers.

/**********************************************************************


SCRIPT NAME:        Rename FileNames 02.sql

PURPOSE:            Rename multiple filenames on the database. 

Change History:

    03/19/2010 4:15 PM - gmilner: Created. 

**********************************************************************/

SET NOCOUNT ON

DECLARE @DATABASE_NAME VARCHAR(64) 
SET @DATABASE_NAME = 'YOUR_DATABASE_NAME_HERE' 

PRINT '----------------------------------------------------------------------------'
PRINT '                 RUN THESE IN A SEPARATE SSMS WINDOW' 
PRINT '----------------------------------------------------------------------------'

-- create the ALTER DATABASE files to change the file names in the sys

/* 
    NOTE: "This will only alter SQL Server's internal definition of the filename, 
        it will not change the actual name of the file in the OS file system." 
*/

SELECT 
    'ALTER DATABASE '+ @DATABASE_NAME +  CHAR(13) + 
    '  MODIFY FILE (NAME = ' + [name] + ', ' +  CHAR(13) + '  FILENAME = ''' + 
    /* the line below strips the path only from the whole (old) filename
        so the new files are mapped to the same place as the old /
    SUBSTRING(physical_name,1,PATINDEX('%'+REVERSE(SUBSTRING(REVERSE(physical_name),1,CHARINDEX('\',REVERSE(physical_name))-1))+'%' ,physical_name)-1)  + 
    @DATABASE_NAME + '_' +

    REPLACE([name],'FG_','') + '.ndf'');'   / NOTE: all filegroups start with 'FG' but files should not. /
    + CHAR(13)   / put in a blank line between each command */
FROM sys.master_files
WHERE database_id = DB_ID(@DATABASE_NAME)
 AND physical_name LIKE '%.ndf';

-- Now we need  DOS BATCH commandS to rename the actual files.
-- We run them as Administrator on the box itself

PRINT '----------------------------------------------------------------------------'
PRINT '                 NOW, DETACH THE DATABASE AND THEN ... ' 
PRINT '                 RUN THESE IN A DOS BATCH AS ADMINISTRATOR' 
PRINT '----------------------------------------------------------------------------'

SELECT 'rename "' + physical_name + 
    '" "' + 
    @DATABASE_NAME + '' +

    REPLACE([name],'FG','') + '.ndf";'   /* NOTE: all filegroups start with 'FG' but files should not. */

FROM sys.master_files
WHERE database_id = DB_ID(@DATABASE_NAME)
 AND physical_name LIKE '%.ndf';

PRINT '----------------------------------------------------------------------------'
PRINT '              AFTER THE DOS BATCH IS RUN, REATTACH THE DATABASE' 
PRINT '----------------------------------------------------------------------------'

SET NOCOUNT OFF 

Be careful when you use Rename option from context menu that appear when you right click on the database in Management Studio. This option does not change the database file names. To change logical filenames for DATA and LOG files you can also use a Management Studio interface but unfortunately sometimes it does not work.

Let’s do it properly... it should works always.

1. Detach database: Using Management Studio, right-click on database > go to “Tasks” > “Detach”, click OK to detach a database (note: that the DB can not be used to detach it)
2. Rename Physical files: Once the database is detached the physical files are unlocked and you can rename them using Windows Explorer:

   

3. Attaching database with New Name: For it use T SQL:

   USE [master] CREATE DATABASE [SqlAndMe] ON ( FILENAME = N’C:\…\NewName.mdf’), ( FILENAME = N’C:\…\NewName_log.LDF’) FOR ATTACH

4. Rename Logical file names: Execute this T SQL:

   USE [NewName] ALTER DATABASE [NewName] MODIFY FILE (NAME=N’OldName’, NEWNAME=N’NewName’) ALTER DATABASE [NewName] MODIFY FILE (NAME=N’OldName_log’, NEWNAME=N’NewName_log’) SELECT name, physical_name FROM [NewName].sys.database_files

It should works!

Here is some example code for the answer of marc_s:

After renaming my database Test, to TestSimple, I wanted to rename its files. The following worked for the log file. (For the main file, the NAME = part became Test, and I substituted the path I wanted for that file.)

USE [master]
ALTER DATABASE [TestSimple] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [TestSimple]
MODIFY FILE (NAME = Test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\TestSimple_log.ldf' )
GO
ALTER DATABASE TestSimple SET MULTI_USER
GO

The above changed what files SSMS looks for, but it does not actually rename the files. So I had to do that via Windows.

After we rename the database， if we want to change data and logfile name also, we could use following step:

1.

USE master;  
GO  
ALTER DATABASE TESTDB
Modify Name = Northwind ;  
GO 

2.

ALTER DATABASE Northwind SET OFFLINE; 

ALTER DATABASE Northwind MODIFY FILE ( NAME = TESTDB, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Northwind.mdf' );
ALTER DATABASE Northwind MODIFY FILE ( NAME = TESTDB_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Northwind_log.ldf' );

3.

Change filename in windows server

4.

ALTER DATABASE Northwind SET ONLINE;