database files movement from one drive to another drive in sql server

1) Capture the .mdf and ldf file names of that particular datbase to which you want to move 
   SELECT * FROM  sys.master_files
2) Ensure that you have latest .BAK file available for rollback plan
3) Take that particular database alone into OFFLINE
   ALTER DATABASE database_name
   SET OFFLINE
4) Move the files to new location
5) Run the below command make changes at system catlogue level

USE master
GO
ALTER DATABASE AdventureWorks2012   
MODIFY FILE( NAME = AdventureWorks2012_Log,   
            FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf');  
GO
ALTER DATABASE AdventureWorks2012   
MODIFY FILE ( NAME = AdventureWorks2012_Data,   
                  FILENAME = 'C:\NewLoc\AdventureWorks2012_data.mdf'); 
6) Bring the database ONLINE
ALTER DATABASE database_name
SET ONLINE
7)Once the database came online run the below commmand to chek whether the new path appearing at catlogue level

SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'<database_name>'); 

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server