Put it *where*?

disclaimer: this is kind of pointless. read at your own
risk

--------------------------------------------------------------------------------------

So let’s put a database in an alternate data stream. for some reason. Furthermore let’s make it all sneaky and make it look like some random unsuspicious system file, like thumbs.db :

CREATE DATABASE [hiddendb] ON PRIMARY

( NAME = N'hiddendb', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'hiddendb_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

Holy crap, that actually worked somehow…

Can we put data in it though?

use hiddendb

go

select *

into dbo.randomrows

from spt.dbo.exported, spt.conversion.modeloff --some other tables

(343917 row(s) affected)

yup, seems so. (column names have been hidden to protect innocent).

But wait…

My thumbs.db file still only shows up as 0 KB! it totally isn't though, you can see it's real size in sys.database_files

So anyway that gets me thinking….said database is in an alternate data stream. SQL server uses alternate data streams when running checkdb. How would that work with this fancy db?

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 123(The filename, directory name, or volume label syntax is incorrect.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\thumbs.db:mdf:MSSQL_DBCC26'.

Msg 7928, Level 16, State 1, Line 1

The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

DBCC results for 'hiddendb'.

so that was fun! hope you find this interesting!