SQL Puzzle | How to move database from one drive to another

Provide t-SQL commands to move database from one drive to another

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

USE Master
GO

ALTER DATABASE [YourDatabaseName] SET OFFLINE
GO 

<<Move the files(mdf&ldf) to your new location.>>

ALTER DATABASE [YourDatabaseName] 
MODIFY FILE (name=YourMdfFileName,filename='YourNewLocation.mdf')
GO

ALTER DATABASE [YourDatabaseName]
MODIFY FILE (NAME = YourlogFileName, FILENAME = 'YourNewLocation.ldf')
GO

ALTER DATABASE [YourDatabaseName] SET ONLINE;
GO

--

Note – You should never detach a db just to change drives on the same instance. Thats an old method and a dangerous one. Use MS article for more details – https://technet.microsoft.com/en-us/library/ms345483.aspx

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com