AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Thursday, 5 November 2015

How to move a database from one drive to another in SQL Server?

In this article i will discuss how to move a database from one drive to another in SQL Server.

Detach the Database:

use master
go 
sp_detach_db 'mydb'
Move the Database files (Xcopy through xp_cmdshell shown):

DECLARE @SRCData nvarchar(1000)
SET @SRCData = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb.mdf';
DECLARE @SRCLog nvarchar(1000)
SET @SRCLog = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mydb_log.ldf';
DECLARE @FILEPATH nvarchar(1000);
DECLARE @LOGPATH nvarchar(1000);
SET @FILEPATH = N'xcopy /Y ' + @SRCData + N' E:\Data';
SET @LOGPATH = N'xcopy /Y ' + @SRCLog + N' F:\Log';
exec xp_cmdshell @FILEPATH;
exec xp_cmdshell @LOGPATH;

ReAttach Database:

sp_attach_db 'mydb', 'E:\Data\mydb.mdf', 'F:\Log\mydb_log.ldf'

0 comments :

Post a comment

  • Popular Posts
  • Comments