Web Wiz - Solar Powered Eco Web Hosting

  New Posts New Posts RSS Feed - Error when restoring to another server
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Error when restoring to another server

 Post Reply Post Reply Page  12>
Author
Pilsener View Drop Down
Groupie
Groupie


Joined: 20 May 2006
Status: Offline
Points: 120
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pilsener Quote  Post ReplyReply Direct Link To This Post 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 ?


Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Poole
Status: Offline
Points: 9730
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Pilsener View Drop Down
Groupie
Groupie


Joined: 20 May 2006
Status: Offline
Points: 120
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pilsener Quote  Post ReplyReply Direct Link To This Post 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 ?
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Poole
Status: Offline
Points: 9730
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Pilsener View Drop Down
Groupie
Groupie


Joined: 20 May 2006
Status: Offline
Points: 120
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pilsener Quote  Post ReplyReply Direct Link To This Post 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 ?




Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Poole
Status: Offline
Points: 9730
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Pilsener View Drop Down
Groupie
Groupie


Joined: 20 May 2006
Status: Offline
Points: 120
Post Options Post Options   Thanks (0) Thanks(0)   Quote Pilsener Quote  Post ReplyReply Direct Link To This Post 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.

Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Poole
Status: Offline
Points: 9730
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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:-

dbo.tblAuthor
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.05
Copyright ©2001-2022 Web Wiz Ltd.


Become a Fan on Facebook Follow us on Twitter Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2022 Web Wiz Ltd. All rights reserved.