Solved

Moving SQL database to different Drive

Posted on 2014-01-05
10
653 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
ID: 39757967
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
ID: 39759436
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
ID: 39760276
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
ID: 39760449
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
ID: 39760530
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
ID: 39766842
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
ID: 39768192
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
ID: 39784142
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
ID: 39785585
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
ID: 39824034
I will try it Thank you
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

17 Experts available now in Live!

Get 1:1 Help Now