Move database files

In a nutshell, the three main steps involved in moving a database file to new location are:

1. Set database to single user mode.
2. Detach the database.
3. Move database file to new location.
4. Reattach the database by referencing the new location of the database file.
5. Set database to multiple user mode.

Note: There are 2 ways to move database files.

1. via SSMS GUI
2. via SQL Scripts

via SSMS GUI

The way that I prefer to move a database that is already existing on a system, is to right click, go to tasks, and take my database offline first. Choosing okay will take my database offline and it will halt any current connections and prevent any new connections from taking place on that database.

Now that our database has actually been taken offline, we can perform another task which is known as detach. So when we detach a database, essentially what it does is it removes it from the SQL Server instance, so we no longer see it listed in SQL Server.

Now what we’re going to do is we’re going to step out and we’re going to move the database to a different folder. I’ve created one called data on my D Drive, and I’m going to select my log file and data file, and now we’re going to move the data and log file over to the new data drive. 

Now that the files are here, they’re no longer in our original directory, which was on the system drive, so we will minimize these and come back into SQL Server, right click the database’s folder, choose attach. Under the databases to attach, we need to click the add button, and we need to go and find our data on the D Drive. So again, we’ve put it in the D data folder.

via SQL Script

-- 1.  Drop all connection from database.
ALTER DATABASE MyDb01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
*/

-- 2.  Detach Database.
EXEC sp_detach_db 'MyDb01', 'true';  

/*
Commands completed successfully.
*/

-- 3.  Copy or Move database files to new drive.

-- 4.  Re-attach Database.
CREATE DATABASE MyDb01 ON   
		(FILENAME = 'X:\MSSQL\MyDb01\Data\MyDb01.mdf'),   
		(FILENAME = 'L:\MSSQL\MyDb01\Log\MyDb01_log.ldf')   
    FOR ATTACH;  

/*
Commands completed successfully.
*/

-- 5.  Restore connection to users.
ALTER DATABASE MyDb01 SET MULTI_USER

/*
Commands completed successfully.
*/

Multiple Files:

CREATE DATABASE MyDB01 ON   
		(FILENAME = 'X:\MSSQL\MyDB01\Data\MyDB01.mdf'),   
		(FILENAME = 'X:\MSSQL\MyDB01\Data\MyDB01_02.ndf'),   
		(FILENAME = 'X:\MSSQL\MyDB01\Data\MyDB01_03.ndf'),  	
		(FILENAME = 'Y:\MSSQL\MyDB01\Index\MyDB01_INDEXES.ndf'),   		
		(FILENAME = 'Y:\MSSQL\MyDB01\Index\MyDB01_02_INDEXES.ndf'),   		
		(FILENAME = 'L:\MSSQL\MyDB01\Log\MyDB01_LOG.ldf')   
    FOR ATTACH;  

Move Database File via Script and GUI

-- 1.  Set database to single user mode
ALTER DATABASE [MyDB01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- 2.  Deattach database
EXEC sp_detach_db 'MyDB01', 'true';  

-- 3.  Move database file to new location

-- 4.  Reattach database files via GUI

-- 5.  Set database to multiple user mode
ALTER DATABASE [MyDB01]  SET MULTI_USER

Sources:

https://www.linkedin.com/learning/microsoft-sql-server-2016-installation-and-administration/moving-database-files