SQL Server – Move database using sp_detach_db and sp_attach_db

This article will help you to understand different options for moving database files (i.e. mdf and ldf ) from one to another location and sp_detach_db and sp_attach_db statement help you to move these files. Here will give you an idea that are available with SQL Server 2005 and step by step approach for moving database files using SQL Server Management Studio also best practice with some tips and tricks for this activity.

As per best practice, you should not keep data and log files on the same drive that will degrade IO performance of sql server. If you have both the files on the same drive, its time to move database file. SQL server provides many options to move files from one drive or disk to another.

Even we can move the files from one server to another server with the help of sql server commands.

How to move database file?

Steps for moving files in sql server

  1. Using sp_detach_db and sp_attach_db
  2. ALTER DATABASE
  3. Backup the database, drop it, and restore it using the WITH MOVE option

Move database file using sp_detach_db and sp_attach_db

This is the fastest option in SQL server to move big database files from one to another location, even another server. Let’s go through each step in details

  1. First thing you need to do is collect all information related to database. Here is the query that will give you the information related to database owner, database size, and date of creation also status of the database with compatibility level

sp_helpdb ‘testdb’

  1. Also give you the file name and location of the database testdb1 with size and growth percentage.
  2. Execute the sp_detach_db command to detach database from sql server

sp_detach_db ‘testdb’

  1. Stop the sql server services to copy the physical files (mdf and ldf) to another location, in my case we are copying the data file (mdf) in D: drive and log file in E:\ drive.
  2. After copying files into another drive, start the sql services and execute the below sp_attach_db command to attach the database with new file location

sp_attach_db ‘testdb’,’D:\testdb_data.mdf’,’E:\testdb_log.ldf’

Also ALTER DATABASE is another option available in sql server

 

Leave a Reply

  

  

  


8 + 9 =