Copy Database Fails in SSMS - Invalid Object Name

This is a carry on from my earlier post about generating scripts to recreate
a database.
Instead of generating a database script, I just tried to copy it, using the
wizard in SSMS. It fails in the same way my generated script did earlier.
I have discovered that if I send the log output to a text file instead of
the windows event log I get much more detailed information (I wish it would
say that on the screen, given that event log is the default; it would saved
me hours). I also realise now that Copy Database just scripts the database
and then executes it, which is why I am getting the same problem here as I
was when I scripted the database separately and then ran the script.
The output log says 'invalid object name' when creating a view in the
script, and it picks out an object - another view, for example - that the
first view is dependent upon. Initially, it would complain about views named
'dbo.MyView1', so I removed the 'dbo.' and it fell over on another view. So,
I naturally thought that it didn't like the 'dbo.' prefix. However, once I
had removed all the prefixes, it started to complain about MyView1 again, so
that obviously wasn't the real problem.
The strange thing is that this used to work. I have copied this database
before (perhaps a month ago) without problem, but I can't begin to think
what might have changed to cause it to fall over now. Could it be something
to do with permissions, or context? I connect using the database owner's
login, but I have tried others (all of which are in the sysadmin group), but
to no avail.
Can anyone suggest what could be going on, or how I can get closer to the
real cause?
TIA
CharlesHi
Could it be that you try to create an object (view) that contains another
object (another view) that does not exist yet?
"Charles Law" <blank@.nowhere.com> wrote in message
news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
> This is a carry on from my earlier post about generating scripts to
> recreate a database.
> Instead of generating a database script, I just tried to copy it, using
> the wizard in SSMS. It fails in the same way my generated script did
> earlier.
> I have discovered that if I send the log output to a text file instead of
> the windows event log I get much more detailed information (I wish it
> would say that on the screen, given that event log is the default; it
> would saved me hours). I also realise now that Copy Database just scripts
> the database and then executes it, which is why I am getting the same
> problem here as I was when I scripted the database separately and then ran
> the script.
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that the
> first view is dependent upon. Initially, it would complain about views
> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
> However, once I had removed all the prefixes, it started to complain about
> MyView1 again, so that obviously wasn't the real problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the database
> owner's login, but I have tried others (all of which are in the sysadmin
> group), but to no avail.
> Can anyone suggest what could be going on, or how I can get closer to the
> real cause?
> TIA
> Charles
>|||Hi Uri
Well that was what I thought originally, but now I am not so sure. It will
frequently be the case that a view uses another view or another object.
Therefore, the Copy Database operation in SSMS must be able to create
objects in the correct order. Either that, or it mustn't care if an object
does not exist yet. Either way, Copy Database would be useless if it
couldn't work out how to copy a database without hitting this problem,
wouldn't you say?
Charles
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4nelCNCIHA.5328@.TK2MSFTNGP05.phx.gbl...
> Hi
> Could it be that you try to create an object (view) that contains another
> object (another view) that does not exist yet?
>
> "Charles Law" <blank@.nowhere.com> wrote in message
> news:eYgkq4MCIHA.2004@.TK2MSFTNGP06.phx.gbl...
>> This is a carry on from my earlier post about generating scripts to
>> recreate a database.
>> Instead of generating a database script, I just tried to copy it, using
>> the wizard in SSMS. It fails in the same way my generated script did
>> earlier.
>> I have discovered that if I send the log output to a text file instead of
>> the windows event log I get much more detailed information (I wish it
>> would say that on the screen, given that event log is the default; it
>> would saved me hours). I also realise now that Copy Database just scripts
>> the database and then executes it, which is why I am getting the same
>> problem here as I was when I scripted the database separately and then
>> ran the script.
>> The output log says 'invalid object name' when creating a view in the
>> script, and it picks out an object - another view, for example - that the
>> first view is dependent upon. Initially, it would complain about views
>> named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on another
>> view. So, I naturally thought that it didn't like the 'dbo.' prefix.
>> However, once I had removed all the prefixes, it started to complain
>> about MyView1 again, so that obviously wasn't the real problem.
>> The strange thing is that this used to work. I have copied this database
>> before (perhaps a month ago) without problem, but I can't begin to think
>> what might have changed to cause it to fall over now. Could it be
>> something to do with permissions, or context? I connect using the
>> database owner's login, but I have tried others (all of which are in the
>> sysadmin group), but to no avail.
>> Can anyone suggest what could be going on, or how I can get closer to the
>> real cause?
>> TIA
>> Charles
>>
>|||Charles Law (blank@.nowhere.com) writes:
> The output log says 'invalid object name' when creating a view in the
> script, and it picks out an object - another view, for example - that
> the first view is dependent upon. Initially, it would complain about
> views named 'dbo.MyView1', so I removed the 'dbo.' and it fell over on
> another view. So, I naturally thought that it didn't like the 'dbo.'
> prefix. However, once I had removed all the prefixes, it started to
> complain about MyView1 again, so that obviously wasn't the real
> problem.
> The strange thing is that this used to work. I have copied this database
> before (perhaps a month ago) without problem, but I can't begin to think
> what might have changed to cause it to fall over now. Could it be
> something to do with permissions, or context? I connect using the
> database owner's login, but I have tried others (all of which are in the
> sysadmin group), but to no avail.
The tools can only do as good as they can given the input. And in the input
in this case is the view sys.sql_dependencies, which by its design cannot
be wholly reliable. To wit, references are stored by object_id. So guess
what happens if you try:
create view a as select n = 12
go
create view b as select n from a
go
exec sp_depends b
go
drop view a
go
create view a as select n = 123
go
exec sp_depends b
go
drop view a, b
By dropping a and recreating it, the information that b depends on a is
lost.
One way out is to create all views WITH SCHEMABINDING. Had b in the example
above been defined WITH SCHEMABINDING, it would not be possible to drop or
alter a without first dropping b. Then again, this measure may be to
harsh for you.
If you are on SQl 2005 SP2, there is a way out for you: you can use
sp_refreshsqlmodule to refresh the dependency information for a view
or a procedure. You would have to run it for all your views before you
script.
Another option is to simply maintain a build-script the loads the views in
the right order. Note that this script would not be an SQL script - it
would be a script in VB-script, Perl, C# or even a BAT file that reads the
files with the view definition and load them to SQL Server. The files
would of course be retrieved from the version-control system.
In SQL 2008, currently in beta, they have a new style for reference
information, so that information is held by name and not by object id.
Thereby dependencies are not lost when an object id is dropped. I have
not tested whether the tools actually make use of this features.
Personally, I find that the new feature has several shortcomings compared
to the old reference information, particularly with regards to information
on column level.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx