SQL Server Performance Forum – Threads Archive

Just a simple question

Hi All, I just want to be in the safe side. So could any one please tell me what are the steps need to follow after the restoration of SQL 2000 Server database onto SQL 2005 Server.
Thanks in advance. Regards
Ashish Kuriyal

---

Hi, What i feel is, u must run DBCC CheckDB first. SOmetimes u may need to drop and recreate indexes and then update the statistics. Madhu

---

– perform full db maintenance :
— dbcc checkdb
— dbcc rebuildindex for all objects
— dbcc updateusage for all objects
— sp_updatestats for your db – implement a backup-schedule for your db – did you migrate your users ?
— remap them the your restored db !
SELECT @sql_stmt = ‘sp_change_users_login @Action = ”Update_One”,@UserNamePattern = ”’ + su.name + ”’, @LoginName = ”’ + msu.name + ””
FROM sys.database_principals su
inner join sys.sql_logins msu
on upper(su.name) = upper(msu.name)
where su.type = ‘S’ – Find out if you can change the dblevel to 90 (sql2005) so you’ll have the full flavour of sql2005 available for your db.
Exec sp_dbcmptlevel @dbname = ‘yourdb’, @new_cmptlevel = 90 ;

---

Thanks for such a perfect and prompt reply. So is there any option using which I can reindex the all indexes available in a perticular databse.

---

DBCC DBREINDEX is the command used to rebuilt the indexes on a table one simple way is, run the following query in a QA window and u will get the script to run DBCC for all the tables in that particular db. copy paste the result into another QA and run. select ‘DBCC DBREINDEX (”’+table_schema+’.’+ltrim(rtrim(table_name))+”’)’From INFORMATION_SCHEMA.tables where table_type=’Base Table’
Madhu

---

Thanks a lot. I guess we created a wonderful document which will help others also. If anyone have further suggestion then please keep updating this topic. Regards
Ashish Kuriyal

---

I have written an article which is under review at present about the Upgrade Issues that are faced during the upgrade and the measure the user need to take…keep watching SSP space in that case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Its really good news Satya.
Please update us once its completed with the proper URL and thanks for contributions/solutions which you provided in the problems.

---

Also check the owner of the d/b, after u restore the d/b u might be the owner of the d/b and if required change it to sa or whatever u like to Satya

---

Looking forward to seeing this article

——————
Bug explorer/finder/seeker/locator
——————

---

Satya, just checking what is the status of your upgrade article, if not ready, do we have a chance to look at the draft? Just curious, how you guys deal with the DTS to SSIS packages? ——————
Bug explorer/finder/seeker/locator
——————

---

Here is one from MS… SQL Server 2005 Upgrade Technical Reference Guide
http://www.microsoft.com/downloads/…d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>