MS SQL Server - missing tables and stored procedures after restoring database

Author

missing tables and stored procedures after restoring database

Old Paulie

2005-08-26, 7:23 am

I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.

Any help in recovering those missing tables and stored procedures would be
greatly appreciated.

saradhi

2005-08-26, 7:23 am

use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc

Mike Hodgson

2005-08-26, 7:23 am

If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?

BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:

select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]

Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,

use A
go

select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc

If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com

Old Paulie wrote:

>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>into the database as user sa.
>
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>
>

Old Paulie

2005-08-26, 7:23 am

saradhi,

Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table. I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future reference
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ....?

"saradhi" wrote:

> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>
>

Old Paulie

2005-08-26, 7:23 am

Hi Mike,

I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong:( Thank you for your assistance.

"Mike Hodgson" wrote:
[color=darkred]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
>
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQL:
>
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
>
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
>
> use A
> go
>
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
>
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
>
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
>
>
> Old Paulie wrote:
>

Tibor Karaszi

2005-08-26, 9:23 am

One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
Check using RESTORE HEADERONLY.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Old Paulie" < OldPaulie@discussion
s.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD- 779132C78585@microso
ft.com...[color=darkred]
> Hi Mike,
>
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice and
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong:( Thank you for your assistance.
>
> "Mike Hodgson" wrote:
>

Old Paulie

2005-08-30, 7:23 am

Tibor,

It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.

THANK YOU VERY MUCH!

"Tibor Karaszi" wrote:

> One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
> Check using RESTORE HEADERONLY.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
> Blog: http:// solidqualitylearning
.com/blogs/tibor/
>
>
> "Old Paulie" < OldPaulie@discussion
s.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD- 779132C78585@microso
ft.com...
>
>

Sponsored Links