Lynn,
Sorry, you are absolutely correct. it is just another database on the same server. we have 2 databases sitting on the same server.one is a live database, another is backup database. what the intention was to backup certain tables from the main db to backup db (and the other way around when restore is needed).
My guess Sean's method can alway be used? any other cleaner method?
if EXISTS(
select * from backupdatabase.sys.columns
where object_id = object_id('backupdatabase.dbo.tableA')
and is_identity = 1
)
SET IDENTITY_INSERT backupdatabase.dbo.tableA off