Database Backup in SQL Server 2005 : Part IV


This is the final part of our series of post on Database Backup in SQL Server 2005. In the previous three posts, we discussed how to create a full / differential /t-log backup using SQL Server Management Studio. In the final part of this series, we will discuss how to create the database backup using Transact-SQL query language. Some of you may have a thought, why we have a separate article discussing about t-sql syntax for database backup? The answer is, this series is designed for people like me, who prefer to work from GUI. However, it is also required to learn the t-sql query as well as some of the tasks need to completed using t-sql only. So lets have a look at the syntax for the database backup. We are going to create bakup on SKG_TEST & SKG_TEST_FG databases.
 
Syntax for Full Database Backup
 
The default syntax for creating a Full database backup is as follows:
BACKUP DATABASE <Database_Name> TO <Backup_Location> WITH NAME = <backup_Description>

GO

The transact sql syntax for creating a Full database backup of SKG_TEST database is shown below:

— Creating a Full Backup of "SKG_TEST" Database

BACKUP DATABASE [SKG_TEST] TO DISK = N‘C:\Backup\Test\SKG_TEST_FULL.bak’

WITH NOFORMAT, NOINIT, NAME = N‘SKG_TEST – Full DB Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

To get the complete details about the Backup command, please visit the BOL here.

 

Syntax for Differential Database Backup

 

The default syntax for creating a Differential database backup is as follows:
BACKUP DATABASE <Database_Name> TO <Backup_Location> WITH DIFFERENTIAL, NAME = <backup_Description>
GO
 
The transact sql syntax for creating a differential database backup of SKG_TEST database is shown below:

— Creating a Differential Database backup of "SKG_TEST"

BACKUP DATABASE [SKG_TEST] TO DISK = N‘C:\Backup\Test\SKG_TEST_FULL.bak’

WITH DIFFERENTIAL, NOINIT, NAME = N‘SKG_TEST – 1st Diff DB Backup’

GO

 

Syntax for Transactional Log Backup

 

The default syntax for creating a transactional log backup is as follows:

BACKUP LOG <Database_Name> TO <Backup_Location> WITH NAME = <Log_backup_Description>
GO
 
The transact-sql syntax for creating a t-log backup of SKG_TEST database is shown below:

— Creating a Transaction – Log backup of "SKG_TEST"

BACKUP LOG [SKG_TEST] TO DISK = N‘C:\Backup\Test\SKG_TEST_FULL.bak’

WITH NOINIT, NOFORMAT, NAME = N‘SKG_TEST – 1st T-Log Backup’

GO

 

Note: For this demo, we are using the same backup media set to store all the backups. Mostly the transaction-log backup uses a.trn extension. Just to inform, this is only for naming convention. A t-log backup with .bak extention will also work fine. However, the earlier one is used to keep sync with other DBAs.

 

File – group Backup 
 
The transact-sql syntax for creating a backup of SKG_TEST_FG database is shown below:

 — Creating a Filegroup backup of SKG_TEST_FG

BACKUP

DATABASE [SKG_TEST_FG] FILEGROUP = N‘PRIMARY’,

FILEGROUP

= N‘CustFG’ TO DISK = N‘C:\Backup\Test\SKG_TEST_FG.bak’

WITH

NOFORMAT, NOINIT, NAME = N‘SKG_TEST_FG-1st Full FG Backup’,

SKIP

, NOREWIND, NOUNLOAD, STATS = 10

GO

 

With this, we complete our four part tutorials on Database Backup in SQL Server 2005.

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s