locked
Why is it taking so long to restore a database? RRS feed

  • Question

  • Using SQL Server Management Studio.

    I backup a on to a pen-drive then restore at my home.

    I use to be able to restore a 300 MB database in minute or so, but a few months ago it started taking 15 minutes.

    It gets to 90% restored in a reasonable amount of time, but then it just sits there at 90% for most of the 15 minutes.

    Any ideas what's happened and why this is taking so long?

    Thanks,

    Dennis

    Wednesday, September 23, 2009 3:05 PM

Answers



  • What kind of recovery model you are using?  Full or Simple

    If you are using full recovery, are you taking any Transaction Log backup?

    Do you have anything else running while performing restore. Looks like to me you have fairly small database.


    Instead of using Management Studio try using Query Editor. See that makes any difference

    Use master
    Go

    --Step 1: Retrive the Logical file name of the database from backup.
    RESTORE FILELISTONLY
    FROM DISK = 'C:\YourBaackUpFile.bak'
    GO

    --Step 2: Use the values in the LogicalName Column in following Step.
    --Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    Go

    --Restore Database
    RESTORE DATABASE YourDB
    FROM DISK = 'C:\YourBaackUpFile.bak'
    WITH MOVE 'YourMDFLogicalName' TO 'C:\Data\YourMDFFile.mdf',
    MOVE 'YourLDFLogicalName' TO 'C:\Data\YourLDFFile.mdf'
    --if you get Error 3154: The backup set holds a backup of a database other than the existing database
    --,REPLACE  --if database you are restoring alrady exists then use replace

    Wednesday, September 23, 2009 6:09 PM

All replies



  • What kind of recovery model you are using?  Full or Simple

    If you are using full recovery, are you taking any Transaction Log backup?

    Do you have anything else running while performing restore. Looks like to me you have fairly small database.


    Instead of using Management Studio try using Query Editor. See that makes any difference

    Use master
    Go

    --Step 1: Retrive the Logical file name of the database from backup.
    RESTORE FILELISTONLY
    FROM DISK = 'C:\YourBaackUpFile.bak'
    GO

    --Step 2: Use the values in the LogicalName Column in following Step.
    --Make Database to single user Mode
    ALTER DATABASE YourDB
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    Go

    --Restore Database
    RESTORE DATABASE YourDB
    FROM DISK = 'C:\YourBaackUpFile.bak'
    WITH MOVE 'YourMDFLogicalName' TO 'C:\Data\YourMDFFile.mdf',
    MOVE 'YourLDFLogicalName' TO 'C:\Data\YourLDFFile.mdf'
    --if you get Error 3154: The backup set holds a backup of a database other than the existing database
    --,REPLACE  --if database you are restoring alrady exists then use replace

    Wednesday, September 23, 2009 6:09 PM
  • I overwrite the existing .bak file while backing up and overwrite the existing database file when restoring. I've done this from the beginning.

    The database is named OPTO, the pendrive is the D: drive, the backup file is o.bak, the mdf file is Opto.mdf, and ldf file is Opto_log.ldf

    I've never used the Query Editor and don't understand "Use master"? Is this what it should look like (the last two lines may be wrong, do I use the full path in both places)? 

    Use master
    Go

    --Step 1: Retrive the Logical file name of the database from backup.
    RESTORE FILELISTONLY
    FROM DISK = 'D:\o.bak'
    GO

    --Step 2: Use the values in the LogicalName Column in following Step.
    --Make Database to single user Mode
    ALTER DATABASE OPTO
    SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    Go

    --Restore Database
    RESTORE DATABASE OPTO
    FROM DISK = 'D:\o.bak'
    WITH REPLACE  'Opto.mdf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Opto.mdf',
    REPLACE  'Opto_log.ldf' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Opto_log.ldf'




    Thank you,

    Dennis
    Friday, October 2, 2009 11:42 AM