Below are the steps, which I usually follow as part of any database file movement activity.
- Check the last full database backup completed successfully. If your change windows allows, prefer to take a latest full backup of the database. If the database is not in simple recovery model, initiate a transaction log backup as well.
- Make sure there is no connections to the database. If there is an existing connection to the database from the Application, reach out the Application team to stop there application. Since these activities are mostly performed during a planned change window, you should ask your application’s contact to stop the application. I don’t prefer to kill the connections by myself, I rather asked to stop the application.
- Take a note of the database files, which you are planning to move. For example, to know the details about the data file & transaction log file for the database Northwind, execute the below command:
- Once you have the details about the database files location, take the database offline by executing the below command:
- I had faced few challenges while moving the databases hosted on a SQL Server 2005 instance. As a standard process, once the database is offline, I grant full access to the windows local administrators group on the data & transaction log file for the database.
- On the newer drive, make sure we have the required folder structures in place. The SQL Server Service account should have read & write permissions on the folders, where the data & transaction log file should reside. The SQL Agent service account should have read permission on the files.
- Modify the location of the data & transaction log file in the system catalog of the SQL Server, by executing the below command:
- Now that we have modified the file location in the system catalog, time to move the actual physical files. I prefer to use ROBOCOPY.EXE to move the files from one drive/mount point to another. The command to copy one file from one location to another is:
- Once all the files are copied to the new location, time to bring the database online, by running the below command:
- Congratulations!! The database is online from the new location. Now time to remove the additional permission, which we have added in step-5. Remove the permission granted to the windows local administrator earlier from the physical database files.
- Inform your Application user to test their application. Once they confirm that the application is working as expected, initiate a full database backup.
This complete the steps requires to move a database file. Added the script used in this post.
-- to check location of the data file use [master]; go exec sp_helpdb 'Northwind'; go -- to take the database offline use [master]; go alter database [Northwind] set offline; go -- to modify the location of data and tlog file in the system catalog use [master]; go alter database [Northwind] modify file(name = 'Northwind', filename = 'C:\UserDB\SQL2012\Data\Northwnd.mdf'); go alter database [Northwind] Modify file(name = 'Northwind', filename = 'C:\UserDB\SQL2012\TLog\Northwnd.ldf'); go -- Once the files are copied using Robocopy, use the below command to bring the database online use [master]; go alter database [Northwind] set online; go
Hope, this will help someone, Happy Learning 🙂