Improve company productivity with a Business Account.Sign Up

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

Movins SQL DB

Hi

I would like to move a database files and the logs files to an other place on my network. An other disk.

What is the best way to do this?

Is it detach and re attache. Or do I have to backup, delete and restore the DB?

I use SQL Server 2008R2

Thank you
0
jpmoreau
Asked:
jpmoreau
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
If you don't mind taking the db offline for a bit detach and re-attach will work and is the cleanest.
0
 
Scott PletcherSenior DBACommented:
The cleanest is to ALTER the db properties to the new file locations, put the db offline, physically copy (or move) the files, then bring the db back online.  Since you can use SQL even for the file copies/moves, the entire thing can be scripted.

Detach has far too many side effects -- including changing the security settings, messing with replication and potentially not re-attaching at all if there are db errors -- so just don't use it unless you intend to move the db to a different server/instance entirely.
0
 
Gerald ConnollyCommented:
Backup anyway
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jpmoreauAuthor Commented:
How to ALTER the settings when the DB is offline?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
I think you can use the alter even while the database is offline:
    http://technet.microsoft.com/en-us/magazine/gg452698.aspx
0
 
Scott PletcherSenior DBACommented:
You alter before the database goes offline.  Indeed, you want to check it carefully before you take the db offline, and make sure that it is correct, just to avoid problems when you put it back online.

ALTER DATABASE <db_name>
MODIFY FILE ( NAME = <logical_file_name>, FILENAME = '<full_path_to_new_file>\filename.ext' )

ALTER DATABASE <db_name>
MODIFY FILE ( NAME = <logical_file_name2>, FILENAME = '<full_path_to_new_file>\filename2.ext' )

--...


Then run this before taking it offline:

EXEC db_name.sys.sp_helpfile

to make sure all the new file locations are correct before you take it offline, copy the files, bring it back online.

Note that if you copy the files to the new location, you don't have to take a separate backup first, since the original files can be brought back online if you hit any error with the new file locations.
0
 
jpmoreauAuthor Commented:
Thank you. The best way I found is to backup and restore to another place.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now