March 4, 2016 at 10:36 am
I wanted to create a copy of database 'A'. I turned the database online(originally it was off). Made a copy only backup, set the database back to offline, and then used the backup to create a new database labeled 'B'. So in Management Studio, I select 'B', and go to Generate Scripts Wizard. What I do there is list the object level permissions of the sprocs, so I change the user accounts to new accounts for 'B'. Anyways, every time I try to generate them, I always get this error:
Database 'A' cannot be opened because it is offline. (Microsoft SQL Server, Error: 942)
Why would it be looking in 'A' if I am doing this in 'B'?
March 5, 2016 at 7:29 am
Perhaps somewhere in the code that is in the database, three-part naming is used for object references. So perhaps a SELECT statement used FROM DatabaseA.dbo.TableX instead of just FROM dbo.TableX. (Same can also occur in references to views, stored procedures, functions, synonyms, and a whole bunch of other objects).
Do a search in the script on any references to the database name and either change them or take them out.
March 5, 2016 at 10:26 am
In addition to what Hugo suggested, don't forget to check any synonyms for ones that refer to a three-part name. You could have some that refer to database A.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply