Restoring SQL2000 bkp file on SQL2005

Is it possible to restore SQL2000 backup to SQL2005? Or I have to restore the DB to SQL2000 and then upgrade it to SQL2005?

Can someone provide me ways to upgrade SQL2000 DB to SQL2005?

Thanks,

Hiten

Simply restore it on the SQL 2k5 server. Backups of 2k are compatible with SQL2k5 (but not vice versa). You have to be aware that the internal (meta) structure of the database is upgraded during the restore process and therefore can′t be backuped and restored on the SQL2k after the upgrade.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Is this possible on SQL 2005 Express too? Or I need to have SQL server 2005 ?|||That also valid for SQL Server 2k5 Express.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Restoring SQL2000 after upgrade

I performed an in-place upgrade of my SQL2000 database to SQL2005. I am having too many problems at the moment with compatability issues between SQL 2005 and VS2003 and need to roll back SQL 9.0 to 8.0. Are there any white papers, or published procedures for doing this? I have a tape backup of the 2000 system, but there have been various updates to the databases during the past 3 weeks since the upgrade. The structure of the databases have not changed.

Please help!

There is no vehicle to rollback. You will want to reinstall sql2k as a different instance, restore data from your tape backup and then use data import/export to update sql2k data with sql2k5 data.

If you're looking for a way to mount a sql2k5 database on a sql2k instance, it's not possible.|||

Are you running the SQL Server database in 9.0 or 8.0 compatibility mode? When you upgrade from 2000 to 2005, you will be in 8.0 mode unless you explicitly changed it to 9.0. If you are running in 9.0 mode and having problems you might try switching back to 8.0 and see if that helps as a temporary measure. What kind of problems are you having?

If you are already in 8.0 mode and still having problems that you cannot work through, then I would install a fresh copy of SQL Server 2000 (on a different server), apply SP4 and any hotfixes, and then restore from your tape backup. Then, you can use SSMS from SQL Server 2005 to connect to both servers are start trying to get your data back in synch on your three week old backup.

You can use "DataDude" (Visual Studio 2005 Team Edition for Database Professionals) to to compare the data between the two databases, and generate scripts to help you get the old copy up to date.

|||

I am running in 9.0 mode. The main problem is that all my applications are written in VS2003 and when I need to change a data adapter, I have to manually code the insert/update and delete methods rather than having VS generate the automatic code, which is a pain.

These applications manage extensive databases and there are probably a hundred data adapters involved, granted, most of them never change, but it makes maintenance a bear when they do. I know that the DB operations are generally more efficient if you code those methods manually, but the applications are used by only a handfull of people (20 or so) and I doubt the increased processing efficiency would be noticable vs. the ease of letting VB auto-create the code. Plus, I think the 2000 enterprise manager is much more intutitive and much faster than the the SQL2005 studio. Call me old fashion, but it strikes me that the 2005 Studio has so many bells and whistles it's become bloated and sluggish.

I've now re-installed SQL2000 as a separate instance and applied SP4, restored the data from tape and am about to copy the 2005 data to 2000. When all is well, I'll uninstall 2005.

At some point I will bite the bullet and migrate my applications to VS2005 and then it will probably make sense to upgrade SQL to 2005 as well. For the moment, I'll stay a version behind. Thanks for your comments.

Restoring Replicated DB to NonReplicated Server

Hi all. I am working on a SQL 7.0-2000 upgrade testing/planning
scenario. My question is: Is there any way to restore a backup of a
published DB to a non-replicated server?
When we go live, I plan to drop all of the subscriptions and
publications before restoring the databases onto the new SQL 2000 SP4
servers, but for testing, I want to take a backup of the live
replicated database and restore it onto a test server without any trace
of replication. Alternatively, is there an easy way to remove all
traces of replication (i.e. replication tables/triggers/etc.) once I
have restored the backup of the replicated DB onto the test server?
Any help/advice is greatly appreciated.
Thanks,
Peter
Peter,
most remaining replication objects can be removed using
sp_removedbreplication. The conflict tables will be left hanging and a few
views, but it should be 99% clean after this.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Will this get rid of the replication triggers as well? That is what I
am most concerned about. Also, not sure if it makes any difference, but
I am using merge replication.
Thanks,
Peter
Paul Ibison wrote:
> Peter,
> most remaining replication objects can be removed using
> sp_removedbreplication. The conflict tables will be left hanging and a few
> views, but it should be 99% clean after this.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||I tried to restore the SQL 7.0 replicated database onto SQL 2000 SP4,
but the restore fails with a "Invalid column name 'excluded_cols'."
error.

>From what I found in a quick Google search, it sounds like this is
probably due to replication. My question is: Is the database fully
restored at this point and I can just run sp_removedbreplication? Or
will I have to find space on a SQL 7.0 server to restore the replicated
database, then run sp_removedbreplication, then back up that database,
then restore it to SQL 2000?
Thanks,
Peter
|||Peter - yes it'll remove the replication triggers.
Cheers,
Paul Ibison
|||Is the database visible in the sql server 2000 instance? If it is,
sp_removedbreplication should do the trick. If not, restore the backup on
the SQL Server 7.0 instance as a different name, remove replication from
this database, back it up then restore on the sql 2000 instance.
HTH,
Paul Ibison