Detach, Attach and Restore the Database with Transact-SQL

Detach, Attach and Restore The Database with Transact-SQL using a test Database. Here i'm using 'MyDataBase' as Test DB.

Prerequisites

• The database must first be detached. Attempting to attach a database that has not been detached will return an error.
• When you attach a database, all data files (MDF and LDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.
• When you attach a database, if MDF and LDF files are located in different directories and one of the paths includes \\?\GlobalRoot, the operation will fail.

Detach The Database

USE master
EXEC sp_detach_db 'MyDataBase', 'true';

Attach The Database

USE master
CREATE DATABASE MyDataBase
ON 
(FILENAME = 'D:\DataBase\MyDataBase.mdf'), -- Location where to store Data File
(FILENAME = 'D:\DataBase\MyDataBase_log.ldf') -- Location where to store Log File
FOR ATTACH;

Restore The DataBase (.Bak File)

USE master
RESTORE DATABASE MyDataBase
FROM DISK = 'D:\DATABASE\MyDataBase'  -- Location where Bak file resides
WITH 
MOVE 'MyDataBase(ThisFashion)_Data' TO 'D:\DataBase\MyDataBase.mdf'  -- Location where to store Data File
MOVE 'MyDataBase(ThisFashion)_Log' TO 'D:\DataBase\MyDataBase_log.ldf'  -- Location where to store Log File