Today my day started with a drive full issue. That too a drive on which the tempdb was residing! Someone had kept some good old backup files on this drive, may be in an effort to clean up another drive.
The question was whodunit? This was a good question. Even though we may not be able to track who copied it there, at least we can know who took the backup.
The approach is even though very simple, but at times it is very useful. Yes, I am referring to RESTORE HEADERONLY…….
Restore Headeronly command gives the header information for a given backup set. The header contains some useful information like the Backup Type, User Name, Server/Database Name, Backup Start & End Date etc. The syntax is something like
RESTORE HEADERONLY FROM DISK = 'C:\GuessWhoDunit.bak'
and the output will be like
In my case, it turned out that the backup was taken using SQL Server Agent and the user name was the service account !
Even though this exercise did not give the expected results, it was good to put in use one of the DBA friendly commands.
Related posts:
thanks for the info
nice info,
i do want to scheduled DB backup daily and in backup folder the older backup ed file must be delete means it must have only last latest 3 days backup policy. how to do it. can u assist.
Maintenance plan is the easiest way to accomplish your requirement. This video on MSDN explains it nicely.