May 1, 2008 at 7:25 pm
I have a Win 2003 Server (32 bit) runing SQL 2000 with one main DB. I have the daily *.BAK file created nightly by the Maintenance Plan. I need to know the proper way to restore/migrate this on a new Win 2003 server (32 bit) running a newly installed version of SQL 2005. I know I'm probably asking alot but I have not had any training with SQL except school of hard knocks. I need some expert advice if anyone can help.
Thanks!
Todd Lubbock,TX
May 1, 2008 at 9:55 pm
You can restore the 2000 db to 2005.
However this doens't move the logins you may need. On this site, you can search for sp_help_revlogin and use that to script out the logins.
sp_change_users_login will help you synch things back up.
TEST, test, test. Restore the db, test it before you let people access it.
May 2, 2008 at 1:57 am
Use the following script in SQL 2005 to check for orphaned users -
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
...... and the following code to correct them -
EXEC sp_change_users_login 'update_one', 'tdb4user', 'TDB4User'
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
May 2, 2008 at 7:15 am
Also be sure to run the Upgrade Advisor to make sure you don't have any issues with objects in the database that are not 2005 compatible.
This showed me 70 vendor provided stored procedures that I had to correct before moving the database to 2005. Simply restoring the database in 2005 will not tell you of these errors until you try to run them.
May 2, 2008 at 9:13 am
Use the following script in SQL 2005 to check for orphaned users -
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND sid is not null
AND sid <> 0x0
AND suser_sname(sid) is null
ORDER BY name
...... and the following code to correct them -
EXEC sp_change_users_login 'update_one', 'tdb4user', 'TDB4User'
You can also use:
EXEC sp_change_users_login 'report'
to check for orphaned users
Gethyn Elliswww.gethynellis.com
May 2, 2008 at 10:04 am
Thank you for your advice!
Much appreciated!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply