Pau Lo
asked on
SQL backup basics
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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.
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.
ASKER