2

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

Holocryptic
  • 5,665
  • 2
  • 28
  • 37
Keith Sirmons
  • 740
  • 3
  • 13
  • 23

1 Answers1

2

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.

Holocryptic
  • 5,665
  • 2
  • 28
  • 37
  • You **Dropped** a database? That's a different story. From [here](http://msdn.microsoft.com/en-us/library/ms178613.aspx) : 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