locked
Cold backup in sql server 2008 r2 RRS feed

  • Question

  • Dear all,

    I want to know about cold backup how we can do this in sql server 2008 r2.

    Tuesday, September 27, 2011 9:28 AM

Answers

  • You don't perform cold backups of SQL Server like you would an Oracle database.  The database can be backed up in a crash consistent state using BACKUP DATABASE at any point in time, even with the database is online and being used by end users.  The way this occurs is that the FULL backup of the database also includes the active portion of the transaction log for the time period that the backup was running, allowing the crash recovery process to roll forward(Redo)/rollback (Undo) the changes being made while the backup blocks are being read from disk.  

    You don't ever have to shutdown SQL Server to get a consistent backup of the database, and you also never have to take a database offline or detach a database to get a backup of it.  


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 5:25 AM
    Tuesday, September 27, 2011 3:59 PM

All replies

  • If a database is offline  you can copy/backup mdf/ldf files to be attached later on... But if you db is set to READ ONLY you can easily backup it by using native BACKUP DATABASE command
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, September 27, 2011 9:35 AM
    Answerer
  • If a database is offline  you can copy/backup mdf/ldf files to be attached later on... But if you db is set to READ ONLY you can easily backup it by using native BACKUP DATABASE command
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, September 27, 2011 9:36 AM
    Answerer
  • Dear all,

    I want to know about cold backup how we can do this in sql server 2008 r2.


    Nope SQL server doesn't have cold backup or command. It's in oracle.

    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>>SqlserverBlogForum
    Tuesday, September 27, 2011 10:04 AM
  • Can you help us understand what you mean by "cold backup".
    Tuesday, September 27, 2011 3:48 PM
  • Treat any backup done in MSSQL as equivalent to a hot* backup in Oracle as it pertains to keeping the database open and active during the backup process.  Outside of the immediate backup window though, the recovery model of the database (along with certain activity if in bulk-logged mode) will dictate if you can do point-in-time restores or not.

    Check out this BOL article for the full rundown: http://msdn.microsoft.com/en-us/library/ms189275.aspx

    Hope that helps,

    John


    John Eisbrener - http://dbaeyes.com/
    Tuesday, September 27, 2011 3:50 PM
  • You don't perform cold backups of SQL Server like you would an Oracle database.  The database can be backed up in a crash consistent state using BACKUP DATABASE at any point in time, even with the database is online and being used by end users.  The way this occurs is that the FULL backup of the database also includes the active portion of the transaction log for the time period that the backup was running, allowing the crash recovery process to roll forward(Redo)/rollback (Undo) the changes being made while the backup blocks are being read from disk.  

    You don't ever have to shutdown SQL Server to get a consistent backup of the database, and you also never have to take a database offline or detach a database to get a backup of it.  


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Stephanie Lv Wednesday, October 5, 2011 5:25 AM
    Tuesday, September 27, 2011 3:59 PM