Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL database graceful shutdown that is not seen as unexpected Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 7:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:08 PM
Points: 132, Visits: 49
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.

Post #1486716
Posted Wednesday, August 21, 2013 8:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:05 PM
Points: 33,165, Visits: 15,299
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
Post #1486742
Posted Wednesday, August 21, 2013 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:08 PM
Points: 132, Visits: 49
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.

Post #1486826
Posted Thursday, August 22, 2013 4:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:35 AM
Points: 69, Visits: 565
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.
Post #1487174
Posted Thursday, August 22, 2013 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:08 PM
Points: 132, Visits: 49
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.
Post #1487205
Posted Thursday, August 22, 2013 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1487261
Posted Thursday, August 22, 2013 8:57 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:05 PM
Points: 33,165, Visits: 15,299
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
Post #1487330
Posted Thursday, August 22, 2013 9:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:05 PM
Points: 33,165, Visits: 15,299
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







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1487346
Posted Thursday, August 22, 2013 6:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 8, 2013 10:48 PM
Points: 31, Visits: 124
If your backup software supports VSS you realise SQL will let you create reasonibly reliable backups of the raw data files without stopping SQL?
Post #1487603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse