Tuesday, May 6, 2008

Move Database

USE [master]
GO   
 
-- Take the database offline 
 
ALTER DATABASE [myDatabase] SET OFFLINE;
GO
 
 
-- Move all files to new path
 
-- Then run statements below
 

ALTER DATABASE [myDatabase] 
MODIFY FILE (NAME = N'myDataPrimary', FILENAME = N'D:\Data\mydatabase.mdf')
GO

ALTER DATABASE [myDatabase] 
MODIFY FILE ( NAME = N'myDataSecondary', FILENAME = N'D:\Data\mydatabase2.mdf')
GO

ALTER DATABASE [myDatabase] 
MODIFY FILE ( NAME = N'myLog', FILENAME = N'E:\Logs\mydatabase.ldf')
GO

 
-- Then Put database online again...
 
ALTER DATABASE [myDatabase] SET ONLINE;
GO


No comments: