SQL Server backups to Alternate Data Streams or Colons

So in the midst of a very busy day I performed a manual backup of a database for a developer so he could make some major changes, test, and rollback if needed. I entered the backup file name with a colon on the timestamp…DUH! Of course, this worked, as NTFS supports the use of Colons in the filename. Went back later to restore the backup for the developer and the filename was truncated at the colon and was 0 bytes in size. WTF, the backup worked, there was no warning or error from SQL Server? Then I remembered my old NTFS “friend” – Alternate Data Streams. Basically ADS is a way to put data into different streams of the file. If you’ve ever wondered how Windows knows to warn you when you run an executable downloaded from the internet via IE, this is how. IE places a “zone.identifier” in the ADS to let Windows know this file might not be safe.

There’s a couple of ways to get around this and recover the backup regardless of the truncated filename and the size of 0. The quickest and easiet way is to just restore the database or log from TSQL. So if you backup a database with

BACKUP DATABASE test TO DISK='test_11:30.bak'

That will work fine. In your default backup directory, you’ll see a file “test_11” and it will be 0 bytes in size. If you then try to use the SSMS GUI to restore this, it will fail.

If you instead use TSQL…

RESTORE DATABASE test FROM DISK='test_11:30.bak'

it will work.

The colon tells the OS to create a file with aname of everything before the colon, and all the data into an ADS with an idetifier of everything after the colon… so in our example, test_11 has an ADS in it with an identity of :30.bak. The backup data is all there in that stream.

So now you’re saying “Well, what if I don’t know the stream identity”! If that’s the case there are a number of tools that can tell you all the ADS identities in a file… I use STREAMS from that Sysinternals genius, Mark Russinovich. If will spit out the ADS in the file you give it. I’ve also used notepad and a Windows port of the *nix “cat” command to pull the data out of that ADS and into a new file. That new file would then be able to be restored from with the SSMS GUI. With Notepad, just open a command prompt and type NOTEPAD test_11:30.bak and give it some time, and it will have all that data in Notepad. Save that as test.bak and you can restore anyway you want.