471,607 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,607 software developers and data experts.

How to recover deleted data from master database

Hi all,
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?
Aug 5 '06 #1
1 5845
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 vínculos...
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
Aug 7 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Grant | last post: by
1 post views Thread by Roberto La Forgia | last post: by
3 posts views Thread by Jeff Kish | last post: by
1 post views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by MichaelMortimer | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.