Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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