Error when restoring to another server
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=29586
Printed Date: 28 March 2026 at 2:10am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Error when restoring to another server
Posted By: Pilsener
Subject: Error when restoring to another server
Date 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 ?
|
Replies:
Posted By: WebWiz-Bruce
Date 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
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: Pilsener
Date 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 ?
|
Posted By: WebWiz-Bruce
Date 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.
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: Pilsener
Date 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 ?
|
Posted By: WebWiz-Bruce
Date 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.
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: Pilsener
Date 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.
|
Posted By: WebWiz-Bruce
Date 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:-
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: Pilsener
Date Posted: 02 August 2011 at 5:13pm
Old name: username@live.no
New name: pilsener
I cannot use dbo at all.
I have to use the login name for the hosting account, which is pilsener.
I have managed to use the database backup-wizard at the hosting provider to change ownership for all tables except these:
tblLadderGroup
tblSetUpOptions
tblSpam
tblThreadThanks
tblTopicRatingVote
What will happen if I delete these tables ?
Will they be rebuilt by the upgrade wizard ?
|
Posted By: WebWiz-Bruce
Date Posted: 02 August 2011 at 6:07pm
You should not delete any tables, they would not be rebuilt.
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
Posted By: Pilsener
Date Posted: 02 August 2011 at 7:42pm
It didn't work, deletion or not.
I'm starting to run out of hair to pull out.
Is it possible to create a new and empty forum/database and then copy the info from a backup of the current one ?
|
Posted By: WebWiz-Bruce
Date Posted: 03 August 2011 at 9:10am
It's not possible to copy data.
You should be able to use DBO with any SQL Server as it means that any user given DBO (Databasse Owner) rights to the database has full control of that object. This makes it simpler to move SQL server Database between databases without the problems that you are having.
I would check with your provider about not being able to set the owner of tables to DBO as it would seem strange if you can not. This way it will also make moving to a new host in future mush simpler.
Don't forget as well you can also host your forum or whole website with ourselves on our own servers. We charge a £19.95 migration fee to move the forum to our network but we can update the object owners of the database to DBO during this migration process.
http://www.webwiz.net/web-hosting/" rel="nofollow - Website Hosting Packages
http://www.webwiz.net/web-hosting-resellers/" rel="nofollow - Reseller Hosting Packages
http://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting package
-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting
|
|