Post reply

SQL 2000 .bak file into SQL 7.0 Question

  • April 4, 2005 at 9:07 pm

    #85780

    Good day

    1..Can I create a .bak file from 2000 and restore it into a 7.0 instance?

    Localy we run 2000.  We have built a system that is to be used in a network where Enterprize manager is not allowed.  I have been asked to create a step by step doc including scripts that allows them to create, restore (from my 2000 .bak file), create login, user, issue read, write etc using query analyzer.  They are telling me my scripts dont work.  I wrote them to run seperatly so they may tell me at what stage they stopped working.  We were not told they were running version 7.0 until after i wrote the doc. 

    If the answer to question 1 is yes, can anybody tell me if the following scripts will run in version 7.0's Query Analyzer?

    2..CREATE DATABASE NavSysCom;

    3..RESTORE DATABASE navSysCom

       FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\backUp\navSysCom.bak' WITH

       move 'navSysCom_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\navSysCom_Data.mdf',

       move 'navSysCom_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\navSysCom_log.ldf';

    4..EXEC sp_addlogin 'NavSysComUser', 'password', 'NavSysCom', 'english';

    5..EXEC sp_adduser 'NavSysComUser';

       sp_addRoleMember 'db_datareader','NavSysComUser';

       sp_addRoleMember 'db_datawriter','NavSysComUser';

    Thank you in advance

  • April 5, 2005 at 2:01 am

    #550454

    As far as I know you cannot restore a 2k backup into 7.0 - sadly I don't have a 7.0 version to test. The only way it might work is if the 2k database is created with the 7.0 option set.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • April 5, 2005 at 6:43 am

    #550496

    Sorry, the backups are only forward compatile from 7 to 2000, not backward.

  • April 5, 2005 at 5:21 pm

    #550659

    Thankyou for your time, it is apreciated.  How would you attack my situation?  The objective is to get the current 2000 DB into a 7.0 instance and crate a 7.0 .bak file

    TYIA

    Great forum, I spend alot of time at p2p.wrox - There are some cool features here the snitz guys should impliment.

  • April 6, 2005 at 4:14 am

    #550728

    Try look at BCP (Bulk Copying Program) to move the data between the servers.

  • April 6, 2005 at 6:36 am

    #550765

    You can also use the import/export wizard.

  • April 6, 2005 at 5:24 pm

    #551008

    I ended up installing 7.0 on a clean machine and DTS'ed the data.  An interesting problem aroze.  I would have thought you could DTS from the 2000 Enterprize Manager (export) to the 7.0 instance - no joy.  It worked the other way round however none of my primary keys came through, datatypes yes - Thank you for your time.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply