I have a backup of a SQL7 DB that i need to restore into SQL 2000. I only have the backup of the DB , i don't have access to the SQL7 server. When ever I try to restore the DB into SQL2000 nothing works, the tables and properties all restore properly and the data is there but i can't retrieve info from it. I'm trying to move a website DB from SQL7 to SQL 2000 for use with Cold Fusion. I know they're is issues with going from 7 to 2000 but i don't know what they are. If anyone could help me with this i would greatly appreciate it. I know I need to do some special steps or changes before it will work in 2000, does anyone know what these are?
I'm currently on a project that'll move all my SQL7 databases into SQL2000. This is from a SQL7 production box to a new Windows2k SQL2000 box. I'm confused as to the proper way of moving all the objects over. What should i do? Any information. Thank You!
Can anyone tell me of an EASY way to migrate a a database from a SQL 7 server to a SQL 2000 server (two different machines)?
Everything that I have found is only for upgrading SQL 7 to SQL 2000 and this isn't what I need.
Two groups in our company merged into one. The first group has a SQL 7 server that the lease is about to expire on and the second group (mine) has a newer SQL 2000 server that we need to move all of the databases to.
I have tried DTS. It failed. I tried backing up the 7 server and restoring to the new server. It failed.
Can anyone point me to a white paper/discussion on the issues of gaining access to SQL2000 data from a SQL7 installation? I'm about to upgrade servers from 7 to 2000, but a few of the servers exchange data bi-directionally and the upgrade to both servers at the same time would be problematic; thus the need at some point to get at the SQL2000 data from the SQL7 server.
Is it possible to administer both SQL7 & SQL2000 databases from the same console ?? It's fine administering SQL 6.5 & SQL7, as 6.5 had no MMC, but it seems that SQL7 & SQL2000 have the same MMC.
Hi 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
I'm upgrading a set of databases from SQL7 on one server (A) to SQL2000 on another server (B). I'd like input on the best method for moving the databases over (BCP, restore from BAK, etc), and in particular on moving the list of valid users (with default databases, permissions to databases, etc) and the DTS packages and Agent jobs.
When moving the databases, which are currently in code page 850, I might want to have them convert to the standard Latin-1 of SQL2000, though this isn't critical.
I have table with about 2 million rows. Everyday about 18000 rows are inserted. The table contain events for site security system so archiving is neccessary. I would appreicate any help or ideas. Thanks in advance.
I have a SQL Server 2000 box running two databases which was upgraded in error from SQL7.0 Basically we are no longer being given support as SQL2000 is not supported by the suppliers.
I therefore need to downgrade the box to SQL7.0 and was wondering if anyone had any opinions on the best method for doing this. Note that I do have a spare server to host the databases while I rebuild this server ( I am having to do a complete rebuild from scratch for other reasons ).
My thought is to transfer the databases to the spare server, rebuild the first box to SQL 7.0 and then perform a snapshot replication to transfer the data back.
Any thoughts on this would be gratefully received.
Hi, guys - looking for a bit of help/advice on moving a database fromSQL 7 to SQL 2000.We've a third party application which currently uses a SQL7 database.This database has a number of views which are owned by userINFORMATION_SCHEMA and are set as system tables. This user doesn'tactually have a login on the SQL server.We've tried the following to recreate the database on SQL2000, butwhatever we try, the views owned by INFORMATION_SCHEMA won't copy overfrom SQL7:(1) We tried the "Copy Database" wizard in SQL2000, but were gettingerrors when trying to actually copy the database (other steps workedfine).(2) We tried a restore to SQL2000 from our Legato Networker backup ofthe SQL7 database, but the views didn't come across.(3) We tried a backup from SQL7 and restore to SQL2000, but got themessage that the backup file contained a database other than the onewe wanted (it didn't - we checked the backup file several times byrestoring it to another SQL7 server).(4) We tried to import/export data from SQL7 to SQL2000, but the viewsowned by INFORMATION_SCHEMA weren't showing to add to the list ofobjects to take across.(5) We created the INFORMATION_SCHEMA login on SQL2000, and tried allrestores again, but still the views wouldn't go across.(6) We generated the SQL for all views in SQL7 and ran these onSQL2000, setting the owner to INFORMATION_SCHEMA, but wasn't surewhere to set the type to "system" (as opposed to "user).We're having problems running the third party application with aconnection to SQL2000, and want to rule out the lack of views, or viewownership, as the problem. Our third party vendor isn't coming upwith any solution in a hurry. Can anyone think of any reason why theviews are coming across when the database is restored to SQL2000?Thanks for your help.
I have setup SQL2000 Personal edition on my notebook.
I cannot connect to an existing SQL7 server on the network anymore. I used to run SQL7 Desktop edition which ran just fine. I also noticed that when I start my machine it take about 2 minutes until the SQL service is started.
I'm attemting to replicate from a SQL7 box to SQL2000.
The snapshot works fine, but it fails when it tries to push the subscription to the SQL2000 box with a trust error (I don't recall the exact message). I noticed that it fails when Enterprise Mgr is configured as: "Tools/Replication/Configure Publishing, Subscribers and Distribution", Subscriber Tab, "Impersonating SQL Service Agent". It works if I change to "SA".
This is only the case when we replicate from SQL7 to SQL2K. SQL7 to SQL7 works fine with "Impersonating". Has anybody seen this behavior? Any workarounds, other than using SA?
Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
I'm attemting to replicate from a SQL7 box to SQL2000.
The snapshot works fine, but it fails when it tries to push the subscription to the SQL2000 box with a trust error (I don't recall the exact message). I noticed that it fails when Enterprise Mgr is configured as: "Tools/Replication/Configure Publishing, Subscribers and Distribution", Subscriber Tab, "Impersonating SQL Service Agent". It works if I change to "SA".
This is only the case when we replicate from SQL7 to SQL2K. SQL7 to SQL7 works fine with "Impersonating". Has anybody seen this behavior? Any workarounds, other than using SA?
The following query runs fine on sql 7.0 but it kind of hangs/keeps running without any output on sql server 2000 --------------------- Set @cmd = 'Update ABCD Set '+@day+'_LB = IsNull(LB,0), '+@day+'_UT = IsNull(UNIT,0) From tempData as T Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC' execute (@cmd) ------------------------------------ But if we hard code the @day parameter and run the query like this it runs very fast on sql 2000....
Update ABCD Set THIRD_LB = IsNull(LB,0), THIRD_UT = IsNull(UNIT,0) From tempData as T Where T.STORE_NUM = ABCD.STORE_NUM And T.ITM_ID = ABCD.UPC ------------------------------
I am planning to move the sql server 2000 database to the sql server 2005 database. I have to restore that by using the back up copy. Do I need to follow any special instructions or just like moving from one server to another. Could some body help with this. Thanks!
Hi I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution. Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS SELECT TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP SELECT TABLE_SCHEMA , TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row. -- the first cursor loop gets all table names, the second will find row counts of source tables. -- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not -- processing anything, and is used only to find tables which have a rowcount > 0 -- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding -- tables with row counts > 0
SET @QUOT = CHAR(39) SET @LBRAKT = '[' SET @RBRAKT = ']' SET @IUT = 'IsUserTable' SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.dbo.' PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) ) DECLARE GETEM CURSOR FOR SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP OPEN GETEM FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' + '(SELECT COUNT(*) FROM OPENDATASOURCE(' + @QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' + ' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT EXEC (@SQL) FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME END CLOSE GETEM DEALLOCATE GETEM
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
How can I copy a SQL 7 database from one server to another? Please tell me all the ways because I've tried the obvious and it doesn't work. (backup and restore - won't go from one server to another, DTS transfer object Wizard - does a whole pile of errors and doesn't do stored proceedures and trigures). Thanks for help in advance. Also, what is the email address to post to the listserv and where is the FAQ for SQL7? Most of my listserv's tell you where to post right at the bottom of the digest I get - any chance you guys would do that with this list? Thanks again.
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?
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
After installing SP2 we encounter a problem with MMC. When selecting a DB we recieve a RunTime error? And the DB items/properties are not shown. Is there anyone with the same problem and knows how to fix this? I search the MS site for more information but there is nothing on it.
I am going to install SQL7.0 on the current NT4.0 with SQL6.5 on it by uninstalling SQL6.5 first and then installing (not upgrading through Wizard) SQL7.0.
Here are the questions I need help with: 1. Should I convert the database after the installation of SQL7.0 or directly restore the SQL6.5 databases backup? 2. If I need to convert the databases, How and in what way? 3. Which way is better upgrade or installation?
