Moving SQL databases to different disk.

Hi all,

Not a DBA and limited knowledge of SQL. Have a SQL 2012 server that I need to move the databases to another disk. Is this a simple task? Can anyone offer steps to do this?
MattAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Peter HutchisonSenior Network Systems SpecialistCommented:
There two known methods.

a) Detach the database, move the files, and re-attach the database. May lose some DB properties this way (may sure its fully documented first).

b) Take database offline (after killing any connections), move the database files, then change the File properties of the database to new location and then bring it back online again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MattAuthor Commented:
Ok thanks. I think the issue is a little bigger than I thought. The disk that the instance currently resides on has to be recreated. My thought was to move the databases to another disk and then remove the old disk. However the existing disk seems to have SQL program files as well as the databases file.

Would this mean creating a new SQL instance?
0
ste5anSenior DeveloperCommented:
Yup. I'm not aware of a documented way to move an instance with all its files.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nemws1Database AdministratorCommented:
We did this at my company awhile back.  Moving master/msdb/distribution are the issue, but it is still do-able.  Here's some of TSQL, with the DOS commands using FastCopy to copy the database from the old location to the new location.  This uses the detach/re-attach method.

-- Move database 'Blog'
-- Detach the old database
USE [master]
GO
---- If needed: this will kick off any users
-- ALTER DATABASE [Blog] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'Blog'
GO

-------- DOS COPY COMMANDS
/*

echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\Blog.mdf" ^
  /to="D:\Data"
echo %DATE% %TIME%
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "F:\MSSQL10.MSSQLSERVER\MSSQL\Data\Blog_log.ldf" ^
  /to="L:\Logs"
echo %DATE% %TIME%

*/
-------- DOS END

-- Re-attach the database at the new location
USE [master]
GO
CREATE DATABASE [Blog] ON
( FILENAME = N'D:\Data\Blog.mdf' ),
( FILENAME = N'L:\Logs\Blog_log.ldf' )
FOR ATTACH
GO

Open in new window


To update the system files:
-- New Destination: D:\System

--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=
--=--=--=
--=--=--=          *** msdb Files ***
--=--=--=
--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=

USE master
GO
ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBData
    , FILENAME = 'D:\System\MSDBData.mdf'
);
GO
USE master
GO
ALTER DATABASE msdb
    MODIFY FILE ( NAME = MSDBLog
    , FILENAME = 'D:\System\MSDBLog.ldf'
);
GO

--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=
--=--=--=
--=--=--=          *** model Files ***
--=--=--=
--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=

USE master
GO
ALTER DATABASE model
    MODIFY FILE ( NAME = modeldev
    , FILENAME = 'D:\System\model.mdf'
);
GO
USE master
GO
ALTER DATABASE model
    MODIFY FILE ( NAME = modellog
    , FILENAME = 'D:\System\modellog.ldf'
);
GO

--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=
--=--=--=
--=--=--=          *** distribution Files ***
--=--=--=
--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=--=

USE master
GO
ALTER DATABASE distribution
    MODIFY FILE ( NAME = distribution
    , FILENAME = 'D:\System\distribution.MDF'
);
GO
USE master
GO
ALTER DATABASE distribution
    MODIFY FILE ( NAME = distribution_log
    , FILENAME = 'D:\System\distribution.LDF'
);
GO

Open in new window


Update the registry for new default locations:
--
-- You may need to do this in REGEDIT
-- 
USE master
GO

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultData'
    , REG_SZ
    , N'D:\Data'
;

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'DefaultLog'
    , REG_SZ
    , N'L:\Logs'
;

GO

Open in new window


And finally, the master database:

--=--=--=
--=--=--=          *** Master Database ***
--=--=--=
/*

 1) Run the SQL Configuration manager, right click on the database
    and select 'Proprties'
 2) Click on the 'Advanced' tab
 3) Clock to the box to the right of 'Startup Parameters'
 4) Change the -d and -l parameters to (-e for error log):

-dD:\System\master.mdf;-lD:\System\mastlog.ldf;-eD:\System\Log;

The SHUT DOWN the server and run the following DOS commands:

::
:: msdb files
::
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf" ^
  /to="D:\System"
echo %DATE% %TIME%
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf" ^
  /to="D:\System"
echo %DATE% %TIME%
::
:: model files
::
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf" ^
  /to="D:\System"
echo %DATE% %TIME%
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf" ^
  /to="D:\System"
echo %DATE% %TIME%
::
:: distribution files
::
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\Data\distribution.MDF" ^
  /to="D:\System"
echo %DATE% %TIME%
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "F:\MSSQL10.MSSQLSERVER\MSSQL\Data\distribution.LDF" ^
  /to="D:\System"
echo %DATE% %TIME%
::
:: master files
::
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf" ^
  /to="D:\System"
echo %DATE% %TIME%
echo %DATE% %TIME%
start /wait "" "C:\Program Files\FastCopy\FastCopy.exe" ^
  "/auto_close" ^
  "E:\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf" ^
  /to="D:\System"
echo %DATE% %TIME%

*/

Start up the server and check the startup log.

Open in new window

0
ste5anSenior DeveloperCommented:
@nemws1: your solution does not move the instance binaries, which is imho the problem.
0
nemws1Database AdministratorCommented:
Ah, you are correct @ste5an.  That would be tricky.  Possible with a lot of registry hacking, but I wouldn't want to do it.  Better to uninstall and re-install in the new location.
0
MattAuthor Commented:
I've requested that this question be deleted for the following reason:

no solution given
0
Anthony PerkinsCommented:
Reopening the question to allow the author to close the question more appropriately, as this is a clear case of The experts told me “you can not do that”. What do I do now?
0
nemws1Database AdministratorCommented:
My comment, #40241686, answered the author's original question (as did #40241548, although more tersely).  However, it did not address his subsequent issue/clarification.  The only solution is to re-install, as I mentioned in #40241701.  I would split the points among these answers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.