Backup SQL 2000 SP4 and Restore on SQL 2005

Does anyone know if this works to where the data is viewable/usable?

I have an application that takes datasets out of the SQL database. We decommisioned our old SQL 2000 SP4 machine and brought in a SQL 2005 Server. What we did was backed up the old database and then restored it on the new Server.

Now the application does not see the old datasets but will collect new ones that are viewable. We can import older datasets but we have nearly 27 GB of data, which our DBA can see the old tables in Enterprise Manager but our applciation can't see it.

I'm thinking it might be a schema issue or the way that 2005 translate the odler 2000 SP4 database. Does anyone know, or have they run a back up on a older 2000 Server and then restored it to a 2005?

Thank you.

David

Can you see the data inside the new management studio.

|||

Yes. But not within the other application

My main question is has anyone tried to run a Backup of a SQL 2000 SP4 and restore in a SQL 2005, and was the data accessible?

|||Hi,

could be a owner / schema issue, depending which owner / schema the objects had and now have. The best thing would be to analyze what the application tries to query using the profiler. There you would see which user is trying to gain access to the database through the application and which objects are tried to access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Backup Maintenence plan question

I have recently installed some maintenace plans to perform database backups for an application database. The database is in full recovery mode so I have setup a full backup to be run once per day and then transaction log backups to run every 2 hours throughout the day. My question pertains to the coordination within SQL Server with respect to the database backup and the transaction log backups.

Let's say the full database backup is in progress and at the same time a trasnaction log backup is fired. What happens? Does SQL Server just skip the tran log backup since it won't be needed? or does it do the backup of the tran log anyway (in case the backup fails).

Another question would be when is it safe to delete the transaction log backup target file? Can it be assumed that after a SUCCESSFUL full backup that the transaction log backup can be deleted?

Since I have seperate maintenance plans in place here I am really trying to find out how/if these tasks are integrated within SQL Server. I'm more familiar with Oracle and the use of RMAN. Specifically for cleanups, RMAN knows what is safe to delete and what isn't and so it will keep a backup on disk if it feels it is needed. Is there similar functionality in SQL Server?

Hello,

You're correct: a full backup will block a tran log backup (until the blocking backup cmd completes), and vice-a-versa.

Following a successful full backup, you can safely delete all tran log backups taken prior to the full backup.

There is no equivelant RMAN functionality provided by SQL Server. There is, however the EXPRIEDATE and RETAINDAYS clause you can make use of in the backup command. Check out BOL for more info, but it may help you...

Cheers,

Rob

|||

Actually, that was the case in SQL 2000.

In SQL 2005, you can concurrently run database backup and tran-log backups. The tran-log backup would simply contain the range of LSNs that span the time of the full backup. It would be used as the starting point for rolling forward from restoring that full backup.