Malevolo
asked on
Importing a backed up SQL database to a new one with a new name/user/pw
A client of mine recently switched to a new webhosting account so we're in the process of restoring their website for them. Their website was built in ASP and depends on a small SQL database for some information. I've restored all data to the new host, and created a new SQL database to restore that to. The account is with 1and1.com, so anyone familiar with their setup will know that they assign you a database name, user and password and you can't alter this information.
Now, after I imported my SQL backup (something.bak) into the new database, it looks like the tables and data are all there but after updating the settings in my main ASP page, I get an "HTTP 500 - Internal server error" when trying to navigate to, lets say: http://xxyyzz.com/products.asp?catCode=TP
I edited the below info in products.asp; no other files I found contained any reference to a database so I guess this is the only one I need to change.
[[[OLD]]]
ConnectString="PROVIDER=SQ
[[[NEW]]]
ConnectString="PROVIDER=SQ
1) Does the "PROVIDER=" field need to be changed from "SQLOLEDB" to something else?
2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?
3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?
Thanks very much in advance!
Now, after I imported my SQL backup (something.bak) into the new database, it looks like the tables and data are all there but after updating the settings in my main ASP page, I get an "HTTP 500 - Internal server error" when trying to navigate to, lets say: http://xxyyzz.com/products.asp?catCode=TP
I edited the below info in products.asp; no other files I found contained any reference to a database so I guess this is the only one I need to change.
[[[OLD]]]
ConnectString="PROVIDER=SQ
[[[NEW]]]
ConnectString="PROVIDER=SQ
1) Does the "PROVIDER=" field need to be changed from "SQLOLEDB" to something else?
2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?
3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?
Thanks very much in advance!
1) Does the "PROVIDER=" field need to be changed from "SQLOLEDB" to something else?
If the restore had been on a SQL SERVER database, no. this means that you are using OLE DB provider for SQL Server to connect.
2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?
Not sure, but ' should mean comment (at least in VB6)
3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?
What do you mean for userid for all the table? the owner? this is the old user because of the restore. You should correct this, but I think your issue comes from another problem.
Be sure to grant to the new user (user22222) the correct permissions (select, insert, update, delete) on the database objects (tables, views and so on).
You can do it all at once giving permissions on the SCHEMA to which the tables belongs.
If the restore had been on a SQL SERVER database, no. this means that you are using OLE DB provider for SQL Server to connect.
2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?
Not sure, but ' should mean comment (at least in VB6)
3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?
What do you mean for userid for all the table? the owner? this is the old user because of the restore. You should correct this, but I think your issue comes from another problem.
Be sure to grant to the new user (user22222) the correct permissions (select, insert, update, delete) on the database objects (tables, views and so on).
You can do it all at once giving permissions on the SCHEMA to which the tables belongs.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DeeEmm:
1&1's SQL servers are not on the same machines that run the webservers; this is the server source required. I could resolve the name to an IP if you think this might help.
ibrusett:
Could it be that 1&1 is using an alternative method to connect to the SQL Server; something other than OLE DB? If there are multiple ways maybe I'll call and ask...
You're correct, I meant the owner is listed as "oldUSR". The username I'm using now should have full access over the database; are these rights not attributed automatically to data imported from a backup file? If they aren't how can I do this? Is there a query or something I can send to change owner from "oldUSR" to "user22222"?
1&1's SQL servers are not on the same machines that run the webservers; this is the server source required. I could resolve the name to an IP if you think this might help.
ibrusett:
Could it be that 1&1 is using an alternative method to connect to the SQL Server; something other than OLE DB? If there are multiple ways maybe I'll call and ask...
You're correct, I meant the owner is listed as "oldUSR". The username I'm using now should have full access over the database; are these rights not attributed automatically to data imported from a backup file? If they aren't how can I do this? Is there a query or something I can send to change owner from "oldUSR" to "user22222"?
ASKER
this is what I got from 1&1's site right now, I'm on the phone trying to confirm which databse they use exactly. this is the application they provide to administrate SQL: myLittleAdmin (for SQL Server and MSDE) v.2.7 r.123
Server MSSQL
Server name : MSSQL
Server version : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Language : us_english
Server MSSQL
Server name : MSSQL
Server version : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Language : us_english
ASKER
I've altered my connection string to use the one that "myLittleAdmin" advised:
ConnectString="Provider=sq
I'm still getting the same issue. How can I modify tables or data to change the name of the owner? is there a query or something I can send out or issue to do this for me?
any other ideas?!
ConnectString="Provider=sq
I'm still getting the same issue. How can I modify tables or data to change the name of the owner? is there a query or something I can send out or issue to do this for me?
any other ideas?!
So you are using ole DB for SQL Server.
To add your user as db_owner to the DB launch this script:
USE [database name] -- same as initial catalog
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [newuserid]
GO
this should be enough.
To add your user as db_owner to the DB launch this script:
USE [database name] -- same as initial catalog
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [newuserid]
GO
this should be enough.
ASKER
USE [db221152097]
GO
ALTER AUTHORIZATION ON SCHEMA::[oldUSER] TO [newUSER]
GO
I ran the above command and it completed successfully, but the owner is still listed in tables as oldUSER. Am I doing something incorrectly? I'm submitting this into "Query Analyser"
GO
ALTER AUTHORIZATION ON SCHEMA::[oldUSER] TO [newUSER]
GO
I ran the above command and it completed successfully, but the owner is still listed in tables as oldUSER. Am I doing something incorrectly? I'm submitting this into "Query Analyser"
ASKER
this is the error I get from FireFox when trying to load the page:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/inpages/products.asp, line 25
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/inpages/products.asp, line 25
ASKER
I get the below error when trying to execute those commands. Please advise!!!
Error -2147217900
Object 'ACTNDetails' does not exist or is not a valid object for this operation.
EXEC sp_changeobjectowner 'ACTNDetails', 'dbo221152097'
EXEC sp_changeobjectowner 'AColour', 'dbo221152097'
EXEC sp_changeobjectowner 'AHangerTypes', 'dbo221152097'
EXEC sp_changeobjectowner 'ACategories', 'dbo221152097'
EXEC sp_changeobjectowner 'AResin', 'dbo221152097'
EXEC sp_changeobjectowner 'AR_Products', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerColour', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerResin', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailers', 'dbo221152097'
EXEC sp_changeobjectowner 'dtproperties', 'dbo221152097'
Error -2147217900
Object 'ACTNDetails' does not exist or is not a valid object for this operation.
EXEC sp_changeobjectowner 'ACTNDetails', 'dbo221152097'
EXEC sp_changeobjectowner 'AColour', 'dbo221152097'
EXEC sp_changeobjectowner 'AHangerTypes', 'dbo221152097'
EXEC sp_changeobjectowner 'ACategories', 'dbo221152097'
EXEC sp_changeobjectowner 'AResin', 'dbo221152097'
EXEC sp_changeobjectowner 'AR_Products', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerColour', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerResin', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailers', 'dbo221152097'
EXEC sp_changeobjectowner 'dtproperties', 'dbo221152097'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Generally localhost will suffice - maybe try this first.
DM