Restore from SQL2000 database - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Restore from SQL2000 database

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2007-08-02 : 17:30:16

Hi all

We have a lot of SQL2000 databases and would like just backup and restore to SQL2005.

Is there anything that I need to look/aware at it apart of DTS stuff (we are not using much DTS).

Does the Level80 sets on SQL2005 for each database will have backward compatibility with SQL2000?

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 17:56:57

>>Does the Level80 sets on SQL2005 for each database will have backward compatibility with SQL2000?

You need to set it to level 90 if you want to use any features of 2005. It depends on what you mean by backward compatibility. Once you change the level there's really no direct way to go back to 2000. You can set up some processes as a backup strategy.

Also, search these forums. Not sure if its Mladen or Jeff that posted a way to get back to 2000 after upsizing to 2005.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-03 : 08:33:06

Do you know how to reset the Login names

If i restore sql 2000 to another server with SQL 2000 i run this
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs

When i do restore and backup to SQL 2005 the
users are created but the login name missing.

How to do the orphans

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-03 : 11:05:23

If you need those sql logins, create them on target server then remap to db users with sp_change_user_logins. Otherwise, delete orphans from target db.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-03 : 11:24:43

The script i have does not work on SQL 2005
So i was wondering what to run on SQL 2005 for orphans

Yes i need all the logins.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-03 : 11:44:05

Have to create sql login first.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-03 : 14:22:20

The user name under security users in the database does come across when you right click on that user there is the username and the login name is blank (This happened in SQL 2000 when restored)...I just used to run the orphan fix.

So i was wondering how you run the orphan fix in SQL 2005

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-03 : 23:20:11

Same as on sql2k, remap db user to existing sql login in both cases.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-04 : 08:15:44

I had the script before to do all logins,,,i try modify it to work for SQL 2005 so it does all of them automatically. Have over 1000 logins.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-04 : 20:29:37

What's the problem? Did you get all needed commands? Did you get any error?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-08 : 10:16:38

I get this message when i run the script
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sys.sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-08 : 13:43:33

Tried this?

select @command=' sp_change_users_login auto_fix, ' + @usrname

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-09 : 08:37:19

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

Now i looked at the script line 207

if @Password IS Null
begin
raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end

Parameters for proc:

create procedure sys.sp_change_users_login
@Action varchar(10) -- REPORT / UPDATE_ONE / AUTO_FIX
,@UserNamePattern sysname = Null
,@LoginName sysname = Null
,@Password sysname = Null

Its asking for the password but i would not know this......if i just restored all logins from one DB...i just want to fix login name

Is it that SQL 2000 passwords are coming in blank?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-09 : 08:42:09

Ok...i just got it.....(The Logins were not in security logins) Just against the Database.
So i added the user to the Security Logins and password then did the

sp_change_users_login auto_fix, 'username' and that puts back the login name.

So i have to create all the logins first (i try the sp_help_revlogin script) first then restore the database and then run this script to fix all the logins

Gosh.........

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-09 : 08:56:31

No i thought i had it....it works for one single user but not in a cursor loop.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-09 : 22:54:48

Did you create all needed logins first?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources