Solved

Moving SQL database to different Drive

Posted on 2014-01-05
10
728 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 27

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:Scott Pletcher
Scott Pletcher 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
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
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:Scott Pletcher
Scott Pletcher earned 286 total points
ID: 39760530
True, since sp_detach_db is still needed for moving dbs to a different server/instance.
0
 

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:Scott Pletcher
Scott Pletcher 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:
Scott Pletcher 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

623 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