Solved

Moving SQL database to different Drive

Posted on 2014-01-05
10
643 Views
Last Modified: 2014-01-31
I have installed MSSQL server  in C: drive, I realized I am low in space, and need to move it to E: drive.
I believe SQLserver application does not grow up but the databases do. So I need to move the databases to E: drive?

any help to moving databases will be very much appreciated.

Thanks
0
Comment
Question by:jskfan
10 Comments
 
LVL 18

Assisted Solution

by:Netflo
Netflo earned 71 total points
Comment Utility
Step by step instructions using SQL Server Management Studio: http://www.katieandemil.com/sql-server-move-database-to-a-different-drive
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 71 total points
Comment Utility
Steps:

1. Detach the databases
2. Move the files (.mdb and .log) on the new location
3. Attach back the databases using the files from the new location

How to detach/attach database:

http://www.youtube.com/watch?v=T4VWrsISSDg
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 286 total points
Comment Utility
1) I suggest never using the GUI to do this type of thing.
2) Don't detach a db just to move it to another drive; detach/attach is a deprecated procedure.

Instead:

A) Modify the system catalog to contain the new locations of the files.
ALTER DATABASE [db_to_be_moved] MODIFY ( FILE = <logical_file_name>, FILENAME = 'd:\full\path\to\new\file\db_to_be_moved.mdf' )
--ALTER ... for every data file
ALTER DATABASE [db_to_be_moved] MODIFY ( FILE = <logical_file_name>, FILENAME = 'd:\full\path\to\new\file\db_to_be_moved_log.ldf' )

B) Verify the new locations of the db files in the sys catalog BEFORE continuing:
SELECT physical_name, * FROM sys.master_files WHERE database_id = DB_ID('db_to_be_moved')

C) Set the db offline:
ALTER DATABASE db_to_be_moved SET OFFLINE

D) Physically copy all the files to the new location(s) (or move them, but with less fallback if something goes wrong)

E) Set the db online:
ALTER DATABASE db_to_be_moved SET ONLINE

F) Verify that the new files are in use:
SELECT physical_name, * FROM db_to_be_moved.sys.database_files

G) Use the db for a while.

H) Delete the original files.
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 72 total points
Comment Utility
I do agree with Scott, don't just move the files, copy them.  Also write out the T-SQL to do this.

I  just wanted to make one slight amendment, sp_attach_db is deprecated (sp_detach_db is alive and well), but you can use create database with the the for attach clause (which is why sp_attach_db has been deprecated).  There's really no advantage whatsoever in using the detach/attach instead of the alter database, though - just wanted to be a stickler for details (hopefully Scott doesn't hate me!).
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 286 total points
Comment Utility
True, since sp_detach_db is still needed for moving dbs to a different server/instance.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jskfan
Comment Utility
ScottPletcher

Can you give an example, please... especially on A and B bullets

Thanks
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 286 total points
Comment Utility
I coded specific examples with them, since they are rather obscure actions :-) .

Usually when the db won't backup, it means a system table has been corrupted, often an allocation table.  That's usually the result of an I/O error, which of course makes sense here since you had a drive failure.

You most likely will end up having to copy data out table by table, although you can generate code to make it less tedious.

Do NOT detach a damaged db, as it will almost certain not re-attach.
0
 

Author Comment

by:jskfan
Comment Utility
I meant the syntax:
ALTER DATABASE [db_to_be_moved] MODIFY ( FILE = <logical_file_name>, FILENAME = 'd:\full\path\to\new\file\db_to_be_moved.mdf' )


I have the following databases to move to D: partition:
OperationsManager
OperationsManagerDW
ReportServer
ReportServerTempDB
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 286 total points
Comment Utility
Run the following commands and post the results.  Also, the drive and path you want to move the files to.

USE OperationsManager
PRINT DB_NAME()
EXEC sp_helpfile

USE OperationsManagerDW
PRINT DB_NAME()
EXEC sp_helpfile

USE ReportServer
PRINT DB_NAME()
EXEC sp_helpfile

USE ReportServerTempDB
PRINT DB_NAME()
EXEC sp_helpfile
0
 

Author Closing Comment

by:jskfan
Comment Utility
I will try it Thank you
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now