[Last Call] Learn how to a build a cloud-first strategyRegister Now


SQL Server 2008 Default Backup behavior?

Posted on 2011-10-17
Medium Priority
Last Modified: 2012-06-27

We have a client that is using SQL Server 2008 as the database for an application that should have components of the application running 24 hours a day.  I have several questions about the backup that is happening.

Every evening SQL server performs a backup of the database.  When this happens it kills the connections that the various workstations running the application have to the database.

I did not specifically setup the backup on this database, is there a default backup routine that is created when a new database is created?

A second question I have is can I setup the backup so that it does not close the connections I have to the database?  The applications that we are running become non functioning when they lose their connection and keep trying to connect.

I am finding that for what I need books online is not giving me the answers I need quickly enough.

Question by:apilkington
  • 3
  • 2
LVL 25

Accepted Solution

jogos earned 1500 total points
ID: 36981779
The backup-command of sql does not disconnect sessions. The easiest way is to create a maintenance plan with the maintenance plan wizard http://msdn.microsoft.com/en-us/library/ms177266.aspx.

Why disconnecting? Probably the backup is done by a thirth party tool.

Author Comment

ID: 36982089
As far as I can tell there is not a third party backup tool on the SQL server. Unless TrendMicro has a backup utility built into it.

I know from looking my databases are being backed up every night at 9:00 PM and when this happens it is disconnecting our applications connections to the database.  

Assisted Solution

apilkington earned 0 total points
ID: 36982387
Ok, after looking at the article that was at the link provided and looking at the scheduled tasks on the server in question, I have figured out that the problem we are having is most likely being caused by Windows Server Backup, not any specific SQL server tools.  This now gets us in a discussion with the client about how this server is being backed up.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 37077298
I've requested that this question be closed as follows:

Accepted answer: 0 points for apilkington's comment http:/Q_27400975.html#36982387

for the following reason:

I was able to do research and found the main issue I was having on my own while I was waiting for a response.
LVL 25

Expert Comment

ID: 37077299
Odd on 17/10 you say
Ok, after looking at the article that was at the link provided and looking at the scheduled tasks on the server in question, I have figured out that the problem we are having is most likely being caused by Windows Server Backup

From SQL Server point of view Windows Server Backup is a third party tool. And now you found it by yourself.

For as far as I can read you were waiting on your client.

Expert Comment

by:Bob Stone
ID: 37079171
There is a nice server backup tool that does SQL backups and restorations by BackupAssist that works well. It is pretty cheap too.

See - http://www.backupassist.com/BackupAssist/tour_SQL.html

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question