Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blainekinnebrew
Starting Member
3 Posts |
 Posted - 2011-10-19 : 17:31:53
|
| Hi,I've implemented a log shipping solution and I start my restores everyday at 0315 and run one restore each hour and the last restore runs at 0715.This last restore is usually very small and it completes in a few seconds. But sometimes it finishes very late and I need to let my users know the database will not be available when expected.At 0720 I'd like to programmatically determine if the database is still being restored. How do I do this?This a SQL Server 2008 SP1 Enterprise Edition box and I'm using the standard backup and restore functionality that comes with SQL Server.In other words, I'm not using any 3rd party tools.I don't find an "is_restoring" column in master.sys.databases. Should I just attempt to connect to the database and assume it's still restoring if the connection fails? What's the best way to do this???Thanks,BlaineBlaine Kinnebrew |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blainekinnebrew
Starting Member
3 Posts |
Posted - 2011-10-19 : 17:40:13
|
| Hi Tara,Yes. I tried replication but it didn't like the fact that I was throwing 20 to 60 million changes a day at it.BlaineBlaine Kinnebrew |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-20 : 08:47:33
|
| Going back to the original question, this should provide you with the status.select state_desc from sys.databases where name='<your database name>'Having said that, have you considered database snapshots with mirroring as a solution? This is good if real time data is not required. You can create a job that re-creates the snapshot every 6 hours or so.. And creating a snapshot db is relatively fast. Anyways, even with log shipping, I guess, you cannot keep on restoring logs every hour or so as that would kick users out of the system. |
|
|
|
blainekinnebrew
Starting Member
3 Posts |
Posted - 2011-10-20 : 11:45:33
|
| Hi Cindy,Thank you very much. I discovered the same thing late last night when I was looking at sys.databases on BOL.BlaineBlaine Kinnebrew |
|
|
|
|
|
|
Programmatically Determine Restore State