Go Premium for a chance to win a PS4. Enter to Win

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

Moving SQL database to different Drive

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
jskfan
Asked:
jskfan
7 Solutions
 
NetfloCommented:
Step by step instructions using SQL Server Management Studio: http://www.katieandemil.com/sql-server-move-database-to-a-different-drive
0
 
ZberteocCommented:
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
 
Scott PletcherSenior DBACommented:
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
Technology Partners: 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!

 
PadawanDBACommented:
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
 
Scott PletcherSenior DBACommented:
True, since sp_detach_db is still needed for moving dbs to a different server/instance.
0
 
jskfanAuthor Commented:
ScottPletcher

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

Thanks
0
 
Scott PletcherSenior DBACommented:
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
 
jskfanAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
jskfanAuthor Commented:
I will try it Thank you
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now