Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    144

    Unanswered: Restoring database to a different name and location

    Can someone please tell me what's wrong with my script below? I am trying to restore a database backup to a different database name and location.

    RESTORE DATABASE test
    FROM 'C:\mybackup.bak'
    WITH MOVE 'h_data' TO 'C:\Program Files\SQL2000\MSSQL\Data\Test_data.mdf'


    Thanks

    SHK

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What error are you getting?

    I do the following

    Code:
    ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE TaxReconDB_Prod
       FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp'
       WITH   MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF' 
            , MOVE 'TaxReconDB_Log'  TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF'
    	, REPLACE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Posts
    144
    The error said that I have a syntax error on line 2.

    I tried

    FROM DISK = 'C:\Mybackup.bak"

    and I got an error "Cannot open backup device", which makes sense because the backup is not from a disk.

    then I tried

    FROM FILE = 'C:\MyBackup.bak"

    and I got a syntax error.

    Help. I am not familiar with restoring database at all.

    Thanks

    SHK

  4. #4
    Join Date
    Jan 2004
    Posts
    6
    The following is correct:

    FROM DISK = 'C:\Mybackup.bak"

    As you are getting "Cannot open backup device", looks like you have mispelled the file name or missed a blank or something...check again

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    Check the ending double quote:::
    FROM DISK = 'C:\Mybackup.bak"

  6. #6
    Join Date
    May 2003
    Posts
    144
    I double checked and the spelling is correct.

    It is a single quote at the end of the FROM DISK.

    I am out of ideas.

    SHK

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok, do this

    RESTORE FILELISTONLY FROM DISK = 'C:\....'

    btw where is the back up file physically at?

    What happend when you run

    master..xp_cmdshell 'DIR C:\*.bak'

    What do you see?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    May 2003
    Posts
    144
    I got it to work.

    I copied the backup from the network to my C drive and wanted to restore the backup to my MSDE 2000. I was trying to run the script from the network when I got all those errors. I was playing around and figured that maybe I could try to run the script locally and there it ran.
    I guess the script failed because the backup was on my C drive and I tried to run it from the network server.

    Thanks for all the suggestions and helps.

    SHK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •