2

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, 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.

Improve this question

3 Answers 3

Reset to default
1

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. :)

Improve this answer
7

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.

Improve this answer
1
  • Thanks for the extra explanation, Aaron. Much appreciated. I'm an avid reader of your blog.
    – Skkra
    Sep 19, 2012 at 13:07
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.