Restoring 2005 Backup With 8.0 Compatability Mode On 2000 - Help
Feb 26, 2007
Hi,
I have a database that is in the SQL Server 2000 compatability mode on my SQL 2005 server. I am trying to restore a backup of this database on my SQL Server 2000 database on another server and keep getting strange messages. First trying Red-Gate and then plain SQL Server with no luck.
It seems as I remember that the 7.0 and 2000 compatibility issue between backups did not arise when the compatibility mode was set to 7.0.
Is this "planned" behavior or an "undocumented feature"??? Anyone else have success doing this?
Hi,We currently have a fairly old product, which was originally onlycompatible with SQL 2000.When we upgraded our server to SQL 2005, the client product gave us a"This product is not compatible with this version of SQL server"error.We put the server into SQL 2000 compatability mode, and things worked.Then I tested a Windows Vista workstation client. The error returned.I realise that if we are simply dealing with a client incompatabilitywith Vista, then I'm in the wrong forum, but what I'm hoping is thatsomeone knows anything server side, perhaps where SQL 2000compatability mode becomes ignored for some reason, when the client isrunning Vista.The vendor pretty much no longer exists, and nothing I've been able todo with Vista's compatability modes has been helpful so far.
I did a backup of a SQL2000 database (named Winstis) using the 2005 management studio. I then created a blank database on my 2005 instance (named Winstis). I then tried to do a database restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different error:
System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:Program FilesMicrosoft SQL ServerMSSQLData est.ldf'. (Microsoft.SqlServer.Smo)
Also the 2005 instance database is on C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataWinstis_Data.mdf
and the 2000 instance is on D:Program FilesMicrosoft SQL ServerMSSQLDataWinstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005 with all tables, store procedures, and functions?
Hi all,I'm having a problem restoring backup from 2000 to 2005. The actualrestore process went ok. However, the database user of the restoreddatabase is missing a Login Name. I've made sure that the Login Nameis created in 2005. Also tried deleting the user, but couldn't theuser is db owner. Is there any way that I can assign a Login Name forthis database user or maybe drop this user and assign another one?Regards,Gerd
I upgrade to sql 2005 from sql2000. The problem is that the compatability mode 90 is not available, 8.0 and 7.0 are, when looking at the compatability level in the properties tab. THe GUI, Management studio is loaded and operational.
What do I need to do to make the 90 compatability mode available?
Hi,I am working in a sql server 2000 environment but my customer uses sql server 7. Is it possible to use backup and restore to transfer the database or should I set up a sql server 7 for development?The database only contains tables and views...ThanksKoen
I am new to SQL server and I need your advise on the following .
1. We have two SQL 7.0 Databases at a distant location and we want to move those databases to our location.
2. we plan to take the SQL backup of the databases in tape ( 4Gb and 2 Gb in size) , bring them here, restore them to SQL 2000 server. - Is this possible , DO SQL 7.0 backups are restored on SQL 2000 without any problems ?.
Also, we plan to take log backups of the DBs at the source after the initial full backups and apply them at the target SQL 2K databases till we cut over to the Target Databases.
Ehat would be the best method of doing this? All your suggestions , pointers to real life scenarios like this, solutions are welcome.
Please let me know if you need any more informations , before suggesting any solution.
Hello,Recently I've tried to restore backup made on SQL Server 6.5.Unfortunately, the only version of SQL Server I currently possess is SQLServer 2000. I have access only to that backup, because I've made it afew years ago, and the main db file doesn't exist any more ;( I would begrateful if you could help me a little ;)RegardsR.Kruk
When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:
The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)
The backup is made from an SQL Server 2000 database called 'UpperBridge'
I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.
I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file. Based on the data selected I will manage the further functionality such as restoration of the backup file. Is it possible to read some data from MS SQL 2000 backup file without restoring? If yes then please let me know how?
i'm trying to set up one of the asp.net website starter kits and part of the process is to backup the sql express db and then import it into the sql 2005 at the web hosting account. it's not working and i'm wondering if there's any difference between the backups they make. the sql express makes a file with a .bak extension and the sql 2005 at the host makes a file with a .sqlbak extension. i was assuming that was just a naming thing and not a structural thing but the host's tech support has said: "This sort of thing happens when the database being imported is not properly formatted. You can try downloading and using sql express from microsoft to see if you can do it manually, but most likely you will need to save your database again properly set for import to an mssql server rather than however you did." all i had done was rename the .bak file from express to .sqlbak as that was the choice for backup files. i will note that it also failed importing the .mdf, which works fine locally with the site but i did make some changes to it from the original that came with the starter kit. should a backup file made with sql express (using sql management studio express, by right clicking on the file and choosing tasks > backup) normally be importable by sql 2005? or, i suppose, does it make sense that a db that works fine locally with sql express (within a visual web developer express project anyway) would actually be "formatted" in a way that would cause it to fail to import into sql 2005? thanks much for any assistance.
I am currently having a problem with restoring a backup made on SQL 2000 XP machine onto a Windows server 2003 machine with SQL Express 2005. I am getting the following error
"System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesAvidian TechnologiesProphetavidian.mdf'. (Microsoft.SqlServer.Express.Smo)"
I'm not sure what is going on becuase I have no issues installing the backup on my XP Pro machine that has SQL Express 2005 Dev edition. So what I did was restore the backup on to SQL Express 2005 on my dev machine and backup again. Unfortunately when I took the newly created backup and tried again to restore the database on to my window server 2003 machine it failed again. Exact same message was displayed.
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
I need to know if Microsoft supports SQL Server 2000 running on the same server that functions as a Terminal Server (both NT and Windows 2000).
I checked out Microsoft.com and found that SQL Server 7 is supported with NT Terminal Services but can't locate any compatability information SQL 2000.
PS: I know this configuration doesn't make sense from a performance perspective but my customers will want to do this anyway.
I'm having an issue with stored procedures after restoring a db from 2000 -> 2005.
If any stored procedures isnt owned by dbo (eg tomsqluser.updateItems rather than dbo.updateItems) then it just wont work and Studio Management Server doesnt give the permission to change it to dbo.
hi there, I've got a problem while restoring a DB from sql server 2005 to sql server 2000 I always got an error message 3169. Please I want if it's possible to make it, it urgent.
Hi Guys, I have two servers at my site out of which one is our production server (on SQL 2000) and the other being a reporting server (SQL 2005). Now the question i have are the following..
Q1) Can i restore an hourly differential backup of my SQL 2000 to SQL 2005? Q2) If yes, are there any specific way i need to go about it other than the conventional Restore with NoRecovery option? Q3) If the answer is no to Q1, then is there any 3rd party software that does this(restoring differentials from 2000 to 2005)?
I had tried this once some time back and at that time it failed to keep the database in a state(norecovery) where in it would accept timely differential db restoration.
I have a question about change of compability. I want to change compability level from 2000 to 2005 on a very large database with a lot of indexes. I have heard that if I change compability level the indexes will no longer be usable. Is it enough to just rebuild all indexed after I have changed compability level or do I have to do something else?
We are experiencing major performance issues after restoring SQL Server 2000 db into newly setup hardware environment. The application is faster on another environemnt running on Windows 2000 server with SQL Server 2000.
We have replicated exact same hardware setup, but only fifference is we are running latest version windows server (2003) and latest version of SQL Server (2005). So far we have troubleshooted the bottleneck to be one of the trigger that is inserting data into a table in one of the stored procedures. The same SP takes 9 mins to execute whereas on our env it takes around one hour. If I disable the trigger it executes in 2 minutes.
After restoring, we have executed
exec sp_updatestats
DBCC UPDATEUSAGE
DBCC DBREINDEX all tables
Set Compatibility level to SQL SERVER 2005 (90).
Now we are installing latest Service Pack for both OS and SQL Server.
Is it possible to restore a SQL 2000 backup with Binary Order collation to a new SQL 2005 installation with Dictionary order, case-insensitive. Is this something I can do during the restore process or would I need to change it after?
I tried to restore a database which I backuped from MS SQL 2000 to SQL Express 2005 but it always failed with the following error message. What could be the problem?
Restore failed for Server 'SERVSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
Additional information
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'gtp' database.
I€™m having a problem setting up maintenance plans on one of our SQL 2005 servers but first here€™s a bit of back ground.
The server is a Virtual machine running win2k3R2 SP2 server std and SQL 2k5, A supplier arrived to install and app on the server when discovering there app didn€™t work with 2k5 they seem to have set the 2 dbs on the server into 2k compatibility mode and set the entire SQL install to think its a 2000 install. (I€™m not sure how this is done all I can see is the wrong version number in management studio) The problem this is giving me it that the folder in management studio for Database Maintenance Plans is no longer under the management folder instead it under a sub folder called Legacy (which is how management studio seems to deal with all registered 2000 instances) and as such a can no longer right click and create new database maintenance plans is there any way around this or will I have to setup my backup job manually?
We have a set of databases some are fully read-only others have read-only file groups, is there any way to restore backups of these taken on an MSDE 2000 to an SQL Express 2005 instance?
When doing the inplace upgrade we change these to read-write before the upgrade and set them back after the upgrade.
These databases are used in the field by customers althought the controlled upgrade requires a backup before (and blocks if it fails) and tries a backup after if the post upgrade backup fails (due to disk space) we might need to recover from this odd situation.
The only solution I have is install MSDE some place restore to this then do the controlled upgrade again, any other ideas?
I'm sure this question has already been asked, I made an attempt to search the forums, but was unsuccessful. Our production servers are currently running SQL Server 2000 3a. I just got a new development system and have no intentions of installing SQL Server 2000 dev, but SQL Server 2005 Dev Edition. I was wondering if I will still be able to install SQL2005 and still be able to administer/modify 2000 mdfs with SQL 2005 Enterprise Manager. I wanted to be able to still make development on SQL 2k5 and update the production server (SQL2k). This is on my dev system not the production, the production will still be running SQL 2k, until the client decides on purchasing SQL 2k5. Of course I will not be able to use the new db features and types on a SQL2000 server, I just want to maintain the legacy with 05 Enterprise Manager (i.e.: SP, UDF and DTO). Thanks a gig. Larry
I don't know if this is the right forum to post this question, but here it goes.
We have restored into sql 2005 the database backups made in sql 2000. We connect with trusted connection and application roles, and when trying to execute a transaction to another database (with the guest user), we get a permission error.
Does anyone know if, apart from restoring the databases, we should do something else to get the guest user working the same way as with sql 2000 in the restored databases?
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
