[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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?
0
Matt
Asked:
Matt
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nemws1Commented:
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
 
nemws1Commented:
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
 
nemws1Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now