| Author |
 Topic Search  Topic Options
|
Pilsener 
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Error when restoring to another server
Posted: 29 July 2011 at 9:29am |
|
I was going to move my forum to another hosting company, so I made a backup of the MSSQL database and sent to the new company, as they didn't offer any option for the users to do the restoration process on their own.
I then ran the update wizard, which reports that I can successfully connect to the database.
However, in the next step, I get this error msg: "The Database Setup Wizard can not find any Web Wiz Forums tables in the
database."
The database is a MSSQL 2005. The new company is running MSSQL 2008.
Could this be the problem ?
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 July 2011 at 10:29am |
|
You can always move up a verion of SQL Server but not down a version, so it is fine moving from SQL Server 2005 to SQL Server 2008.
The error suggests that:-
1. The database has not yet been restored
2. The database has not been restored correctly
3. You are connecting to the wrong database
|
|
|
|
Pilsener
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 July 2011 at 10:44am |
Option 1 and 3 can be ruled out. But I got a tip from some dude: " Please note that the database usernames need to
match the login users on the server -- because the
tables were once created in that user's context
It's also important to resolve/match the users SID
on your current box to the same one on the database"
Do I need to change the "object owner" for the tables ?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 July 2011 at 10:54am |
|
It's best to connect to the database with the Database Owner (DBO) and create tables under that user, otherwise when you move database even if you have the same username it will not match as the object owner due to the SID.
Tables should be [dbo].[tableName] if not you will have problems like this. You would need someone with the correct permissions on the database to change the object owner for each table to 'dbo'.
The syntax that you need to change the owner a table is:-
sp_changeobjectowner 'owner.objectname', 'dbo'
You would need to do this for each and every table in your database to change the owner to 'dbo'
Once the tables have the object owner of 'dbo' then any user you give Database Owner rights on the database will have full control of these objects and allow you to easily move the database between database servers without having this issue of object user.
|
|
|
|
Pilsener
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 July 2011 at 11:01am |
|
I had to change the object owner when I upgraded from Access to MSSQL, because the webserver refused to let the WebWiz upgrade wizard modify the tables.
None of the webservers offers any option to change the owner back to "dbo".
Is there any editors available for doing this ?
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 July 2011 at 2:34pm |
|
You may need to get your host to do this as only ever done this at admin level, so unsure if you need to have the admin permissions in order to do this.
You could give it a try though with SQL Server Studio manager. You may find though without admin permissions that you need to make the change on the old server and then do the backup and restore again.
|
|
|
|
Pilsener
Groupie
Joined: 20 May 2006
Status: Offline
Points: 123
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 August 2011 at 3:49pm |
|
I have installed an MSSQL 2008 server here at home and restored the database into it.
I tried to run sp_changeobjectowner 'username@live.no.tblAuthor', 'pilsener' but it gave me an error msg saying Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 62
Object 'username@live.no.tblAuthor' does not exist or is not a valid object for this operation.
Is it the @ that causes the problem ?
The old hosting provider required the account login name as database username, so that's how the @ got involved.
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 August 2011 at 4:36pm |
You have the wrong syntax it should be:-
sp_changeobjectowner 'owner.objectname', 'dbo' |
So for example if your old username was 'pilsener' and the table is 'tblAuther' you would have:-
sp_changeobjectowner 'pilsener.tblAuthor', 'dbo' |
Once the change is made the table owner should be dbo as in the example below:-
|
|
|
|
