Accidentally deleted all data in the master database

Question

I used this script accidentally in the 'master' database instead of the temp database.

sp_msforeachtable 'delete from ?' 

Has it done any harm? If so, how can I recover the data?

Answer 1

No, it should not have deleted anything (unless you have user tables in master ).

Testing

 exec sys.sp_MSforeachtable 'select ''?''' 

It returns nothing to me. Thus, this excludes system tables such as spt_values .

Editing: indeed, looking at the definition of a procedure, it only includes tables in which OBJECTPROPERTY(o.id, N'IsUserTable') = 1

Answer 2

Martin Smith is right that sp_MSforeachtable does not delete system tables.

However, although we can consider tables like spt_values and MSreplication_options as system tables, they are actually user tables according to SQL Server.

When I run this query in my main database:

 SELECT name, OBJECTPROPERTY(object_id, N'IsUserTable') AS IsUserTable FROM master.sys.tables; 

I see the following set of results:

 name IsUserTable --------------------- ----------- spt_fallback_db 1 spt_fallback_dev 1 spt_fallback_usg 1 spt_monitor 1 MSreplication_options 1 

So how was Stijn saved after reinstallation?

If you look at how sp_MSforeachtable implemented, you will see that it does something like this to drop tables:

 declare @mscat nvarchar(12) select @mscat = ltrim(str(convert(int, 0x0002))) SELECT * from dbo.sysobjects o join sys.all_objects syso on o.id = syso.object_id where OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and o.category & @mscat = 0; 

In my main database, this returns an empty result set.

The where clause applies a bitmask to the category column of the sysobjects table to exclude tables that are not " mscat ".

Thus, tables in the main database are protected not because they are system tables, but because they are Microsoft tables.

This use of the category column is completely undocumented in Books Online. It all has an undefined description:

Used for publication, restrictions and identification.

But the sysobjects table is deprecated anyway, so you shouldn't use it. :)

An equivalent query using the supported sys.tables would look like this:

 SELECT * FROM sys.tables WHERE is_ms_shipped = 0; 

In my main database, this also returns an empty result set.