September 27, 2010 at 4:40 am
Hi,
I need advice on to upgrade one of the small system from 2000 to 2008.
Should I go ahead with detach/attach
or should I take the bkp and restore them on 2008 and then sp_help_revlogin to migrate the logins.
Please suggest.
PS:- System is very small in compare to size and use and free for upgrade at any time. Also system is not in same domain and have no DTS, no linked server.
Quick suggestion will help me to create strategies.
----------
Ashish
September 27, 2010 at 6:34 am
found one good article from MS website :-
having 490 pages but extracted the information relevant to my upgrade.
Can someone please review my steps :-
1.1.1.1Backup/Restore Upgrade Method
Take the following steps to upgrade a user database by using the backup/restore upgrade method:
1.Back up the database to be moved from the SQL Server 2000 or SQL Server 2005 instance by using either SSMS or the BACKUP DATABASE Transact-SQL statement.
2.Use SSMS to connect to the SQL Server 2008 relational database instance to which you want to restore the SQL Server 2000 or SQL Server 2005 relational database.
3.Restore the relational database from the backup file, changing the database or file names and locations as necessary.
Important: You must manually move to the SQL Server 2008 instance the master and msdb database objects related to the database being upgraded (for example, logins, jobs, alerts).
1.1.1.2Detach/Attach Upgrade Method
Take the following steps to upgrade a user database by using the detach/attach upgrade method:
1.Detach the database to be moved from the SQL Server 2000 or SQL Server 2005 instance by using SQL Server Enterprise Manager, SSMS, or the sp_detach_db stored procedure.
2.Copy (or move) the detached data file(s) and log file(s) to the new server.
3.Attach the copied data and log files to the SQL Server 2008 instance by using SSMS or the CREATE DATABASE Transact-SQL statement with the FOR ATTACH or FOR ATTACH_REBUILD option.
4.Optionally, if you copied the original data and log files, reattach the original data and log files to the previous instance of SQL Server 2000 or SQL Server 2005.
Important: You must manually move to the SQL Server 2008 instance the master and msdb database objects related to the database being upgraded (for example, logins, jobs, alerts).
1.1.2General Post-Upgrade Tasks
Whether you are doing an in-place upgrade or a side-by-side upgrade, you must execute the following post-upgrade steps:
1.Execute DBCC CHECKDB WITH DATA_PURITY to check the database for column values that are not valid or are out of range. After you have successfully run DBCC CHECKDB WITH DATA_PURITY against an upgraded database, you do not have to specify the DATA_PURITY option again because SQL Server will automatically maintain "data purity." This is the only DBCC CHECKDB check that you must run as a post-upgrade task.
2.Execute DBCC UPDATEUSAGE on all attached databases to update usage counters and make sure that correct values exist for table and index row counts.
a.You can use the following statement to enable AUTO_UPDATE_STATISTICS:
b.ALTER DATABASE <Database Name> SET AUTO_UPDATE_STATISTICS ON
c.You can then use either the sp_updatestats stored procedure or the UPDATE STATISTICS command to update statistics immediately to ensure optimal performance.
3.Update statistics on all databases after you upgrade them. Execute UPDATE STATISTICS in user-defined tables in SQL Server databases.
4.Repopulate full-text catalogs. For information about this task, see Chapter 6, "Full- Text Search."
5.Make sure that the relational databases are working correctly by executing a sample set of queries.
6.Update any scripts affected by SQL Server 2008 behavior changes.
You can determine the compatibility level of your databases either by right-clicking the database in SQL Server Management Studio and selecting Properties and then Options, or by executing the following script:
SELECT name, compatibility_level FROM sys.databases
ALTER DATABASE <Database Name> SET COMPATIBILITY_LEVEL = 100
----------
Ashish
September 27, 2010 at 6:56 am
Big system or small, the question for me when upgrading the system is, how can I do it safely? What's the best method for protecting the business' data?
The answer to that question is to do a side-by-side upgrade. This means having two servers (physical or virtual, doesn't matter), the original running SQL Server 2000 and the new one running SQL Server X (2005,2008, 2008 R2, 10, vNext, doesn't matter). Then you use the backup and restore method. Why? Because, this approach is the safest way of doing the upgrade. If something goes wrong during the process. If there's some sort of snag, the new server is messed up in any way, the old server is still sitting there, ready to work. Any other approach, in place upgrades, detach, attach, can result in the old server either being offline or, worse yet, broken. Also, with the ability to restore to a point in time from logs, you can seriously minimize the down time of the servers as you make the migration occur.
Those are my feelings on the subject. People can honestly disagree, but I think it's the approach most taken.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2010 at 8:24 am
Thansk for your feedback.
----------
Ashish
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply