Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a server that we will be completely reformatting then rebuilding. Everything will be rebuilt exactly as it is now - same filenames, same directories, etc.

After SQL Server is installed, I could reattach each of the databases on the server instance one by one from the full backups I've taken.

  • However, out of curiosity, will the following also work to make the job faster?
    1. Shut down the SQL Server instance
    2. Copy all databases, including system databases, to another disk
    3. Reformat the server and reinstall SQL Server
    4. Shut down the SQL Server instance
    5. Copy all files back to their original locations, overwriting existing files (would I also need the hidden Resource database?)
    6. Start the SQL Server instance again

As long as everything is in the correct location, shouldn't the master database fire right up without issue, and the full server will be restored to the same way it looked pre-reformat? I'm just wondering if this could save me some time.

Many thanks!

share|improve this question
add comment

migrated from stackoverflow.com Sep 20 '12 at 12:34

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 1 down vote accepted

You're better off detaching the databases from the server, then reattaching them after installation. Detach and Attach are pretty fast operations, and they're quite clean to execute from SQL Server Management Studio.

Plus, you only need to do that to the databases you care about. Let the System DB take care of itself. :)

share|improve this answer
add comment

This might work, and I cannot say that I have every tried it. But I would expect to run into problems at the intersections between the SQL Server instance and the operating system. For just one example, you will almost always find certain system entities as logins, such as NT Authority\Network Service. If you use windows logins and domain level security, you may have many such users.

If you actually try this, I would be curious about the results. But I would try this on an expendable test system first and if you do decide to actually go through with this on a production server I would make sure you have a set of conventional backups ready just in case.

share|improve this answer
add comment

Personally I advocate quite strongly against the detach / attach method. You're already taking proper backups, right? Why not continue that plan and use backup / restore?

Detach / attach is dangerous, for one simple reason: if something happens during the detach or during the copy, you now have ZERO copies of your database.

With RESTOREs you will be able to proceed even if your new server gets hosed and you need to use a different server, or you're not able to re-use the same server name, or any other host of things could go wrong.

share|improve this answer
    
Thanks for the extra explanation, Aaron. Much appreciated. I'm an avid reader of your blog. –  Skkra Sep 19 '12 at 13:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.