Restore database to a different server 1

[LinsLo]

All,
I am trying to create a copy of an existing database and place on a different sql server for testing purposes. Currently my method of doing this is to create a backup, then restore a database on my new sql server. But here is the problem I am running into. When I look in Enterprise manager I can see all of the stored procedures and tables and data just fine, which is how I would like it. But, when I open up Query Analyzer I am not able to run any queries because it says "invalid object name" error. I know that the object name is correct. I think that I am having a conflict between the users which were carried over from the source backup file and the users that are on my 2 new sql server. If anyone could help, I would really appreciate it. I am stuck at this point. I am guessing it is probably something simple I am just unaware that I need to do.

Thanks,
LinsLo

 

[gradley]

Do you have the same user names on your 2 new SQL Servers that you have on the main SQL Server? If so, are the passwords different?

If So:

Click on the "Users" tab in Enterprise Manager for the database in question. If you see a blank under the Login Name column then you will need to delete and re-add these users.

** Before you do this, make sure you know what rights the user has because you will need to re-add them.

I have run into this before that when the passwords are different on different servers, the database does not recogize the user permissions.

Hope this helps!

 

[mrdenny]

If the problem is that the users were orphaned as gradley pointed out, you can actually fix them without deleting the user from the database. This code will fix a users account in the database

sp_change_users_login 'Update_One', '[i]username[/i]', '[i]username[/i]'

This code will tell you all the user names that have been orphaned.

sp_change_users_login 'Report'

When you look at the objects that can't be found are the user objects the only ones that it can't find? Try selecting from a system table like sysobjects to see if it can select from there. Who owns the objects that can't be found? Is it DBO or someone else?

When you look in the users list for the database, is dbo there? Is not you'll need to reassign someone as dbo. You can use sp_changedbowner for that.

sp_changedbowner 'sa' /*Or what ever user you want to be dbo*/

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gradley]

Thanks mrdenny,

That's good to know!

 

[mrdenny]

no problem

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Theseekers]

Hi There,

I am about to do this exact thing as you describled. However, I have a different question (very basic). My test server is on an another subnet and does not have a backup tape device. How would you perform this to a bare system (no backup devices)? Can you restore the curren db to a backup point on the net so that I can sort of using the restore feature in SQL Server itself to bring it it.

Current DB is on SQL 7.0 and plan to put it on a SQL 2000.

TIA for your input.

 

[LinsLo]

This is how I ususally do the backup and restore:

1) Back up the database to a file, you can place the file where ever you want

2) Move the file (if needed) to somewhere where the new server will see it.

3) Then, when you want to restore the new database, restore from type device and then browse for your .bak file. Also, go into the options tab and make sure that your data file and log file matches the correct destination which was set up for the new database and new server.