I want to copy a SQL2005 database to a SQL2000 environment? I created a backup from the DB in SQL2005 and moved the file to the other server but SQL2000 says the BAK file is invalid since the backup has structure version 611 while the server supports version 539. Any solution?
Canada DBA
Hello All:
From my previous post it seems that the general consensus is for me to use backup and Restore to upgrade a dB from SQL2K to SQL2K5.
Could anyone refer me to some explicit instructions for doing this?
I have SQL2K5 and SQL2K on separate servers and I cannot back the dB up to SQL2K5. I also can't access the drives for SQL2K from SQL2K5. I'm hoping it's a simple mistake.
Any advice or guidance is greatly appreciated.
Thanks,
Cleech
Hi,
Is it possible to deploy both SQL2000 and SQL2005 on the same server in the production environment?
Is it recommended? Next year we will be deploying a SQL2005 solution and was wondering if we could get away with installing it on the same server as the SQL2000 server, or whether we should put it on another server.
We have up to a maximum of 50 concurrent users.
Thanks.
Hi there, we tried to restore a sql 7 DB to sql2000, somehow some ofthe varchar filed become nvarchar, why is this?thanks,-sarah
View 5 Replies View Relatedmany thanks!
View 1 Replies View RelatedHi there,
I need to create a copy of a SQL 2000 database in another server which has SQL 7.0 installed. When I do a backup and restore, it gives me the following error :
Server: Msg 3154, Level 16, State 2, Line 1
The backup set holds a backup of a database other than the existing 'inventmast' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It seems to be like a downward compatability issue. Is there any other way to restore a sql2000 db onto sql 7 ??
Thanks
Paul
Hi all
We have a lot of SQL2000 databases and would like just backup and restore to SQL2005.
Is there anything that I need to look/aware at it apart of DTS stuff (we are not using much DTS).
Does the Level80 sets on SQL2005 for each database will have backward compatibility with SQL2000?
Thanks
Hello,
Restoring a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:
TITLE: Microsoft SQL Server Enterprise Edition
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
Hello,
In the process of migrating a logshipped database (in non recovery mode) from SQL2000 SP4 to SQL2005 SP2, I got the following error (sorry for the french translation!):
---
Conversion de la base de données 'AdStatistiques' de la version 539 à la version actuelle 611.
Base de données 'AdStatistiques' exécutant l'étape de mise à jour de la version 539 à la version 551.
Msg 601, Niveau 12, État 3, Ligne 1
Impossible de poursuivre l'analyse avec NOLOCK car les données ont été déplacées.
Msg 3167, Niveau 16, État 1, Ligne 1
RESTORE n'a pas pu démarrer la base de données 'adstatistiques'.
Msg 3013, Niveau 16, État 1, Ligne 1
RESTORE DATABASE s'est terminé anormalement.
---
Of course, the restore operation performs well when the destination is SQL2000. I suspect the integrity checker of SQL2005 to be less permissive, hence I run a checkdb on the original DB, which raise several errors, which are quite difficult for me to analyse:
[Sorry, it's in french again]
----
Serveur : Msg 8929, Niveau 16, État 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620702208 possédé par l'enregistrement de données identifié par RID = (1:1385:13) id = 1902629821 and indid = 1.
Serveur : Msg 8929, Niveau 16, État 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620767744 possédé par l'enregistrement de données identifié par RID = (1:1385:14) id = 1902629821 and indid = 2.
Serveur : Msg 8929, Niveau 16, État 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620833280 possédé par l'enregistrement de données identifié par RID = (1:1385:15) id = 1902629821 and indid = 3.
Serveur : Msg 8929, Niveau 16, État 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 15620898816 possédé par l'enregistrement de données identifié par RID = (1:1385:16) id = 1902629821 and indid = 4.
Serveur : Msg 8929, Niveau 16, État 1, Ligne 1
Objet ID = 2 : Erreurs trouvées dans le texte ID = 26594574336 possédé par l'enregistrement de données identifié par RID = (1:7153632:27) id = 1031010754 and indid = 5.
Serveur : Msg 8961, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 2. Le nœud text, ntext ou image à la page (1:1974), slot 0, texte ID = 26594574336 ne correspond pas à sa référence dans la page (1:191177), slot 10.
Serveur : Msg 8974, Niveau 16, État 1, Ligne 1
Nœud texte référencé par plusieurs nœuds. L'objet ID = 2, page de nœud text, ntext ou image (1:1974), slot 0, texte ID = 26594574336 est pointé par la page (1:191177), slot 10 et par la page (1:38504), slot 0.
Serveur : Msg 8965, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 2. Le nœud text, ntext ou image à la page (1:1111184), slot 0, texte ID = 15620767744 est référencé par la page (1:191177), slot 11, mais n'a pas été vu à l'analyse.
Serveur : Msg 8965, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 2. Le nœud text, ntext ou image à la page (1:1111185), slot 0, texte ID = 15620833280 est référencé par la page (1:191177), slot 12, mais n'a pas été vu à l'analyse.
RĂ©sultats DBCC pour 'AdStatistiques'.
RĂ©sultats DBCC pour 'sysobjects'.
Il y a 388 lignes dans 9 pages pour l'objet 'sysobjects'.
RĂ©sultats DBCC pour 'sysindexes'.
Serveur : Msg 8965, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 2. Le nœud text, ntext ou image à la page (1:1111186), slot 1, texte ID = 15620898816 est référencé par la page (1:1385), slot 16, mais n'a pas été vu à l'analyse.
Il y a 437 lignes dans 26 pages pour l'objet 'sysindexes'.
CHECKDB a trouvé 0 erreurs d'allocation et 10 erreurs de cohérence dans la table 'sysindexes' (objet ID = 2).
[SNIP]
RĂ©sultats DBCC pour 'AdStatPageHeure_OLD'.
Serveur : Msg 8977, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nœud parent pour la page (1:1043307) n'a pas été rencontré.
Serveur : Msg 8977, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nœud parent pour la page (1:1043308) n'a pas été rencontré.
Serveur : Msg 8977, Niveau 16, État 1, Ligne 1
Erreur de table : Objet ID = 1902629821, index ID = 1. Le nœud parent pour la page (1:1043309) n'a pas été rencontré.
Serveur : Msg 8977, Niveau 16, État 1, Ligne 1
[SNIP, several errors pretty similar here]
-Trop d'erreurs trouvées (201) pour l'objet ID = 1902629821. Pour voir tous les messages d'erreurs, relancez l'instruction avec 'WITH ALL_ERRORMSGS'.
Il y a 21062117 lignes dans 72062 pages pour l'objet 'AdStatPageHeure_OLD'.
CHECKDB a trouvé 0 erreurs d'allocation et 72063 erreurs de cohérence dans la table 'AdStatPageHeure_OLD' (objet ID = 1902629821).
---
Of course, I'd like to delete the AdStatPageHeure_OLD table but I get a cryptic error message when doing that, and I can't remove the indexes that, according to some other posts I browsed, could explain the behavior:
---
La page (1:1111186), slot 1 pour le noeud text, ntext ou image n'existe pas.
Page (1:1111186), slot 1 for text, ntext, or image node does not exist. (Microsoft SQL Server, Erreur : 7105)
Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=7105&LinkId=20476
---
Is there any easy option you can see to operate the transfer, since the backups I have from the DB already contain the data corruption?
Thanks a lot for any hint you could provide,
--
Julien
Hi all
i have taken backup of a database from SQL Server2005
can i Restore it on SQL Server2000 server?
Please help me
Thanks
How to restore a database dump file (.BAK) that backed up from SQL2000 server to a SQL 7.0 server?Please help.Saiyou
View 1 Replies View RelatedI have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies View Related
I can restore databases one by one, setting .mdf and .ldf destination paths.
How can I restore all my Databases at the same time?
Thanks
G. Zanghi
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies View RelatedHave people installed SP2 on SQL2k5 . Any problems / issues with that especially wrt failover clustering .
View 4 Replies View RelatedHow to schedule a DTS package residing in LEGACY container, from SQL2K5?
------------------------
I think, therefore I am - Rene Descartes
In SQL2K EM you can right click a DB, and backup. Finally there is a option to schedule, which will create a job.
Is this option has been removed from SQL2K5?
------------------------
I think, therefore I am - Rene Descartes
Is there any reason why an application running on a 32-bit O/S that works with a SQL Server 2K database would not work with a SQL Server 2K5 64-bit database running on a 64-bit O/S?
It is my understanding that the connection layer (be it ODBC, OLEDB, ADODB, etc) would obfuscate any details regarding the underlying architecture... I wouldn't think the instructions sent would need to be any different, unless there is some deprecated call that is no longer supported.
Thanks in advance for any assistance!
Hello,
I have installed in a workstation (windows xp pro) sql 7 server (database engine + client tools).
After that I installed sql server 2005 with named instance (hostmyinstance).But I didn't end this installation.
I had a message saying me that there are problems on clients tools. moreover when I open a dos window and type "sqlcmd", I have the same message (client problems).
I then change my sql7 port to 1434 (which isn't the default port).But even with that my sql 2005 sqlcmd don't work correctly.
My question is :
can one install sql 7 (server + client tools) and sql 2005 (server + client tools) in the same machine ( a workstation [windows xp pro] )?
Dear All,
I have a need to replicate data between MS Access and SQL 2005 (Enterprise Edition).
Could someone give advice as to whether this is possible or not, and how I might go about setting it up?
Thanks
Krishna
I found that SQL2K5 doesn't prompt for file locations while installing - for binaries and databases. Is this the default behavior?
------------------------
I think, therefore I am - Rene Descartes
Hi
I am a Oracle DBA who is going to embark upon a
oppurtunity to migrate Oracle to sqlserver.
Can somebody give me tips for
Learning sqlserver2k5 for a Oracle DBA Books
Or Beginner DBA/Development Books for sql2k5
How good is the migration assistant from m$$ for
migrating from oracle to sql2k5
Does it migrate data as well as procedural code ?
Do you anything for me to watch out for best parctices
migration guides
You opinion would be higly appreciated
regards
db2hrishy
For a SQL/2005 DB Server do I need the /3GB switch or not? I will have IIS running on the server; however, it won't be doing very much as the SQL Database is the main thing:
Windows 2003 R2 Enterprise w/16 GIG RAM 4 CPU (Virtual Machine)
SQL/2005 Enterprise Edition
Hardware: Dell Server 8 CPU w/32 GIG RAM running VM ESX V3
I know I need the /PAE switch to utilize Memory over 4 GB - the question is do I need the /3GB switch along with the /PAE switch?
DeWayne
I need tyo move tables from Oracle 9.2 or 10.2 version to SQL server 2005.Can any one give me some good hints, scripts etc.I try to look into the Business Intellegent Studio, but I only see Report,Analaysis or Integration services - NO DTS.Can someone tell me where to find it. It used to be so easy in 2000.Thanks,Nasir
View 4 Replies View RelatedI have a partition view named StudentRequest with underlying tables StudentRequest_T1 and StudentRequest_T2.
Primary Key is on NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode
Partition View
Create View dbo.StudentRequestPartView with SchemaBinding
as
Select
NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode, AssignedCourseCode, AssignedSectionId,
LockAssignedRequest, UpdatedByPID, UpdatedDate, CreatedByPID, CreatedDate, AuditCommentId, DebugTrace
from dbo.[StudentRequest_T1]
union all
Select
NumericSchoolDBN, SchoolYear, TermId, StudentID, CourseCode, AssignedCourseCode, AssignedSectionId,
LockAssignedRequest, UpdatedByPID, UpdatedDate, CreatedByPID, CreatedDate, AuditCommentId, DebugTrace
from dbo.[StudentRequest_T2]
go
Partition Tables
CREATE TABLE [dbo].[StudentRequest_T1](
[NumericSchoolDBN] [int] NOT NULL,
[SchoolYear] [smallint] NOT NULL,
[TermId] [tinyint] NOT NULL Check ([TermID] = 1),
[StudentID] [int] NOT NULL,
[CourseCode] [varchar](10) NOT NULL,
:
)
CREATE TABLE [dbo].[StudentRequest_T2](
[NumericSchoolDBN] [int] NOT NULL,
[SchoolYear] [smallint] NOT NULL,
[TermId] [tinyint] NOT NULL Check ([TermID] = 2),
[StudentID] [int] NOT NULL,
[CourseCode] [varchar](10) NOT NULL,
:
)
I am able to insert, update and delete records in the StudentRequest view using simple DML SQL statements i.e.
e.g.
Insert into Student Request (NumericSchoolDBN, SchoolYear, TermId,€¦)
Values(12345, 2006, 1,€¦)
Delete from StudentRequest Where NumericSchoolDBN = 12345 and TermId = 1
But when I use complex SQL statements using self-joins€¦
Insert into Student Request (NumericSchoolDBN, SchoolYear, TermId,€¦)
Select NumericSchoolDBN, SchoolYear, TermID,€¦.
From Student Request sr1 left outer join Student Request sr2 on
Sr1.NumericSchoolDBN = Sr1.NumericSchoolDBN and
Sr1.SchoolYear = Sr2.SchoolYear and
Sr1.TermId = Sr2.TermId and
Sr1.StudentID = Sr2.StudentID and
Sr1.CourseCode = Sr2.CourseCode
I get the following error and I can€™t seem to find any documentation that this is a limitation!!!...
Msg 4439, Level 16, State 6, Procedure Course_UpdateCoursePromotion, Line 232
Partitioned view 'STARS.dbo.StudentRequest' is not updatable because the source query contains references to partition table '[STARS].[dbo].[StudentRequest_T1]'.
Hi,
We recently migrated from a SQL 2K to 2K5. We had an automatic DTS scheduled every night that would transfer data to a MySQL server on one of our client€™s machine. It worked like a charm in SQL 2K.
I am trying to figure out how I can connect to a MySQL data source using SSIS in 2K5 as I don€™t see an ODBC Destination anywhere in the Data Flow Task; and run the same transfer using 2K5.
Do I have to add a reference of some kind or does 2K5 doesn€™t support ODBC destinations?
Thanks
Having problems installing SQL 2005 Developers Edition on a Terminal Services Server that already has SQL 2000 Developers Edition on it. I keep getting this error, no matter in what order I install packages:
Failed to install and configure assemblies M:Program FilesMicrosoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2147024894
Error message: The system cannot find the file specified.
Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
The help link says to set the DTC service up to run as a network service and start it, but it already is and is already running. Restarting the DTC and then clicking on retry yields the same error.
Any ideas? I've been beating my head against this machine for a couple of days now.
Thanks.
Phil Hershey
Hello everyone,
I need to get a server built for a client today. He has sent me the proof of purchase for his SQL2005. He is working on getting me the licence / cd key today (if thats even possible?).
I have our SQL 2005 server CD here that I wanted to use however when I get to the company name stage of the install the CD-key/licence code is greyed out. Any ideas on how I can change the embedded cdkey/licence to the clients so I can use our media today but with our clients cdkey?
This needs to be done today, its extremly important but due to a problem with logistics the media from the client was delayed untill mid next week.
Any help you can provide would be aprecaited.
Cheers
~Chris
I continue to have issues with SP-2 for SQL 2005 suite. It has now gotten so bad, I have multiple installations, including the TOOLS ONLY on my laptop failing, that I am going to stop ALL future installations of SP-2. The COM+ failures have not been resolved that I can determine. I have tried uninstalling, I have tried SP-1 before SP-2, every combination one can find. HELP.
Time: 05/07/2007 13:24:05.631
KB Number: KB921896
Machine: GA029-MDGRAVES
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.00.1399.06 x86
SQL Server Native Client ENU 9.00.3042.00 x86
Client Components ENU RTM 9.00.1399.06 x86 STANDARD
MSXML 6.0 Parser ENU 6.00.3883.8 x86
Backward Compatibility ENU 8.05.1054 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Setup Support Files
Product Version (Previous): 1399
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SqlSupport.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : SQL Server Native Client
Product Version (Previous): 3042
Product Version (Final) :
Status : Not Selected
Log File :
Error Description :
----------------------------------------------------------------------------------
Product : Client Components
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
----------------------------------------------------------------------------------
Product : MSXML 6.0 Parser
Product Version (Previous): 3883
Product Version (Final) : 6.10.1129.0
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_msxml6.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Backward Compatibility
Product Version (Previous): 1054
Product Version (Final) : 2004
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SQLServer2005_BC.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 29549
I installed SP1 for SS 2005 on a quasi-production server, which is why I didn't mind rebooting the server. Unfortunately, it's been 5 hours and the server still hasn't rebooted. (I see a gray screen and a pointer, and that's it... but there's still disk access happening. I'm not really sure what it's doing.)
What's the best way to avoid the 5 hour reboot in the future?
Thanks,
Robert
Is it possible to convert a SQL2K datafile to SQL2K5? I have a 2K database that I need to easily convert to 2K5, I apprecaite any insight on this issue.
View 1 Replies View RelatedI am trying to create a Linked server between Access 97 and SQL2K5
provider=Microsoft.Jet.OLEDB.4.0
MDAC version installed on SQL server system is 2.8
with same configuration if i convert MDB file to Office 2000 then its working fine
Please advice
I've populated reporting DB's in the past using different techniques:
1. Have Transactions in Data Tier Write to OLTP AND Reporting DB Simultaneously
2. Log Shipping
3. Restore BAckups Nightly
4. Replication
5. DTS
My question is what is the BEST Practice in SQL2k5.
I'm all gung ho about using Mirroring but am not certain that this will be the best way to go.
My thoughts are something along the lines of this
1. have OLTP Mirrored to what I'll call a "Staging" DB. This is NOT to be used for Failover just as a means of accessing read only data for the Reporting DB
2. Have 2 DBs on another box that are flattened (Denormalized) for reporting (ReportA and ReportB)
3. Use SSIS to Populate the Report DBs in alternating sequences every XXX minutes (60 minutes let say)
4. Use Logic to let the Reporting Application Figure out which of the Two reporting DB's is "live". The Currently loading DB will be offline while it's being loaded and we'll alternate between the two (Exact method TBD but this wont be rocket science)
Now, I know this will "Work" but is this the best way to go about this ?
I do NOT want to use SSIS to populate the Reporting system directly from our OLTP system due to contention issues etc.
thoughts ?