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

Database Security

Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).

No its not possible to restrict SA from any database. There are many post on this topic on this forum

check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1

Madhu

|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||

Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA

Madhu

|||Thanks to your effort. I will try this for now|||

check my blog for some DDL script

http://madhuottapalam.blogspot.com/search?q=ddl+trigger

Madhu|||

I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Database Security

Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).

No its not possible to restrict SA from any database. There are many post on this topic on this forum

check this

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993336&SiteID=1

Madhu

|||Is there any suggestion on how could we secure our Database? for a possible tampering? or changing the data types.|||

Create DDL trigger on this database and prevent tampering or log tampering of the db objects. Its very good option avaliable in sql server 2005. Generally, you should remove Built/AdminGroup,Guest from the database. Set strong password for SA

Madhu

|||Thanks to your effort. I will try this for now|||

check my blog for some DDL script

http://madhuottapalam.blogspot.com/search?q=ddl+trigger

Madhu|||

I just want to emphasize that (as Madhu mentioned) it is not possible to restrict members of sysadmin from any database. Using triggers and other mechanisms to try to avoid tampering can be very helpful for keeping honest people honest and to prevent modifying the schema by mistake, but a sysadmin with enough determination won’t be stopped by such mechanisms.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine