SQLTeam.com | Weblogs | Forums

System generated full backup?



HI all,

SQL Server 2014, Playing with back/restore and I used SSMS 2014 to restore from database and it showed a very recent (2.5 hours ago) full backup and then two log backups (which I do hourly) which would get me to the most recent point in time.

I made no full backup 2.5 hours ago, I made one 2 days ago, Under to log backups was listed 'sa' but under the full backup was NT_Authority (or something like that).

My question: did the system make a full backup own it's own? How is that possible. There is no such .bak file int the backup folder of sql.

So what's going on here?



SQL Server maintains a set of system-level databases, system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update.
Please go this one it may be helpful to you. https://msdn.microsoft.com/en-us/library/ms190190.aspx


Thanks for that info. While the requirement to backup the master, model, and msdb system databases is something I was unaware of and obviously of great importance, it does not answer my original question. It seems the 'system' created a full backup of my database, which is really nice of it, but I don't know why it did it and why it was going to use that backup it made to restore the database.


Your systems people may be doing tape backups or SAN snapshots etc with VSS.
These might or might not be reliable as I have never been in a position to test them.

You can tell if they are by looking in the SQL Server Log (Management|SQL Server Logs in SSMS) and looking for a TYPE=VIRTUAL_DEVICE in a backup.
Another way to check is the device_type in msdb.dbo.backupmediafamily with something like

SELECT TOP (1) S.backup_start_date, S.backup_finish_date, M.physical_device_name
FROM msdb.dbo.backupset S
    JOIN msdb.dbo.backupmediafamily M
        ON S.media_set_id = M.media_set_id
WHERE S.[type] = 'D' -- Full Backup (I = Diff, L = Log)
    AND S.database_name = 'YourDB'
    -- 7 is the snapshot done by tape backup
    --AND M.device_type = 2
ORDER BY S.backup_finish_date DESC;

If VSS backups are being done, you will not be able to rely on differential backups so will have to keep a lot of log backups.


You might want to double-check what the MSDB database thinks the backup history is. Comment IN or OUT whichever "bits" you need

--	BS.database_name,
--	, * 
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
	AND BS.database_name = DB_Name()		-- Current DB
--	AND BS.database_name = N'MyDatabaseName'	-- Specific DB
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type='L'
--	AND BS.backup_size > 10000000
--	AND BS.backup_start_date > '20001231 01:23' 
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number

I agree the most likely "unexpected backups" would be from a Tape Backup which has an "Agent" on your particular server which is making the backups. If that is what you have and database backups to Files are NOT being backed up to Tape then I would want to be sure that your recovery route is "sound". You need to be able to get the Agent installed on a brand new machine (after a disaster) and then recover, direct from tape, using the agent.

Whereas if you put Backup Files on Tape then all you need to do is get the file back, somewhere, and transfer that file to the new server, and then restore it. Much better chance of success without "aggro" IMHO.

If your Backup Files ARE being copied to tape I'd get rid of the Agent backup direct to tape - high probability that it takes a Full backup at an unexpected time which wrecks your Chain for DIFF backups (and would require getting the Tape loaded, and Restored in order to get the DB backup, rather than just being able to restore "Latest FULL, DIFF and LOG files" which you have "lying around on disk for just that eventuality". Dunno about you, but here getting a tape loaded for a restore "takes some time" compared to any backup files that I have ready-access to on the server.


I think that is it! - I'm using a dedicated server from a hosting service and I did signup for system backups - with the SQL server plugin, forgot all about that. When I went to restore it saw that backup and was going to use it and 2 log file backups I had made. Thanks lfor and Kristen for that brilliant heads-up.