Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: make a copy of a db

    I need to restore a copy of database to a new db name

    i'm trying by creating a new db and restoring by doing

    RESTORE DATABASE [userr] FROM DISK = N'D:\sqlbackups\user.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
    GO

    i'm getting an error

    Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing 'userr' database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    What's the best way to do this?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Apparently what you believe to be FILE = 4 and what the RESTORE finds in FILE = 4 is not the database you believe it to be.

    Post the results of RESTORE HEADERONLY for the backup device please, and let's work from there.

    BTW is the name of the database you are looking for "user" or "userr"?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If this restore is going on the same instance as the original database, you will also need to move the physical files in the restore command.

  4. #4
    Join Date
    Jun 2006
    Posts
    66
    what's restore headeronly?

    i'm trying to restore a backup from user to userr

  5. #5
    Join Date
    Jun 2006
    Posts
    66
    i'm restoring to a new db.
    I don't want to lose my backups or touch my regular db in anyway

    what's the best way to do this?

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by esthera
    what's restore headeronly?

    i'm trying to restore a backup from user to userr
    Check BOL!!

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by MCrowley
    If this restore is going on the same instance as the original database, you will also need to move the physical files in the restore command.
    True, but I figure one problem at a time ... the RESTORE command, as he provided it, is trying to pull one backup out of a backup set ... a database named 'userr' ... and RESTORE says it is not at file position 4. Once we find the correct backup, we will work with "WITH MOVE".

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Yeah, but userr is the destination name, not the source name. I agree there may be a problem with the backup file (such as no fileID 4), but restore should pick up whatever backup is in slot 4, and restore it under the new name. I have not fiddled with this bit, since backing up everything to one file is a bit riskier than I like, so I have never had a reason to experiment with it.

  9. #9
    Join Date
    Jun 2006
    Posts
    66
    so what is the best way to do this - I urgently need to restore to a new db.

  10. #10
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    why dont you use the restore option from enterprise manager.... It is lot more easier....

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Backup and restore is the best way.

Posting Permissions

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