How to Back Up a Transaction Log in MS SQL Server Management Studio
Posted by Sean Syed on 18 February 2013 06:18 PM
To back up a transaction log
1. After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
4. In the Database list box, verify the database name. You can optionally select a different database from the list.
5. Verify that the recovery model is either FULL or BULK_LOGGED.
6. In the Backup type list box, select Transaction Log.
7. Optionally, you can select Copy Only Backup to create a copy-only backup. A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.
10. Specify when the backup set will expire:
11. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.
To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.
12. To view or select the advanced options, click Options in the Select a page pane.
13. Select an Overwrite Media option, by clicking one of the following:
For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.
For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.
14. In the Reliability section, optionally, check:
15. In the Transaction log section:
A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL.
16. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.
17. SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.