August 21, 2013 at 7:48 am
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.
August 21, 2013 at 8:13 am
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.
August 21, 2013 at 11:40 am
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.
August 22, 2013 at 4:56 am
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.
August 22, 2013 at 6:25 am
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.
August 22, 2013 at 7:46 am
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
August 22, 2013 at 8:57 am
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.
August 22, 2013 at 9:22 am
August 22, 2013 at 6:44 pm
If your backup software supports VSS you realise SQL will let you create reasonibly reliable backups of the raw data files without stopping SQL?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply