Restore v Detach/Attach

Question

Restore v Detach/Attach

beath

SSCrazy

Points: 2939
More actions
July 12, 2006 at 9:09 am

#61450

I need to rebuild the master database on one of our servers and recover all the databases. I've done this many times before with no problems but on this particular server the databases come to about 500GB in size and I'm looking to find the quickest way. I could test each method but that would take me ages and defeat the purpose!
I think I could either:
1) Blow away sql. Reinstall. Restore all databases from backup.
2) Detach all user databases. Rebuild master. Restore msdb. Re-attach user databases.
I know roughly how long the first method will take as we've benchtested the restore times. Is the detach/attach method any quicker for large databases? Also I'd like to check that when I rebuild the master it doesn't physically delete the user database files so they're still there to attach when I'm finished?
cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

James Luetkehoelter SSCommitted Points: 1733 More actions · Answer 1

Kudos on being thorough! You'd be shocked at the number of times I've been asked questions about this situation AFTER the fact...

Yes, detach/reattch is much faster than backup/restore. I assume this is moving to a new server? Detach/file copy/reattach is going to be very fast compared to a backup/restore scenario. Just be aware that the .MDF maintains information as to the file locations of all of the other database files. When you reattach, you should have the option of changing those paths, otherwise make sure the physical location (D:\data\... etc., ) is exactly the same.

You will run into orphan users if you rebuild master. Be sure to script out any logins first to make sure you know what you need (or if you have a general login/user/grant script, that's even better). Check BOL under 'orphaned users' before starting.

Rebuilding master should not delete any user files, but if you want to be extra safe, detach any user databases before running it (I would, but I'm a bit paranoid).

Hope that helps a bit.

beath SSCrazy Points: 2939 More actions · Answer 2

Thanks for the info.

Actually everything's staying on the same server but sql was installed incorrectly and we've got collation issues across databases. I want to rebuild the master to put it in line with the others before changing the collation on the user databases.

Personal gripe here - it's fine you can restore different collation databases in SQL2000 onto the same server but they didn't make it particularly clear you can't run querys across them. Or maybe I was asleep when they told me that bit!

cheers

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 3

you can query inter-collation !

but you have to convert to the same collation in the query :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=95954

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Allen McGuire Mr or Mrs. 500 Points: 588 More actions · Answer 4

Very interesting. I'm glad I found this thread. I have a similar situation, and I'm assuming my resolution is easier.

I have a group of developers that wants to revert back to a particular database (call it the 'old' one) after they've done some testing and altered the data within. In other words, we often have to restore a 65 GB database from a couple months ago so they can run more tests. Would it be safe to assume I could:

1) Drop the current, altered database

2) Copy over the 'old' .MDF and slide it into the same location

3) Attach back to it via EM

That's it? I would imagine leaving the system databases alone would be fine to accomplish what I need to do?

Allen McGuire Mr or Mrs. 500 Points: 588 More actions · Answer 5

Sorry, one last twist to my post: my backup is currently a .BAK file, not an MDF. Does that mean my options change?

I'll research but I wanted to add this bit now that I've located the backup files - I'm actually supposed to do this today or tomorrow, but am thinking there are more efficient ways to go about this in the future.

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 6

actualy you can name your backup files whatever you want.

And in fact, you can also name your sqlserver db-files whatever you want.

If you stick with the guidelines of using the extentions .bak for backups and .mdf (for the primary-datafile), .ndf for secondary datafiles and .log for logfiles, it makes life a bit easier because you will not have to figure out what the heck is inside

You could create a backup before you have your def-team on the lose and just restore it afterward

If you take the db offline and make copies of _all_ the files that belong to that particular db and then put it backup online, once your dev's have done their thing, you can put the db back offline, rename or remove the datafiles and rename the copy-files back to the old filenames, put and the db back online. This would mean that you have at least your db-files-size disk available !