How to Move your SQL Server Database to a new location?

How to move SQL Server to a New Server?

I have been working with SQL Server Databases for many years now, its fun to work with it. Recently I came across a situation where I was running out of space on my C drive on the system where I had my SQL Server Database running.  To resolve the space issue, I thought of moving my Database files from the C drive to another drive say E drive. I did move it and brought it up back and it looks good. Let me explain in steps what you need to do to move your database to a different location.

 

Step 1: Backup your Database

Make sure you have backed up your database, just in case something goes wrong during the moving process.

 

 

Step 2: Identify the files you need to move

Go to the Query window in your SQL Server Management Studio and run this following query to know which files you will need to move.

use YourDatabase

go

exec sp_helpfile

 

The above query will list the database files which you need to move. It will look something like this.

 

 

Step 3: Detach the Database

Next you need to detach the database to be able to move the file to the new location.

Run the following script to detach the database

exec sp_detach_db 'YourDatabase'

 

Step 4: Move the Database File

Next move the database files you identified in Step 2 to the new location.

 

Step 5: Attach the Database to the database files in the new location

Now after moving the database files to the new location you need to attach your database to the new files. You can do that with the following Script.

exec sp_attach_db 'YourDatabase',

'NewPath\\YourDatabase_DataFile.mdf',

'NewPath\\YourDatabase_Logfile.ldf',

Note: The Name of the files will be the one you identified in Step 2 and in this example it will be AdventureWorks_Data.mdf
AdventureWorks_Log.ldf

That’s it; your database should be back up and running from the new location.