How to transfer Database MDF and LDF files

We can easily use SQL Server to create a database instance that will be stored in the specified default location (not necessarily the C drive, where the default storage location can be changed manually). Suppose the DB instance is created in the default location in the C drive, which is consistent with the location of the database installation, and is stored in the C:\Program Files\Microsoft SQL Server\mssql11 for 64-bit operating systems. The Mssqlserver\mssql\data. For a variety of reasons, our code persistence in the write database, the database LDF file becomes larger and smaller, and the space of the C disk is more and more small, at this time, our first thought is to thin the database, thin body is very simple, right-click the database name Select Tasks----Shrink, as shown in:

After slimming, we will see that the C-disk space is actually released part of the size of the release depends on the situation. Sometimes, we also consider transferring the database files to other disks to solve the C-disk space problem, but how do we do it? It's really simple!

Now suppose that we need to transfer the database file from the C-Drive DB instance (pubs) default path to the D drive in the specified folder Loc1, the first step, open SQL Server, create a new query file, and enter the following code:

 UseMASTER;GO--Take the database in single user mode--if you are facing errors --This could terminate your active transactions for databaseALTER DATABASEpubsSETSingle_user with ROLLBACKIMMEDIATE;GO--Detach DBEXECMASTER.dbo.sp_detach_db@dbname =N'pubs'GO

View Code

Executing the above code, you will see the following message:

At this point, you will find the original path of the MDF and the LDF file before a small lock:

The second step, copy the database MDF and LDF two files to the destination folder you want to transfer, here is D:\loc1:

In the third step, go back to SQL Server and enter the following code:

CREATE DATABASE  on  = n'D:\loc1\pubs.mdf'= n'D:\loc1\ Pubs_log.ldf'  ) for ATTACHGO

View Code

Execute the code and you will see a prompt message: Command (s) completed successfully.

Here, the database transfer is complete, at this time, you can safely delete the relevant data files in the C drive.

Here's a small reminder: You can use rollback immediate to instantly terminate your activity, but don't let it execute randomly, unless you know for sure that they no longer need it or if it's been reviewed for any reason that the connection to the database could not be terminated manually.

How to transfer Database MDF and LDF files