Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: Database Restores

    I took some database backups from a client site to do some development.
    There were 20G, 16G and 200MB

    i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.

    went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I have to bring 2 or 3 client databases in house a week. Our largest client db is only about 70 gb but here is my restore template. Never fails.

    Code:
    --STEP 1. Create the Database
    CREATE DATABASE MyDatabase
    
    --STEP 2. Get the physical file names
    sp_helpdb MyDatabase
    
    --STEP 3. Get the logical file names
    RESTORE FILELISTONLY 
    FROM DISK = 'X:\MyBackup.BAK'
    
    --STEP.4 RUN THE RECOVERY 
    ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE MyDatabase
    FROM DISK = 'X:\MyBackup.BAK'
    WITH REPLACE,RECOVERY,STATS=10,
    MOVE 'Logical_Data' TO 'D:\physical.mdf' ,                                                                                                                                                           
    MOVE 'Logical_Log' TO 'E:\physiical.LDF'
    GO
    ALTER DATABASE MyDatabase SET MULTI_USER
    GO
    BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
    GO
    USE MyDatabase 
    GO
    DBCC SHRINKFILE(Logical_Log,1)
    GO
    USE MASTER
    GO
    ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
    “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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's the sort of thing you should post for reference on sqlkit, Thrasy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I know. I am getting some stuff together. Sample code... I have an article started. I will probably start posting this weekend. I am looking forward to some biting criticism. Why don't you post your CSVtoINT thing and don't you have a first name \ last name from one field thing too. I just have a full version release coming plus customer projects by the end of the month and I am woefully behind.
    “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.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I have noticed a remarkable delay from issuing the restore command to the first status tick being reported. Esecially on large databases. It could be that SQL Server is verifying the backup before beginning the restore, but that would be a question for Microsoft.

Posting Permissions

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