SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL database graceful shutdown that is not seen as unexpected


SQL database graceful shutdown that is not seen as unexpected

Author
Message
margarett.hance 40946
margarett.hance 40946
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 53
We have SQL server 2008 R2 on a Windows Server 2008 R2 Standard machine. We shut the database down every night to get a cold backup - the service is set to restart after an Unexpected shutdown.

Our problem is the database is restarting before we can get a cold copy of the files (about 15 minutes) & whatever database is copying when SQL restarts fails to start up because the file is locked.

My question is there a way to do a graceful shutdown so the operating system can tell the difference that this is NOT unexpected? We are using a .bat with Net STOP agent & net stop SQL SERVER.

The obvious answer would be to take the restart off the service - but we do like that option in case of a shutdown of the database other than for the backup.

Thank you for any help.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148322 Visits: 19444
I'd think that a net stop would be an expected shutdown. It could be some driver in Windows that is is not shutting down cleanly, and one that SQL Server uses.

However, the other question I'd have is why shutdown for backup? A cold backup isn't any safer or better than a hot backup in SQL Server.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
margarett.hance 40946
margarett.hance 40946
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 53
We do a full database backup then we do the cold backup of the database files - we have had occasion where we want to move those to anther server & attach them for an easy setup for a test. They can also be used in case of server failure - we have a window when we can shut the database down so we take advantge of that.

This method has worked fine until we changed the services to restart on unexpected shutdown.
liteswitch
liteswitch
SSC-Addicted
SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)SSC-Addicted (426 reputation)

Group: General Forum Members
Points: 426 Visits: 598
But you can restore the backup to another server easily enough, so I'm not sure what gains having a copy of the raw database files there are when you already have the database backup.
margarett.hance 40946
margarett.hance 40946
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 53
Redundant it is. I like to have as many options as possible.

SO i guess there is no way to shut the dataabse down without the system thinking it is unexpected. Thats what we thought - but was hoping we were wrong.
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3561 Visits: 2784
Full backup with log backup will result in minimal data loss

but

what happens if a server goes down in half way in your cold backup.

if you say redundant initiate mirroring or logshipping , another copy of server is almost ready.

Regards
Durai Nagarajan
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148322 Visits: 19444
There's a small advantage of the cold backup files in that you don't need the same amount of space. You need db space only, not db space + backup file space. A time savings as well, since you aren't copying the data from the backup file to the db files.

However. I haven't seen that to be worth the effort of shutting down the service.

That's a separate discussion. In terms of your issue with the service, I can't find anything, but my Google-fu is failing. Shot a note out on Twitter to see if I can find you something.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148322 Visits: 19444
Perhaps a bug?
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bc92fc1e-cf6e-4e68-8fe1-1702dd188f40/sql-server-instance-restarts-after-shutdown

I love Twitter :-D

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
PretendDBA
PretendDBA
SSC-Addicted
SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)SSC-Addicted (494 reputation)

Group: General Forum Members
Points: 494 Visits: 503
If your backup software supports VSS you realise SQL will let you create reasonibly reliable backups of the raw data files without stopping SQL?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search