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 Windows 2008 R2 server that needs to be periodically reset to a clean state. Normally I would look at creating an image of the system drive, however due to organizational reasons I cannot use an image for this purpose.

So what I'm working on is using a combination of Windows System Restore and SQL's internal backup. The piece I'm missing right now is how to actually back up the SQL 2008 R2 Instance and associated configuration settings.

Basically I would like to be able to save a snapshot of the instance itself (logins, schemas, defaults, etc.) as well as anything necessary to restore the application to a working state from a bare metal restore (so things like service owners and registry keys).

Does SQL itself have a way to do this or do I have to look for another way to back up the application? For the logins, schemas and defaults, are those contained within one of the system databases?

share|improve this question
    
Using a combination of a Restore Point and a Volume Shadow Copy sounds reasonable to me... I've never attempted anything like that, though. An O/S image really is the way to go. –  Jon Seigel Aug 13 '13 at 16:55
    
I don't think I can do a Volume Shadow Copy of the system drive (which is where the OS and SQL live, but not the databases). I might have to yell at people to let me just do an image of that drive. –  Sean Long Aug 13 '13 at 16:58
1  
I believe you can Shadow Copy just a folder, not the entire volume (I was thinking all the SQL files would be on a volume separate from the system volume). –  Jon Seigel Aug 13 '13 at 17:00
    
You just blew my mind. I'll check that out. –  Sean Long Aug 13 '13 at 17:03
    
Okay, sorry, I was wrong. I just played with this. The volume snapshot gets enabled at the volume level, and then you can restore anything on that volume individually. You're right, though, that I can't seem to restore on a system volume. If you can move SQL off there (it's a good idea anyway), this solution should work. –  Jon Seigel Aug 13 '13 at 17:09
show 3 more comments

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.