March 2, 2009 at 2:51 am
Im upgrading my SQL2000 DB's to SQL2005.
If I want to take the existing data would it be best to:
1. Detach the DB and copy over the LDF/MDF file and reattch it to the new QL2005
2. Take a backup of the existing SQL2000 DB and restore on SQL2005
Regards,
March 2, 2009 at 3:21 am
You can use any of both options for user databases. Once you get database in SQL Server 2005 then check its compatibility and set it to 90 for complete SQL server 2005 compliance. If you have to upgrade all databases then consider using upgrade option for your SQL Server 2000 instance.
DBDigger Microsoft Data Platform Consultancy.
March 2, 2009 at 3:47 am
Either will work. Check compatibility before you move to 2005 (test restore, update advisor) as there's stuff that breaks even in compat mode 80.
Once on 2005 update all statistics, run DBCC updateusage and run DBCC CheckDB with the DATA_PURITY option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 4:36 am
Thanks guys.
Question, what do you mean by:
Compat mode 80/90?
How would I:
update all statistics on 2005
run DBCC updateusage
run DBCC CheckDB with the DATA_PURITY option.
March 2, 2009 at 4:57 am
A Little Help Please (3/2/2009)
Question, what do you mean by:Compat mode 80/90?
Look up Compatability level in Books Online
How would I:
update all statistics on 2005
Look up UPDATE STATISTICS in Books Online
run DBCC updateusage
Look up DBCC UPDATEUSAGE in Books Online
run DBCC CheckDB with the DATA_PURITY option.
Look up DBCC CheckDB in Books Online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 7:08 am
ok i get the hint!!!!!!
March 2, 2009 at 8:36 am
and then ...look up books online in books online...
Sorry Gail, I know I'm not supposed to read your mind :D.
March 2, 2009 at 11:07 am
The difference you have between detach / attach and backup /restore is that for the first way, you will lose the cache, which is not the case when you RESTORE a database
For updating statistics on all tables of the database, I'd rather prefer to use the system stored procedure sp_updatestats
@++ 😉
March 2, 2009 at 12:30 pm
Ninja's_RGR'us (3/2/2009)
and then ...look up books online in books online...Sorry Gail, I know I'm not supposed to read your mind :D.
I wish I could .... I'd know a lot more about SQL !
March 2, 2009 at 12:41 pm
Not really, because by then you wouldn't have to know as much, just ask Gail and read her mind.
Much faster than searching in Bols.
March 2, 2009 at 12:57 pm
ElSuket (3/2/2009)
The difference you have between detach / attach and backup /restore is that for the first way, you will lose the cache, which is not the case when you RESTORE a database
What cache are you talking about?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2009 at 4:01 pm
If detach/attach or backup/restore after completion - check for mismatch and orphan ids fix it , Default database might also have changed if database is moved to another location . Verify the database properties to be in safer side
March 2, 2009 at 8:46 pm
The answer is "it depends". I've done both migrations and upgrades to SQL 2005/2008 and each one of them was a bit different. Are you going to a new box, or keeping the same server?
There are some good suggestions here when you get it moved, but also consider a checklist to put together before the migration or upgrade: 1) dts packages 2) users and logins 3) connections stored on the server 3) any link servers 4) jobs 5) replication or log shipping 6) maintenance plans, etc. etc.
Good luck,
Lee
March 3, 2009 at 3:24 am
I have a preference that I cannot justify for restoring from backup rather than copying and attaching files. I feel confident about a file produced by a backup that is known to have completed successfully, and similarly confident about the state of a database successfully restored from such a backup. I think I imagine that SQL Server is doing the processing rather than a simple OS file copy, so there is more chance of a problem being picked up - and more reassurance when SQL Server has no errors or problems. This is probably just a superstitious misconception - can anyone comment?
In any case, other good reasons for restoring from backup are that (i) backups are usually to hand for a properly-administered system (ii) restoring backups is good practice and (iii) restoring backups establishes that they are sound.
March 3, 2009 at 4:49 am
Ewan Hampson (3/3/2009)
I think I imagine that SQL Server is doing the processing rather than a simple OS file copy, so there is more chance of a problem being picked up - and more reassurance when SQL Server has no errors or problems. This is probably just a superstitious misconception - can anyone comment?
As long as you detach the database cleanly before trying to copy, there's not problems. If you just shut down SQL, copy the files and then try to attach those, there is a potential problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply