XML FeedsUsing ALTER DATABASE and MODIFY FILE you can reset the metadata in the master database for renaming physical mdf and ldf files. This however does not actually change the physical file name itself on disk. To fix that, you are typically required to change the metadata in master and then manually change the physical files on disk after taking the database offline. Then bring the database online and the task is complete.
To get around this manual process you could use CLR with External access set in a DBA database.
Note: DBAs should have a repository that stores all of their functions, procedures and other objects. This keeps your SQL Server clean and is easily created on new servers when they are built with scripts.
The CLR procedure to handle the change can be used off the model code below:
(Since this SQLCLR procedure uses System.IO, it requires External access)
- public partial class StoredProcedures
- {
- [Microsoft.SqlServer.Server.SqlProcedure]
- public static void ChangeFileName(string oldmdf, string oldldf, string newmdf, string newldf)
- {
- try
- {
- SqlContext.Pipe.Send(oldmdf);
- SqlContext.Pipe.Send(oldldf);
- if (File.Exists(oldmdf))
- {
- File.Move(oldmdf, newmdf);
- SqlContext.Pipe.Send("File moved");
- }
- if (File.Exists(oldldf))
- {
- File.Move(oldldf, newldf);
- SqlContext.Pipe.Send("File moved");
- }
- }
- catch (SqlException ex)
- {
- SqlContext.Pipe.Send(ex.ToString());
- }
- }
- };
Then to follow this you can run the following T-SQL Statement to execute the change to master and also change the physical file names
- USE MASTER
- Go
- DECLARE @MDF NVARCHAR(1000)
- DECLARE @LDF NVARCHAR(1000)
- DECLARE @NEWMDF NVARCHAR(1000)
- DECLARE @NEWLDF NVARCHAR(1000)
- DECLARE @CMD NVARCHAR(2000)
- SET @NEWMDF = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf'
- SET @NEWLDF = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Log.ldf'
- SET @MDF = (SELECT physical_name FROM SYS.MASTER_FILES WHERE database_id = DB_ID('AdventureWorks') AND type_desc = N'ROWS')
- SET @LDF = (SELECT physical_name FROM SYS.MASTER_FILES WHERE database_id = DB_ID('AdventureWorks') AND type_desc = N'LOG')
- ALTER DATABASE AdventureWorks SET OFFLINE
- EXEC master.dbo.ChangeFileName @MDF,@LDF,@NEWMDF,@NEWLDF
- SET @CMD = ('ALTER DATABASE AdventureWorks
- MODIFY FILE
- ( NAME = N''AdventureWorks_Data'',
- FILENAME = ''' + @NEWMDF + ''')')
- EXEC(@CMD)
- SET @CMD = ('ALTER DATABASE AdventureWorks
- MODIFY FILE
- ( NAME = N''AdventureWorks_Log'' ,
- FILENAME = ''' + @NEWLDF + ''')')
- EXEC(@CMD)
- ALTER DATABASE AdventureWorks SET ONLINE
This is only valid for a database that has one mdf and one ldf. To alter this to handle either multiple ldf or added ndf file, change the CLR procedure to simply change one at a time. Alternately you could add in logic by passing the entire contents of the database sys.master_files and then based on the rows sent as the parameter, change accordingly. Use this method as a guide to build it to your own flavor :-)
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.