locked
Backup and restore RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I have one small doubt for example we are taking backup one database on particular instance. The database size is 20 GB and restore into backup into same instance but different database. which one (backup and restore) is taking more time? I mean backup is taking more time (or) restoration is taking more time? Could any one explain about that

    Raveendra

    Monday, July 9, 2012 10:58 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    It depends . If there are many 'un committed' transactions and while restore sql server must bring the database into a consistence state this it needs to roll forward or roll back transactions  and it is huge , it will take some time. It also depends on your network...

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, July 9, 2012 11:05 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    As Uri said ,It will depends because on the external factors like your network & H/w configurations,load on the database engine where you are performing,also threads will also matters here.

    so it is true that restore will take bit time compare to backup because it has to do file initialization(where according to the OS if you have enabled it

    instant file initialization that make little but Backup/restore faster) if your OS and SQL has it's some cobination(refer the link below).

    see

    ---Instant File Initialization Speeds SQL Server

    http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/

    also redo/undo also will matters.. hope this gives some info for you.!

    additional info-

    you can check backup/restore progress,estimation time complete percentag  using the DMV ->sys.dm_exec_requests

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.


    Monday, July 9, 2012 12:01 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Raveendra,

    Restore will take more time, because SQL Server validates first it the backup file is valid and complete, before it starts the restore process.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Monday, July 9, 2012 11:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    It depends . If there are many 'un committed' transactions and while restore sql server must bring the database into a consistence state this it needs to roll forward or roll back transactions  and it is huge , it will take some time. It also depends on your network...

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, July 9, 2012 11:05 AM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    As Uri said ,It will depends because on the external factors like your network & H/w configurations,load on the database engine where you are performing,also threads will also matters here.

    so it is true that restore will take bit time compare to backup because it has to do file initialization(where according to the OS if you have enabled it

    instant file initialization that make little but Backup/restore faster) if your OS and SQL has it's some cobination(refer the link below).

    see

    ---Instant File Initialization Speeds SQL Server

    http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/

    also redo/undo also will matters.. hope this gives some info for you.!

    additional info-

    you can check backup/restore progress,estimation time complete percentag  using the DMV ->sys.dm_exec_requests

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.


    Monday, July 9, 2012 12:01 PM