I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.
Someone know how can I recover this data?Here is the script:
declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor
-- ligando os vnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
deallocate table_name_cursor
And here is the names of the tables of master database that this script had deleted all the containing data:
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
spt_server_info
No comments:
Post a Comment