Solved

Move databse files

Posted on 2006-11-08
6
205 Views
Last Modified: 2008-02-01
Hi,
Is there a way appart from the restore process to move the database files in different drives than they were originally?
for example from X: to Y:

Cheers,

R.
0
Comment
Question by:racineconde
[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
  • 4
6 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17897782
CHECK FOR SP_DETACH AND SP_ATTCH
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17897785
2 methods:
* backup the database, and during a restore, you can choose new locations for all the files individually
* detach the database, move the files, and use the procedure sp_attach_db to attach the db back to the server with the files in the new locations
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17897795
use sp_detach_db to detach the db files
copy the files(log and mdf) to other location on the server and then use sp_attach db to attach the file.
The detached files remain and can be reattached using sp_attach_db or sp_attach_single_file_db. The files can also be moved to another server and attached.


EXEC sp_detach_db 'pubs'

This example attaches two files from pubs to the current server.

EXEC sp_attach_db @dbname = N'pubs',
   @filename1 = N'y:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
   @filename2 = N'y:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:racineconde
ID: 17897939
There's another way by using the ALTER DATABASE Statement. I've got an error when I try to run it. Is someone has an idea of WHY

***********************
USE Master
GO
ALTER DATABASE myDB
MODIFY FILE (NAME = myDB_Data, FILENAME = 'D:\myDB.mdf')
GO
ALTER DATABASE myDB
MODIFY FILE (NAME = myDB_Log, FILENAME = N'H:\myDB_log.mdf')
GO

*******************
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17898039
what is the error you are getting.
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 100 total points
ID: 17898102
this can only be done to tempdb check alter database command in books online there is a note may be you missed that

Note  This example is applicable to tempdb only. To move user databases, use sp_detach_db and sp_attach_db. For more information, see Attaching and Detaching a Database.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

732 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