Tuesday, August 10, 2010

SQL Server, changing database filename via T-SQL

This code will change the name of your database of filename using Transact-SQL.  Some of the times you can't use SQL Management Studio but this snippet of code will let you work around that (this uses tempdb as the default db, please change as necessary):

--Determine the logical file
--names for the tempdb database:


USE tempdb
GO

EXEC sp_helpfile
GO


--The logical name for each file
--is contained in the NAME column.
--Change the location of each file
--using ALTER DATABASE.


USE master
GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')

GO


ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')

GO


--Stop and restart SQL Server.

No comments:

Post a Comment