Database Snapshot on tempdb

Hi Experts:

Can you please offer an explanation on why a database snapshot cannot be created on tempdb?. Is it because the tempdb is created everytime fresh when SQL Server is restarted or some other reason?.

Any insight is appreciated.

Thanks

Ankith

Can I answer this with a question? Why would you want a snapshot of tempdb?

Anyway, the books online say: "Snapshots of the model, master, and tempdb databases are prohibited." These are system databases and you normally wouldn't want to mess with them too much imo.

|||

Hi:

I am not creating a snapshot on tempdb. I just want to know the techinical reason behind it.

Thanks

|||

Probably the primary reason is that tempdb is a temporary store for use by the engine for many situations, worktables, row versions, etc. having a snapshot does not have a purpose and would not work because of these.

|||

Hi Simon:

Thank you for the explanation.

Ankith.

|||

The actual technical reason is that database snapshots undergo restart recovery to rollback any active transactions at the time the snapshot is created. Since TEMPDB cannot be recovered (it is recreated each time the server starts), you cannot create a snapshot on it.

Thanks,
--R