{"id":2605,"date":"2015-10-21T10:12:57","date_gmt":"2015-10-21T14:12:57","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2605"},"modified":"2023-10-17T05:41:49","modified_gmt":"2023-10-17T09:41:49","slug":"database-backup","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/database-backup\/","title":{"rendered":"Database Backup"},"content":{"rendered":"

Everyone who works with SQL Server databases\u00a0first or last faces the need to protect their information. That implies making database backups. So what is a database backup?\u00a0Database backup is a process of saving your database in its present condition. It allows making a copy of your database in case you made some unnecessary\u00a0changes and want to roll back or your primary database is corrupted.<\/p>\n

Types of SQL Server Backups<\/h2>\n

There are three\u00a0main types of backup:<\/p>\n

    \n
  1. Full database backup<\/strong>\u00a0creates a backup copy containing all data files and active part of the transaction log.<\/li>\n
  2. Differential database backup<\/strong>\u00a0creates a backup copy containing all changes made to the database since the last full backup and active part of the transaction log.<\/li>\n
  3. Transaction log backup<\/strong> contains all log records that haven’t been backed up, up to the last log record that exists at the time of backup completion. It has to be admitted, that transaction log backup can be carried out\u00a0only if you use a full or bulk-logged recovery model.<\/li>\n<\/ol>\n

    How Often Should You Backup Your Databases?<\/h2>\n

    Keep in mind that in order to maintain your databases safe and sound you should make backups regularly. The question here is: “How often should I backup my databases?” The answer is: it\u00a0depends on how large your database is, how often you make changes to your database and how important your data is, etc. For example, you can make a full backup every 24 hours, differential backup every 6 hours and backup your transaction log every hour.<\/p>\n

    There are many ways of making database backups. However, in this article we are going to explore only three of them.<\/p>\n

    How to Backup SQL Server using T-SQL Commands<\/h2>\n

    For making\u00a0a full backup<\/strong>\u00a0execute “BACKUP DATABASE” command:<\/p>\n

    BACKUP DATABASE <\/span>your_database <\/em>TO DISK<\/span>\u00a0= 'full.bak'<\/span><\/pre>\n
    For differential backup<\/strong> you need to add “WITH DIFFERENTIAL”\u00a0clause:<\/p>\n
    BACKUP DATABASE <\/span>your_database <\/em>TO DISK<\/span> = 'diff.bak'<\/span> WITH DIFFERENTIAL<\/span><\/pre>\n
    For\u00a0transaction log backup <\/strong>use “BACKUP LOG” command:<\/p>\n
    BACKUP<\/span> LOG<\/span> your_database<\/em> TO DISK<\/span> = 'log.bak'<\/span><\/pre>\n
    How to Backup SQL Server using SSMS<\/h2>\n
    If SQL Management Studio<\/a> is installed on your computer, you can backup\u00a0your database in just a few simple steps as explained in the video below:
    \n