February 15, 2009 at 8:58 am
Could anyone tell me that the procedure to follow for a backup transfer from Server01 to Server02 ? Cause I have a situation like this and the application does not recognize the new copy on Server02. The users on the Server02 are not the same as the Server01 Thank you. (SQL 2000)
February 15, 2009 at 9:34 am
francis (2/15/2009)
Could anyone tell me that the procedure to follow for a backup transfer from Server01 to Server02 ? Cause I have a situation like this and the application does not recognize the new copy on Server02. The users on the Server02 are not the same as the Server01 Thank you. (SQL 2000)
steps to transfer backup
1. take backup on server01
2. restore it on server02
OR detach the database on server01, copy it and attach it to server02.
Also, what do you mean by application not recognizing the new copy on Server02? is the application properly pointed to the new server? Does the application has been hardcoded/stored in some files with the details of the server01? this could be one reasons of the application not recognizing server02.
What do you mean by users on server02 are not as Server01? if users on server02 have their access defined, they should be able to access the server.
February 15, 2009 at 2:14 pm
Aapka Dhanyavaad !
I have done the same path you mentioned. I could open everything the database under Server02, and the only left to be done is the application recognized ONLY the original database ID. Therefore it does not take the database under Server02 although data is 100% the same. Thank you again.
February 16, 2009 at 8:33 am
The steps to follow are
Please test the follwoing in a non production environment before doing it for real.
1, backup the database on server 1
2, Copy the backup file onto server 2
3, Check the locations for the data and log files on each server. Are the the same?
4a, If the data and log files are held in different locations on server 1 and server 2, restore the database on server 2 using the "With move" option.
4b, If the data and log files are held in the same locations on server 1 and server 2 simply restore the database
5, Execute the following on server 1 and server 2 "select dbid, name from sysdatabases where name = [Your database name]". There is a very good chance that the dbid will be different on server 1 and server 2. If your application is using the dbid to identify the database I reckon that this will need to be changed in the application
6, (Assuming your are using SQL logins) Check for orphaned users, the process of using backup and restore to move databases from one server to another will create orphaned users. This is where the login name exists in master.dbo.syslogins and [Your database name].dbo.sysusers, but the sid in each table is different. Execute the following in query analyser to identify orphaned users
select
u.name
from master.dbo.syslogins l right join
[Your database name].dbo.sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema')
7, If orphaned users are identified execute the following to create statements to either fix or delete the logins
-- -- Section 3: Create local variables needed
--
declare @CNT int
--
declare @name sysname--char(128)
--
-- declare @sid varbinary(85)
--
declare @cmd nchar(4000)
--
-- declare @C int
--
-- declare @hexnum char(100)
--
declare @db varchar(100)
--
--
--
set @db = --[Your database name]
-- drop table if it already exists
if (select object_id('tempdb..##orphans')) is not null
drop table ##orphans
-- Create table to hold orphan users
create table ##orphans (orphan varchar(128))
-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
set @cmd = 'insert into ##orphans select u.name from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and
issqlrole <> 1 and isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)
-- Are there orphans
select @CNT = count(*) from ##orphans
WHILE @CNT > 0
BEGIN
-- get top orphan
select top 1 @name= orphan from ##orphans
-- delete top orphan
delete from ##orphans where orphan = @name
-- Build command to drop user from database.
set @cmd = case
when @name not in (select name from master..syslogins) then 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
when @name in (select name from master..syslogins) then 'EXEC ' + rtrim(@db) + '..sp_Change_Users_Login ''UPDATE_ONE'','''+ rtrim(@name) + ''', ''' + rtrim(@name) + ''''
else @name + ' Investigate'
end
print @cmd
--exec(@cmd)
-- are there orphans left
select @CNT = count(*) from ##orphans
end
February 16, 2009 at 9:03 am
francis (2/15/2009)
I could open everything the database under Server02, and the only left to be done is the application recognized ONLY the original database ID.
If you're sure it's the DB ID that's linked with your application, i'd suggest u find the original DB ID. ideally the number should be greater than 4...
DB ID is sequential number assigned to databases as and when they're created.. If your original DB ID is 8, i'd suggest create 3 temp dbs like testdb1, testdb2, testdb3 which'll have DB IDs as 5, 6 and 7 and then restore your original database... it'll have dbid of 8...
you can then drop test databases....
February 17, 2009 at 8:44 am
Thank you guys for the help. I would test it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply