SQL Server Database Backup

Backup is an vital part for any database system. It reduce accidental data loses. This article describes how to create a database backup in SQL query. Summary of the article:

• What is Database Backup?
• SQL Script to Backup Single SQL Server Database
• SQL Script to Backup all SQL Server Databases

What is Database Backup?
Database Backups creates a duplicate of the entire database. It is an important part of any database related application. We can create a backup of  database in many ways:

1. SQL Server Management Studio
2. SQL Scripts

We can backup our database by using SQL Server Management Studio. It is easy and simple for limited number of database. But when there are a lot of database in a single SQL Server then  SQL Server Management Studio is not good. It is time consuming.  For that case SQL Scripts is the best. It is faster and reduce time.

SQL Script to Backup Single SQL Server Database
The following SQL query will backup a particular SQL Database. The simple command for this is “BACKUP DATABASE DatabaseName”.  The “TO DISK” option specifies that the backup should be written to disk in a path. Path means the location and filename. The SQL script is:

BACKUP DATABASE MyDataBaseName TO DISK = 'D:/FileName.bak'

SQL Script to Backup all SQL Server Databases
This simple query will backup all the databases existing in a SQL server except System Databases (master,model, msdb, tempdb) or we can also create a custom list to skip. This T-SQL or SQL script will backup all SQL Server Databases. We need to change the Path to set required backup directory. The file name format is DatabaseName_YYYYMMDD.BAK.

DECLARE @DBName VARCHAR(250)	--Database name
DECLARE @Path VARCHAR(250)	--Path for backup files
DECLARE @FileName VARCHAR(250)	--File name for backup
DECLARE @DateTime VARCHAR(20)	--Current date time

SET @Path = 'D:\'
SELECT @DateTime = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ( 'master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @FileName = @Path + @DBName + '_' + @DateTime + '.BAK'
	BACKUP DATABASE @DBName TO DISK = @FileName
	FETCH NEXT FROM db_cursor INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor

In this way we can backup all of our databases easily.