Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: SQL 2000 Restore Problem

    Hi...

    I am trying to restore a week-old backup from a file of one database to a different (new) database for development. This is something that I do on a regular basis and have never had issues before. Now it is failing using both QE and Enterprise Mangler and am getting this error message:

    Server: Msg 3135, Level 16, State 2, Line 1
    The backup set in file 'D:\BACKUPS\..\....bak' was created by BACKUP DATABASE...FILE=<name> and cannot be used for this restore operation.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    I have tried this....
    RESTORE DATABASE temp_new_db
    FROM DISK = 'D:\BACKUPS\..\mybackupfile.bak'
    WITH REPLACE

    And this....
    RESTORE FILELISTONLY
    FROM DISK = 'D:\BACKUPS\..\mybackupfile.bak'
    RESTORE DATABASE temp_bio_remote
    FROM DISK = 'D:\BACKUPS\..\mybackupfile.bak'
    WITH MOVE 'Original_DB' TO 'D:\temp_new_db.mdf',
    MOVE 'Original_DB_log' TO 'D:\temp_new_db..ldf'


    What am I doing wrong? Any ideas?

    Thanks in advance...

    Justin

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does filelistonly show you?
    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
    Feb 2004
    Posts
    7
    Hi Brett..

    Thanks for your quick response. Here are the gory details...

    LogicalName: bioremote_Data
    PhysicalName: D:\MSSQL\DATA\BIOREMOTE\BIOREMOTE.mdf
    Type: D
    FileGroupName: PRIMARY
    Size: 303824896
    MaxSize: 35184372080640


    This is bizarre. I have read other threads on a few boards about this same error, and not a single person could get around it or identify the problem.

    Any ideas you may have would be most appreciated!

    Thanks again,

    Justin

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you have to MOVE the file BioRemote_Data...where's the Log file name?


    how about

    Code:
    ALTER DATABASE New_BioRemote SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE New_BioRemote
       FROM DISK = 'D:\BACKUPS\..\mybackupfile.bak'
       WITH   MOVE 'BioRemote_Data' TO 'D:\Database\Servername\Instance\New_BioRemote_Data.MDF' 
            , MOVE 'BioRemote_Log'  TO 'D:\Database\Servername\Instance\New_BioRemote_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.

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    Thanks for the info, but unfortunately, I am still getting the same error. Yes, I was using WITH MOVE in my statements. As for the log, I have no idea what happened to it. This file is supposed to contain a complete backup, but apparently does not.

    No matter what I try, I still get this error:

    The backup set in file 'D:\backups\mybackupfile.bak' was created by BACKUP DATABASE...FILE = <name> and cannot be used for this restore operation.

    RESTORE DATABASE is terminating abnormally.

    This is now officially driving me nuts. As what was originally for testing purposes is now critical, as I just found out that one of our crack developers "accidentally" truncated one of the key tables in the DB last weekend.

    Any ideas? Has anyone ever seen or gotten past this error?

    Thanks in advance...

    Originally posted by Brett Kaiser
    Well you have to MOVE the file BioRemote_Data...where's the Log file name?


    how about

    Code:
    ALTER DATABASE New_BioRemote SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE New_BioRemote
       FROM DISK = 'D:\BACKUPS\..\mybackupfile.bak'
       WITH   MOVE 'BioRemote_Data' TO 'D:\Database\Servername\Instance\New_BioRemote_Data.MDF' 
            , MOVE 'BioRemote_Log'  TO 'D:\Database\Servername\Instance\New_BioRemote_Log.LDF'
    	, REPLACE

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have any other dumps? Or are you overwriting the same one?

    What recovery model are you using?

    Can you take advantage of this?

    http://www.lumigent.com/products/le_sql/le_sql.htm

    And maybe you should Crack the developer upside the head...

    accident indeed...

    Scrub is more like it...

    Tell him next time to do

    SELECT * INTO to back up the goddamn data...

    Or bcp the shit out to a file....

    OR...tell him/her to start typing......

    sheesh

    Sorry...it's been a long week....and it's only Wednesday...
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Will have to research .. but it seems the backup was taken by a statement of the kind
    Code:
    BACKUP DATABASE pubs
        File = 'PUBS' 
    TO Disk  = 'p:\PUBS.bak'
    WITH init
    this takes the backup of only one file and it seems can be restored only on the same database , you cannot create a new db from this.
    Get yourself a copy of the The Holy Book

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

  8. #8
    Join Date
    Feb 2004
    Posts
    7
    Hey Brett...

    Yes, we have other dumps, but they are useless, unfortunately. The table in question is used for an eCommerce/event registration website, which was opened to the public just one day before the "accident". There was, of course, a flurry of activity on it the first 24 hours, which was what was lost. Not a good scene, I can tell you. At this point, my best guess is that the media is corrupt.

    Oh yeah... I'm using full recovery model.

    Hehehe... I'd love to crack him upside the head. Me being in DC and him in NYC makes that one hard, though.

    And believe me... I have begged and pleaded with the developers to not write ANY update, insert or delete statements without going through me first to prevent these types of "surprises". Maybe now, though, my boss will put some teeth into this policy.

    Thanks for the link... I'm definitely going to check it out. Hopefully it will help.

    Oh yeah.. it has been a long week already. Crap.

    Thanks again,

    Justin



    Originally posted by Brett Kaiser
    Do you have any other dumps? Or are you overwriting the same one?

    What recovery model are you using?

    Can you take advantage of this?

    http://www.lumigent.com/products/le_sql/le_sql.htm

    And maybe you should Crack the developer upside the head...

    accident indeed...

    Scrub is more like it...

    Tell him next time to do

    SELECT * INTO to back up the goddamn data...

    Or bcp the shit out to a file....

    OR...tell him/her to start typing......

    sheesh

    Sorry...it's been a long week....and it's only Wednesday...

  9. #9
    Join Date
    Feb 2004
    Posts
    7
    Actually, after unsuccesfully trying to create a new DB from this, I tried to restore to the orginal database and still get the same error.

    Damn. This just makes my head hurt. Makes no sense.


    Originally posted by Enigma
    Will have to research .. but it seems the backup was taken by a statement of the kind
    Code:
    BACKUP DATABASE pubs
        File = 'PUBS' 
    TO Disk  = 'p:\PUBS.bak'
    WITH init
    this takes the backup of only one file and it seems can be restored only on the same database , you cannot create a new db from this.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Try something like this :-
    Code:
    RESTORE DATABASE PUBS
    file = 'PUBS' 
    FROM disk = 'C:\PUBS.bak'
    Get yourself a copy of the The Holy Book

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

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    Will have to research .. but it seems the backup was taken by a statement of the kind
    Code:
    BACKUP DATABASE pubs
        File = 'PUBS' 
    TO Disk  = 'p:\PUBS.bak'
    WITH init
    this takes the backup of only one file and it seems can be restored only on the same database , you cannot create a new db from this.
    Good point...


    How are you doing the backups?

    And btw...I'm going to THE city tomorrow....

    Where is he?

    I'll crack him for you....
    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.

  12. #12
    Join Date
    Feb 2004
    Posts
    7
    I'm actually becoming more convinced that the media is corrupt. I tried to restore to the original DB and get this error:

    The backup set holds a backup of a database other than the existing 'BIORemote' database.
    RESTORE DATABASE is terminating abnormally.

    This is, of course, BS.

    As far as how I'm backing up, I have a maintenance plan set up for a complete backup to disk, optimization, yadda yadda yadda. I have the same setup far all my databases and have never (and I mean never) had any restores fail before. They always worked like a charm.

    Screw it. Im SOL.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Check on RESTORE VERIFYONLY in BOL. This may confirm if the backup set is corrupt or not.

    You mentioned something about another database in there? What do you get from restore headeronly from ...?

    Only one file came up in restore filelistonly? May have to do some more digging...

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    quote:
    --------------------------------------------------------------------------------
    Originally posted by Enigma
    Will have to research .. but it seems the backup was taken by a statement of the kind

    code:--------------------------------------------------------------------------------
    BACKUP DATABASE pubs
    File = 'PUBS'
    TO Disk = 'p:\PUBS.bak'
    WITH init
    --------------------------------------------------------------------------------


    this takes the backup of only one file and it seems can be restored only on the same database , you cannot create a new db from this.
    --------------------------------------------------------------------------------


    If you happen to have a second database server, or as a last resort a separate SQL Instance, you may be able to get this restore to work.
    1) Backup the current munged DB
    2) restore munged DB to other server as same name
    3) Restore the file from the good backup onto that restored copy.

Posting Permissions

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