restoring database from one server to another?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

restoring database from one server to another??

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-07 : 08:49:45

Hi!!
I need your help...
I have two sql server 2000...now i want to restore one database from one server to another...i took the full database back up and try to restore it on another server but it gives me error regarding .mdf and .ldf files..i created the database on another server having same name....let me know what procedure should i adopt so that database from first server restore successfully on another..also which precausion should i take so that all users and logins installed successfully on another server...i am taking full database strategy...I wanna to do it through EM....one more thing i am accessing both the server through Remote Administrator....I am getting location path error...

my file for DB on server 1st is
E:\mssql\data\test_data.mdf and
E:\mssql\data\test_log.ldf and on 2nd server where i wanna to restore it is on
C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_data.mdf and
C:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.mdf

please help in how i can manage to get proper path???

T.I.A

Papillon

kpsreenath
Yak Posting Veteran

52 Posts

Posted - 2006-03-07 : 09:44:14

Best way to migrate a database from one server to another is the backup restore strategy.

Here is the list of things which you need to take care during the migration
http://support.microsoft.com/kb/314546/en-us

while restoring, whats the error you are getting?

Hope this helps

Thanks
Sree

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-07 : 09:59:40

If it's not in Sree's link...
http://support.microsoft.com/kb/246133/

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-07 : 11:49:25

hi!!

i restored my DB to my destination server!! but as i logon i am getting err like

unable to connect to server ABC:

server:Msg 18452,level 16,State 1
[Microsoft][ODBC SQL Server Driver][SQL Serve]Login failed for user 'xyz'.Reason: Not associated with a trusted SQL Server connection.

i checked on destination server, where my user and login is same as my source server except in user [login name]i saw null in front of user 'xyz' where as in source server there is user [login name] 'xyz' in front of user 'xyz'......

so how can i resolve this problem????

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-07 : 12:02:52

EXEC sp_change_users_login 'Auto_Fix', 'xyz'

Run that in the master database..

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-07 : 14:05:27

hi

i used all the stuff for resolving my login problem but i am unable still i am gettig the same error

unable to connect to server ABC:

server:Msg 18452,level 16,State 1
[Microsoft][ODBC SQL Server Driver][SQL Serve]Login failed for user 'xyz'.Reason: Not associated with a trusted SQL Server connection.

i used Transfer Logins Task to tansfer all my login then i used
EXEC sp_change_users_login 'Auto_Fix', 'xyz' to fix the orphaned user it gives me 0 update also i tried for
sp_change_users_login again with the Update_One option but no use :(

can any body help me out

T.I.A

Papillon

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-07 : 16:22:29

hi!!
my one of the forum friend got solution n that is

Go to the properties of the server where you did the restore, under the Security Tab and Authentication, try selecting the SQL Server and Windows radio button if it is not already selected.

thanks to all

Papillon

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-08 : 08:00:13

So you was in Windows authentication mode only?

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-03-08 : 12:23:18

hi..
there just one thing i wanna to know is that..

i restore only one DB from one server to another..ok..here i transfer two users associated with that DB..one is 'xyz' and another is 'dbo'....then i created login by
-- Server1
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'xyz'
--Results
------------------------------------------------------------------
0x2131214A.......

(1 row(s) affected)

-- Server2
EXEC sp_addlogin 'xyz', 0x2131214A.......,
@encryptopt = 'skip_encryption'

i granted defalut DB (that i restored) access to login 'xyz'

ok..then i search for orphaned user by..
sp_change_users_login 'report'
i got two..one is 'xyz' and other is 'dbo'..
now i fixed 'xyz' by....
sp_change_users_login 'auto_fix','xyz'

again i fired ...
sp_change_users_login 'report'
i got DBO as orphaned....
so i changed DB owner by ...(as given in BOL)

sp_changedbowner 'sa'

now there is no more orphaned user!!

but i still couldnot logged to QA by login 'xyz'..it gives error me logging failed for user 'xyz'..

then i opened SECURITY>>>Logins>>> right click on 'xyz' and manually type same password..and now i logged on QA with that login name 'xyz' and with same password...but when i checked again on 2nd server with

SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'xyz'

then here i am getting different password than my 1st server password..now i can access DB but that different password makes any difference???? and how i can check my whole efforts is worth???
or is m i missing some steps????

T.I.A

Papillon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources