Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a SQL 2000 SP2 instance (version 8.00.760) that is on a drive that gets regular shadow copies.

Can a shadow copy be used to restore the database?

It seems possible to stop the SQL service, restore the Data folder from the shadow copy (includes msdb, master, model, temp, and the user databases, then restart the service.

Would the files be in a crash consistent case in the worst case? If so, when restarting the service wouldn't it recover as if the power were pulled from the server?

Thank you, Keith

share|improve this question

1 Answer 1

up vote 2 down vote accepted

You can use them to do a restore. If you have SQL upgrade to SP 4, plus what I have listed below, then it will recognize a VSS call and be put into a consistent state.

Apply KB940349-v3.

Run “vssadmin list writers” and check if the MSDE writer appears in the list. If not and if the Vssadmin.exe tool stops responding, apply MSFT KB913100.

In SQL Server 2000 Enterprise Manager add “NT AUTHORITY\System” to the Login within Security entry.

In Server Functions, add “NT AUTORITY\System” to the SysAdmin function.

Edit: Gahh, I just looked at your question again, and you say you're running SP2. This means that it doesn't recognize the MSDN VSS writer, and won't be in a consistent state. The database and logs will be in a crash consistent state.

share|improve this answer
    
You Dropped a database? That's a different story. From here : A dropped database can be re-created only by restoring a backup. I'm by no means a SQL authority, but it looks like you're restoring from a backup. –  Holocryptic May 16 '10 at 16:42
    
Thank you for your help. I will keep this in mind when working backup schedules. –  Keith Sirmons May 17 '10 at 20:35

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.