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.
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',
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
That’s it; your database should be back up and running from the new location.