Learn how to a build a cloud-first strategyRegister Now

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

Move databse files

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
racineconde
Asked:
racineconde
  • 4
2 Solutions
 
imran_fastCommented:
CHECK FOR SP_DETACH AND SP_ATTCH
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
imran_fastCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
racinecondeAuthor Commented:
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
 
imran_fastCommented:
what is the error you are getting.
0
 
imran_fastCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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