Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Moving SQL database to different Drive

Posted on 2014-01-05
10
Medium Priority
?
754 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 284 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 284 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1144 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
Independent Software Vendors: 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!

 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 288 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1144 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1144 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 70

Accepted Solution

by:
Scott Pletcher earned 1144 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
How to deal with a specific error when using the Enable-RemoteMailbox cmdlet to create a mailbox in the cloud-based service, for an existing user in an on-premises Active Directory.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

715 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