You may find many pages describing how to move sql server database to another location. But a beginner may wonder what needs to be done with the following commands:
Basically, the above commands are just a set of queries to be run from within a tool like “Microsoft SQL Server Management Studio Express”.
KNOW WHERE THE DATA FILES ARE
In order to move the database, first you will need to know where they are currently stored. This you can find by right clicking on the database in Object Explorer and choosing Properties. In the Properties window you will find the details under the ‘Files’ pane.
Notice the path of your database and open that folder in Windows Explorer so that you can move the data files after detaching the database in the below steps.
DETACHING THE DATABASE
1. Run Microsoft SQL Server Management Studio Express
2. Connect to the required SQL Server
3. Go to File->New and choose “Database Engine Query”
4. Paste the following commands (of course you need to replace the mydb with your database name)
5. Choose ‘Execute’ from the toolbar. That’s it. This will detach the data files from the database.
MOVING THE DATA FILES
Now, move the data files (*.mdf and *.ldf) to a new drive/folder.
ATTACHING THE DATABASE
To attach the files with the database, replace the previous commands with the following commands and execute.
VERIFYING THE DATABASE
To check whether attaching was successful, replace the previous commands with the following commands and execute.
That’s it. Thanks. Hope this post is clear. If not please do let me know.