Sometimes we need on SQL Server move database files to another drive. In this article we will try to explain 3 easy methods to perform this task. The first method explains moving of database files using SQL Server Management Studio Interface. Going step by step without any script. The other two methods are based on script. The first uses the method of “ALTER DATABASE”. The second uses “Detach/Atach” method.

Why will need to move sql databases on another drive?

First scenario: All your databases ate located on D: partition and partition is running out of the space. You are going to create new Partition E: and need to allocate some of data and log files to the new partition.

Second Scenario: Database creator has used some T-SQL Script to create databases. T-SQL script created database on default location that was specified on SQL Server Instance and most likely is on C:. Microsoft recommended to use different partition from where OS is installed for better performance. Also different partition for lof file (LDF) and data file (MDF).

You can learn also How to disable all sql jobs at once.

Sql server move database files to another drive

• Using SQL Server Management Studio Interface to move database files.
• Script to move database files with “ALTER DATABASE” method.
• Moving database files with “Detach/Atach” method using T-SQL Script

Using SQL Server Management Studio Interface to move database files.

Before you can start with sql server move database files to other location need to be checked:

• Please perform a Full Backup before you move any database.
• Login User need sysadmin server-role to do the following changes.
• The default database of login user different from the database that will be moved.

Locate the path of the data and log files that will be moved.

1. You can use database properties to identify the path:

Database name – Properties – Files (Under the “path” is Path of files)

2. Or use the following T-SQL script:

SELECT name, physical_name AS current_file_location

FROM sys.master_files

where Name in ('test','test_log')

Change the name of the database or remove “where Name in (‘test’,’test_log’)” to get all database files path.

The default located of data and log files is:

C:\Program Files\Microsoft SQL Server\MSSQL11.SGALDW\MSSQL\DATA\Test.mdf

Starting the move of the database:

Go to SQL Server Management Studio and find the database that you will move data and log files.

Right Click – Task – Detach

The following view will appear:

Need to check Drop Connection box to be sure that we didn’t have any connection during detach the database. In default Detach command will not perform if any connection exist. After click OK the database will be detached and will disappear from databases view on Management Studio.

Go to the directory where database files stored. Cut and paste to the new destination folder on different drive. Like the example below:

After the copy finished go back to Management Studio and Right click to the database and after that click Attach…

Find the data file on the new destination and after add the data will be filled automatically.

Click Ok and the new database will appear on Management Studio. Do some select from the tables to be sure that everything is ok.

Sql Server move database files with “Alter Database” method.

You can use T-SQL commands to move data and log file of the sql database in another drive.

Please follow steps to move database to a new location.

1. Find the path of the data and log file explain previously:

Use master

SELECT name, physical_name AS current_file_location

FROM sys.master_files

where Name in ('test','test_log')

2. Change the database you want to move to offline with the command:

ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE;

Note! To kick out everyone and rollback all currently open transactions need to use “WITH ROLLBACK IMMEDIATE”

3. Move files to the new location with the following command:

-- ms sql move database to another drive
ALTER DATABASE test MODIFY FILE ( NAME = test, FILENAME = " C:\Program Files\Microsoft SQL Server\MSSQL11.SGALDW\MSSQL\DATA\Test.mdf") GO

ALTER DATABASE test MODIFY FILE ( NAME = test_log, FILENAME = " C:\Program Files\Microsoft SQL Server\MSSQL11.SGALDW\MSSQL\DATA\Test_log.ldf") GO

4. Change the database status to Online:

ALTER DATABASE test SET online GO

In fact this method its fastest that using Management Studio interface.

SQL Server move database files with Detach/Attach method using T-SQL

The last method use Detach/Attach to move SQL server database file. We are going to explain it step by step.

Our goal Is to move database files from Source “C:\Program Files\Microsoft SQL Server\MSSQL11.SGALDW\MSSQL\DATA\Test.mdf” to destination “D:\New_Destination\”

1. Detach databse command

-- move sql database to another drive
USE MASTER;
GO
-- Take target database in single user mode.
-- This may disconnect your active transactions for target database
ALTER DATABASE test
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach targetDB
EXEC MASTER.dbo.sp_detach_db @dbname = N'test'
GO

2. In this step move the data and log files of target database from source folder to the new destination.

3. Attach the target database with the new location folder

-- Sql server Move data and log files from source to the new destination folder
-- Re-Attached DB
CREATE DATABASE [test] ON
( FILENAME = N'D:\New_Destination\test.mdf' ),
( FILENAME = N'D:\New_Destination\test_log.ldf' )
FOR ATTACH
GO

4. After this the database will attached and you can check on Management Studio Databases. Do some select from the tables to be sure that everything is ok.

Note! Execute command “ROLLBACK IMMEDIATE” only if you are sure that not going to disconnect any important connection.

The post Sql server move database files to another drive in fastest way – data and log appeared first on Get IT Solutions.