Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2006
    Posts
    27

    Unanswered: Restore backup file

    I tried to use backup and restore database tasks to restore backup file but it does not work. The backup file I tried to restore in SQL server 2000 is from somewhere else (from my friend) and saved in cd-rom, not the one I created before.
    How can I restore it to view in SQL server 2000 database?
    Can you show me step by step?
    Thanks for your help

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What error did you get when you tried the restore

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I suspect that you first created a database and then tried to restore into it. In this case the TSQL script for this will be:

    Code:
    RESTORE DATABASE [databasename]
    FROM DISK = 'e:\cdrom\backup.bak',
    WITH replace
    It is important to add the "with replace" to restore into the newly created database.

    Please give more info on what you were trying to do, ie. what is the db info, error message(s), etc.

    Goodluck

  4. #4
    Join Date
    Oct 2006
    Posts
    27
    Quote Originally Posted by MCrowley
    What error did you get when you tried the restore
    The problem was when I clicked the new database name I just created to restore the backup file, there is nothing. So I thought it's probably because SQL server 2000 will only recognize the backup file if the database schema exist. Is this correct? To this case, I want to move the database from one server to another server.
    Please give me advice. Thanks

  5. #5
    Join Date
    Oct 2006
    Posts
    27
    Quote Originally Posted by Reghardt
    I suspect that you first created a database and then tried to restore into it. In this case the TSQL script for this will be:

    Code:
    RESTORE DATABASE [databasename]
    FROM DISK = 'e:\cdrom\backup.bak',
    WITH replace
    It is important to add the "with replace" to restore into the newly created database.

    Please give more info on what you were trying to do, ie. what is the db info, error message(s), etc.

    Goodluck
    Yes, I created a new database in Enterprise Manager and used backup and restore database tasks to restore backup file. But I don't know if it works when this backup file from another server and I want to move to this server?

  6. #6
    Join Date
    Oct 2006
    Posts
    27
    Quote Originally Posted by jennyphb
    Yes, I created a new database in Enterprise Manager and used backup and restore database tasks to restore backup file. But I don't know if it works when this backup file from another server and I want to move to this server?
    I tried to use this code just change the db name, path, and my backup file name like this:

    RESTORE DATABASE BackupDB
    FROM DISK = 'd:\cdrom\svtechmall_backup_200609210300.bak'
    WITH replace


    it shows the error:

    Server: Msg 3101, Level 16, State 1, Line 1
    Exclusive access could not be obtained because the database is in use.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    You need to view connections and terminate them all. Obviously exclusive access is required for a full restore. Talk about a need for major locking! Check Management/Current Activity in Enterprise Manager. If you have a web server, you may have to restart the web server to release the connection (right click from within IIS manager and select restart).

    If you use the RESTORE wizard in Enterprise Manager, and the backup file is from another database, you need to click on From Device, navigate to the file and select it, and on the Options tab, click "Force Restore over Existing Database". Depending on the naming and directory structures, you will likely have to also change "Restore to" column on the Options screen.

    It should also work if you close down the other database (stop all SQL services) and simply copy the .MDF and .LDF files into your DATA directory on the new server. I have only done this on SQL Server 2005 and there was a minor declaration process - someone else could chime in on this one.
    Last edited by vich; 10-13-06 at 00:48.

  8. #8
    Join Date
    Oct 2006
    Posts
    27
    If I simply copy .MDF and .LDF files, how can I do since the backup file was saved in cd-rom, and I got it from my friend and he wants to view the content of the backup file, it named
    svtechmall_backup_200609210300.bak

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or just do a

    ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    GO

    before your restore command. I usually use the WITH MOVE option with the WITH REPLACE option for the restore command. This may just be habit. To learn about WITH MOVE please see SQL Server Books Online--> Transact SQL Reference--> Restore.

    P.S. make sure your query analyzer is not logged into the database you are trying to restore.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Oct 2006
    Posts
    27
    Thanks.
    If the backup file was created in SQL server 7.0 version, I can not restore it in SQL server 2000 version, is it right?

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I have restored SQL 7 backups to SQL 2000 with no problems.
    Paul

  12. #12
    Join Date
    Oct 2006
    Posts
    27
    It makes me confused since another instructor told me I can't restore backup file with different version.

    How could you do it without problem?
    Thanks

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Restoring SQL Server 2000 to SQL Server 7.0 is not possible. Microsoft tends to not make things "Forward Compatible". Making things Backward Compatible is considerably easier, so you can restore a SQL 7.0 backup to SQL Server 2000 or 2005, I bet. I don't have any more 7.0 to play with, so I can not test for sure.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What can I tell you; it works without any special effort. I just did it again to make sure my memory wasn't failing. Backup created on NT 4 Server/SQL Server 7 machine copied to and restored without problem on a Windows 2003 Server/SQL Server 2000 machine.

    I'd make sure the instructor wasn't speaking about something else. Notice I didn't say they were wrong, even though the few I've met had no real experience in the real world.
    Paul

  15. #15
    Join Date
    Oct 2006
    Posts
    27
    Thanks you guys for your reply.

Posting Permissions

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