March 12, 2014 at 1:14 am
We have a cloud server at an ISP. Yesterday the server unexpectedly shut down (I can see that in the system logs).
When it restarted it restored the SQL 2008 Express server installed on the server to a point in history. It seems like 3 months back. So what happened is that all the databases and Logins I created between 3 months back and now simply disappeared. I searched the system logs for a system restore but there is nothing.
How is this possible?
Has it ever happened to someone else?
The server is Windows Server 2008 R2
The SQL Server is SQL Express 2008 r2
March 12, 2014 at 2:13 am
Ask your ISP what they did. Sounds like backups were restored of the databases or the entire machine or the system was reverted to an earlier savepoint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 3:28 am
Do you have database backups? You'll need to go to those to restore your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2014 at 3:37 am
i think system is gone back to its last restoring point. If you have a system restore point that was created 5 months ago and you restore to that point then the disk will be reset to look just like it was at that point. Any files you created since then will disappear. Any files changed since then will have their contents reset to the state they were in at that point. you should contact you ISP for this.
by the way where do you place your db backups
March 12, 2014 at 4:20 am
Thanks to the quick response
It also looks like a system restore to me, but I went through the System Logs and I can't find a log entry for system restore.
Could it be that the server is setup in such a way that when it crashes it restores itself to the closest Restore Point?
Anyway, I just wanted to find out from experts if they experienced something similar. It happened previously and the ISP support staff said "I don't know".
I'm going to contact them again now.
For backups we use a third party tool and it syncs with Google Drive, so luckily I could restore the databases. But still its effort and I have to recreate the Logins.
March 12, 2014 at 4:54 am
hans.pret (3/12/2014)
It happened previously and the ISP support staff said "I don't know".
Time to change ISP?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 4:57 am
GilaMonster (3/12/2014)
hans.pret (3/12/2014)
It happened previously and the ISP support staff said "I don't know".Time to change ISP?
+1000 🙂
March 12, 2014 at 7:15 am
Apparently cloud data can evaporate just like clouds in the sky.
March 12, 2014 at 7:23 am
Here in South Africa anything is possible. So data can get lost here in the clouds. Apparently this ISP is the best in South Africa as they won ISP of the year for three years now. We must accept that things are a bit slower here.
April 2, 2014 at 12:25 am
I know this discussion is basically closed but it happened again. The server was restarted and all my logins are gone and my databases has been restored to a point back in time.
I have done some thorough research on what could cause this and maybe its my fault and not the ISP's fault.
Could this behavior be because of the system databases, especially the master database? I realized that I do not backup the system databases.
Maybe when the server shuts down and comes up again SQL Express reads the latest backup of the master database and restores all objects according to that.
Is this possible?
April 2, 2014 at 12:51 am
No. Unless you had a script that overwrote those database files as the server was coming up, or something unbelievable like that.
April 2, 2014 at 1:04 am
I got more information on this.
I reviewed the sql logs again and saw that a checkpoint is created in the master database every time the server is restarted. If the server shuts down again and restarts SQL Express restores itself to that checkpoint and a new checkpoint is created in the master database.
This mean anything I created like a login or new database between the last shut down and the latest shut down is lost.
Can anybody tell me how I can prevent this?
If this is normal behavior for SQL I feel that I should create a checkpoint in the master database every hour at least just in case the server shut down unexpectedly. But how do I create this checkpoint.
Thanks for your help.
P.S. The unexpected shutdown is the ISP's fault.
April 2, 2014 at 3:41 am
hans.pret (4/2/2014)
I got more information on this.I reviewed the sql logs again and saw that a checkpoint is created in the master database every time the server is restarted. If the server shuts down again and restarts SQL Express restores itself to that checkpoint and a new checkpoint is created in the master database.
This mean anything I created like a login or new database between the last shut down and the latest shut down is lost.
Can anybody tell me how I can prevent this?
If this is normal behavior for SQL I feel that I should create a checkpoint in the master database every hour at least just in case the server shut down unexpectedly. But how do I create this checkpoint.
Thanks for your help.
P.S. The unexpected shutdown is the ISP's fault.
No, that doesn't make any sense. When you add a user, it gets written to a storage location. Written. Stored. Not through some vague process, but through the same processes as your data. So that stuff should still be there, even after a reboot. I've never had to do anything to my databases, even Express, before a reboot in order for the data that was saved to still be there after the reboot. That includes system tables.
No, something else is still at work here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2014 at 3:58 am
hans.pret (4/2/2014)
a checkpoint is created
A checkpoint just means it's writing to disk. It doesn't mean what you appear to think it means. They happen all the time, it's user configurable but usually every few seconds. It has nothing to do with what you're describing.
April 2, 2014 at 4:02 am
Thanks for the replies, I'm totally stumped. I have no clue what to do next.
I will investigate further.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply