Backup SQL Server 2000 Restore to SQL Server 2005
-
Hi,
Would it be possible to just simpily restoring the SQL 2000 databases to SQL 2005? A new machine will be used for just SQL Server 2005. I read an article about detaching from 2000 and parking to 2005; however, the SQL 2000 server must stay online and the databases must not be set to read-only as it must not loose production down-time. Would it possible to migrate the 2000 databases over to 2005 without going offline on SQL 2000?
Thank you.
-
Yes it is. If you do this the database on the 2005 server will be in compatibility mode 80.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Hi,
I just took a week class on SQL Server 2005 and this is what I learned. There are four methods to move databases around :
1. Script objects and migrate data
2. Copy Database Wizard
3. Detach and attach
4. Restore a backup.
The harderst but the best is to script objects and migrate data. Scripting objects is easy, migrating data is harder but possible. In this case you will reduce the database size, get rid of the old backups, logs, it will be a nice clean start.
Copy Database Wizard didn't work very well for me because servers should have live connection. Then you cannot use detach and attach because you do not want to put existing db down. Then you can try to make full db backup and restore it on 2005 server.
Hope it helps a little,
Vika
-
Thank you for your answers. Now I can continue with knowing i'm on the right path. Sounds like this will be easier than other upgrades...
-
Good Luck!
-
Mike -
Backup/restore is a good way to approach the migration - but just for user databases, do not try to bring across any of the system databases. You'll also need to address logins, jobs, etc. but those are relatively easy to script (search this site and you'll find plenty of help in that regard)
Joe
-
If you want to migrate users, logins, jobs, you have to backup and restore system databases. Some things you cannot migrate. If you have DTS packages jobs, you cannot migrate them. There is such thing like "legacy" and you can move DTS packages, but if you schedule them, they will not run because they were replaced by SSIS (integration services). You would have to re-write them in SSIS.
-
Restoring system databases is not the only way to migrate users, logins, and jobs. As Joe stated, you can script all of those in SQL 2000 and recreated them in SQL 2005.
DTS 2000 packages can be scheduled and run in SQL 2005 without rewriting in SSIS. I've got dozens of legacy DTS packages running in scheduled jobs in a SQL Server 2005 instance.
Greg
Greg
-
This is what our instructor in class and Microsoft say: "re-write them". We also moved dts packages to SQL 2005 and scheduled them. Now some of them fails to run and somebody's running them manually every day
-
If your database is large and you want to minimize downtime you can also consider this method: bring database off-line, physically dismount hard disks with its data-files (or in case if you have SAN storage, just re-direct switches), mount them on the new server, bring database online, it will automatically ask you to upgrade to 2005 in-place, click yes, and it will run upgrade wizard, usually it will take minutes to run.
In addition to that I would also recommend to perform full backup prior to all this procedure as well as at the end of it.
-
Yeah, I know the recommendation is to convert packages to SSIS, but my point was that you can maintain and run DTS 2000 packages in SQL Server 2005 indefinitely. I don't know why some of your packages fail, I'm sure it's not because they're DTS packages.
Greg
Greg
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply