How do you go about converting SQL 2000 databases to SQL 2005?

[sonname]

I have a number of SQL Server 2000 databases that need to be converted over to SQL Server 2005. How do you go about doing the conversion, do you do a detach and attach, a backup and restore or do you use DTS to bring over tables and stored procedures? Thanks in advance.

 

[ptheriault]

The detach and attach method will be quickest but if you can't take the db off-line use the backup and restore method.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

but if you can't take the db off-line

Uh, system upgrades require that the database go offline.

When doing a production upgrade you have to take the database offline and do a detach and attach otherwise there is no guarantee that records won't be changed between the backup starting and the restore finishing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog