Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE
recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE
recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE
recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE
recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
The msdb
database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.
This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+. Just tested in 2012.
/*
Find Where DB Backups Went Physical Location
For last two days.
backupset.type
D --> FULL
I --> DIff or incrimental
L --> Log backups
*/
DECLARE @dbname sysname
SET @dbname = ''
SELECT
@@servername [ServerName]
,master.sys.sysdatabases.name [DatabaseName]
,msdb.dbo.backupset.backup_start_date [Backup Date]
,msdb.dbo.backupset.user_name
,datediff(second, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
,msdb.dbo.backupmediafamily.physical_device_name [File Location]
,msdb.dbo.backupset.type
FROM
msdb.dbo.backupmediafamily,
master.sys.sysdatabases
LEFT OUTER JOIN
msdb.dbo.backupset
ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY
master.sys.sysdatabases.name
,msdb.dbo.backupset.backup_start_date
,msdb.dbo.backupset.backup_finish_date
,msdb.dbo.backupmediafamily.physical_device_name
,msdb.dbo.backupset.type
Best Answer
Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.
When creating a backup, you can specify the
WITH COMPRESSION
keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that
xp_cmdshell
is enabled on the instances, and then use a command line to compact using for e.g. WinRar.I use a cmd file that looks something like this:
You can then execute this cmd file from your 2000 / 2005 instances. You can also play around with passing
%1
-type variables to the cmd file, if your filenames are not generic.