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.