Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL backup basics

Posted on 2013-05-21
Medium Priority
Last Modified: 2013-06-06
Can i ask for MS-SQL 2005, 2008, are there any useful free techniques to get a report of whats being backed up, how often, where the backup file is being written to, and ideally FLAG up databases where no backup job is configured, or it hasnt worked for some time, across all your databases? I assume its one backup job per database, not one backup job per instance?
Question by:pma111
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 30

Accepted Solution

Rich Weissler earned 1000 total points
ID: 39184159
Starting somewhere in the middle.  Last winter I got to see Mike Hillwig speak during a SQL Saturday, and he presented a framework for alerting from SQL Agent, which had the advantage of being free.  One of the example alerts he set up was to report databases which hadn't been backed up in a while.  http://crankydba.com/2012/07/28/seven-jobs-sqlsat126/comment-page-1/

Normally I have a few backup jobs per instance... but one job might be performing full backups of all the 'user' databases, one full backups of the system databases, and another job performing transaction log backups of most of my databases, and another to perform log backups of databases I love.

And I hate to just redirect you to someone else's blog, but I think they're going to be more comprehensive than I will.  The data you want is in the MSDB database... and other folks have written scripts to give you back all kinds of information about what backups have been preformed, etc.
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 39184176
Ideally one backup job per instance should be sufficient and you can use SQL Server Maintenance Plans to achieve it.

Author Comment

ID: 39184553
Razmus can those scripts provide false negatives. I.e. if you do an "all user database" backup, would that tool say you've done no backup to this database, when in reality it may have been swept uip in the instance level backup. As I am no DBA I dont really understand the difference between user level backup and system level backup.
LVL 30

Expert Comment

by:Rich Weissler
ID: 39185573
> I.e. if you do an "all user database" backup, would that tool say you've done no backup to this database, when in reality it may have been swept up in the instance level backup

The one backup job that handles the instance would backup individual databases.  You wouldn't have a separate backup that backs up the instance in a block.  (At least not with the tools native to SQL, which would then write the results into the msdb database.)  I can't think of a way for a false negative to occur, unless you truncated the history to a period of time shorter than your period between full backups, and I can't imagine a good reason that would occur.)

> [...] difference between user level backup and system level backup.

Not really levels of backups...  MS SQL Server has a small handful of databases it uses internally.  Specifically Master and MSDB should be backed up.  Model is the database used as a template to create new databases.  Those would be the system databases to be backed up.  (There's also a hidden resource database and the tempdb, but you woulnd't need to worry about performing backups on those.)  The system databases don't usually need to be backed up quite as frequently as the databases that contain the data everyone other than the DBA really cares about: payroll, sharepoint, vmware logs, etc.  I tend to backup master and msdb weekly, and after major system changes.  User databases I backup daily.  Then there are the transaction log backups that usually trip people up.  If the database is in FULL or BULK_LOGGED recovery mode, if you don't backup the transaction logs, they grow forever.  The only way to keep the logs under control is with backups.  But that's okay, because those transaction log backups make it possible to restore databases to a specific point in time.  I usually perform transaction log backups several times per day.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

610 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