Introduction

Here, I am presenting a new and pretty interesting article about SQL backup techniques. Backup and restore process are an essential and important episode of a database. We all are aware with backup and restore processes of SQL Server. Microsoft SQL Server provides high-performance and easy to use backup and restore capabilities. Backup component of SQL Server provides strong facility by which you can protect your critical data stored in a database. Taking proper backup of a database time-to-time prepares you to respond quickly and easily against any data loss or disaster. So let's find out some cool and easy ways of taking bakup in SQL Server.

Database backup - an introduction

In SQL Server, "Backup is a copy of database, which includes copy of all data stored in tables as well as other database objects (like stored procedure, views, functions etc)". Extension of database backup files in SQL Server is .bak. We creates backup's of databases to protect our data loss due to any system failure. Taking backup is an important part of a DBMS.

Let's say you have a database for your company where all data of your company like employee, customer and transaction details get stored everyday. Suppose someday something goes wrong with your system and your system got crashed. Now what? All your company data erased and you lost all details, which can put a full stop on your company's future.
To prevent these kind of risk and losses of data, we have backup database facility in SQL Server. With SQL backup facility we can take backup of databases daily or at some interval of time (1 hour, 5hours etc). All it depends on your data storage frequency and importance. Backing up a database creates the same copy of the database in the form of .bak file.
So if you have proper database backup, then you can recover easily and early from the disaster like above. With proper backup of databases you can restore your latest backup and again start working, which will not cost a lot to you.

So backup plays an important and crucial role in database management systems. Regular backup is an important task for a DBMS. So let's find out in how many ways we can use SQL Server to take backup.

Taking backup using SSMS

SSMS (SQL Server Management Studio) provides facility to take backup of a database. Taking backup using SSMS is not difficult, it's a very basic and easy method of backing up a database. Following are the steps and also visuals displayed; those will tell you how to take a database backup using SSMS.

NOTE: To use SSMS you must have SQL Server 2005 installed

First step is to open SSMS (SQL Server Management Studio). To open SSMS there are two different ways, you can make use of anyone which you like. Two ways are as follows

1. Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio
2. Start -> Run -> Type "sqlwb" -> OK

You can use any way to open SSMS, normally second one is easy and I also use second way.

Then you'll get SSMS will open enter your SQL Server username and password to login. After login you'll get window as below, expand the database node to get list of all databases.

Then right click on database -> Select "Task" -> Click "Back Up..." as below I have done in the following image.

After clicking backup option, database backup window will open, in backup window, under Destination section, choose "Disk" option to save your backup file inside your system. Then click on "Add" button to add the path where you want save your backup file on your disk. After selecting the path click "OK" to complete backup. It will take some time to complete backup, it all depends on your database size, if your database size is huge, then obviously it will take time. After completion a backup file will be created inside the path what you were chosen.

BACKUP (Transact-SQL)

Above method (taking backup using SSMS) includes 5-6 steps to backup a database. SQL Server also have Backup (T-SQL) command using that we can take backup of a database in just one step, that is by executing a simple query backup command in Query Analyzer (which is also SSMS in SQL Server 2005). Here I am just explaining simple backup command syntax, for more detailed explanation about options of backup command visit this link: http://msdn.microsoft.com/en-us/library/ms186865.aspx. A simple backup command to take backup of a database in specified path on the disk is as follows:

BACKUP DATABASE AdvWorks TO DISK = 'C:\backup\AdvWorks.bak' WITH INIT

The above command will take backup of database "AdvWorks" in the path "C:\backup\" with name "AdvWorks.bak" name. WITH INIT option here specifies that any existing database backup with the name "AdvWorks.bak" presents inside "C:\backup\" path will be overwritten.

Backup of all databases (System & User Databases)

Till now we have seen two different methods of taking backup of a single database, what about rest databases. If you have 5 or 10 databases (which is a case normally with web servers where many websites are hosted and each one have their own database inside the same SQL Server) in your SQL Server then its not a logical approach to take backup of all databases one by one.

So what to use if we want to take backup of all databases present in side our SQL Server? Answer is a hidden inside a T-SQL query using which you can take backup of all databases in one go inside the specified path on the disk. For this query we'll use a system stored procedure "SP_MSforeachdb" as follows.

SP_MSforeachdb 'BACKUP DATABASE ? TO DISK = ''C:\backup\?.bak'' WITH INIT'

In the above query we are using a system stored procedure which will run the BACKUP command given inside single quotes ('') for each database one by one. ? (Question mark) in the above command specifies the database name which is handled by the stored procedure. It will put the name of databases automatically one by one.

Let's say you have two databases db1 and db2 in your SQL Server with including the system databases master, msdb, model etc. The stored procedure "SP_MSforeachdb" runs the command specified in the parameter for each databases in the SQL Server including system databases. That means the above query internally runs the Backup command for each database one by one some thing like below, where question mark will be replaced by corresponding database name.

BACKUP DATABASE db1 TO DISK = 'C:\backup\db1.bak' WITH INIT

Then

BACKUP DATABASE db2 TO DISK = 'C:\backup\db2.bak' WITH INIT

Then

BACKUP DATABASE master TO DISK = 'C:\backup\master.bak' WITH INIT

And so on for all user and system databases .....

It's a single query and will take your entire databases backup inside specified path on the disk, you need not to sit and take backup of each databases one by one. Just execute this command and rest the command will do for you. It will take time to execute on the basis of your databases size.

Backup of all user databases

We know how to take backup of entire databases present inside a SQL Server using stored procedure SP_MSforeachdb. But the problem in this stored procedure that it takes backup of all user databases as well as system databases. Backup of system databases normally not required, rare cases where we nee to take backup of system databases (how to take database backup of only system databases, we'll cover this further in this article).
So can we do anything to take backup of only user databases? Answer is yes, you can. Following query will help you to achieve this. You can create an stored procedure for this or you can execute it as a single query in SSMS.

-- command for taking backup of userdefined databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
 @query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
 SELECT NAME FROM MASTER..Sysdatabases 
 WHERE NAME NOT IN ('master', 'model','msdb', 'tempdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
 BEGIN 
 SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''C:\backup\'+ @dbname+'.bak '' WITH INIT' 
 EXEC(@query) 
 FETCH NEXT FROM @cursor INTO @dbname
 END
CLOSE @cursor
DEALLOCATE @cursor

The above query will select the database names from Master..sysdatabases table excluding the system databases. Then we'll run a cursor that will take backup of all user databases one by one. It's a good query when you need to take backup of all your databases. It is usually required when you take backup in a month or in a week. Just execute the above query and it will take the latest backup. Create a stored procedure and just execute that and your done!

Backup of all system databases

Backup of system databases not required normally, but if you have to take backup of system databases. Then you can achieve the same by modifying little bit in the above query. So query which will take backup of only your system databases.

-- command for taking backup of system databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20), 
 @query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..Sysdatabases WHERE NAME IN('master', 'model','msdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''C:\backup\'+@dbname+'.bak '' WITH INIT'
 EXEC(@query)
 FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

As we know that taking backup of system databases is not required. You can take backup of all your user databases on daily basis but for system databases if you want to take backup then execute the above command weekly or on monthly basis and your system databases will also secure, daily backup for system databases is not needed because we do not modify them on daily basis. So the above command will be handy when you want to take backup of only system databases.

Do you take backups manually?

Nope! I am not, taking backup manually on daily basis not an easy task. I make use of SQL SERVER JOBS to take backup automatically. You can make a job which will run on a certain interval of time and will take backup automatically. You can use above commands with SQL jobs to take backup of only user databases or system databases. I have made a job which takes backup of all my user databases weekly and I have also made another job that takes backup of system databases on monthly basis. I have used above commands in my SQL jobs for taking backup of user and system databases separately. That is a separate topic, and I am not going to discuss it here, try goggling to know more about SQL jobs. I am looking forward to write an article on SLQ jobs but till then Google it.

Conclusion

Hope you enjoyed the article. The article tells that there are many ways which can save your time and efforts. The article also tells that there are many commands and queries using those you can take backups of single, or all databases in one go. You can explore much in SQL Server; just you need to work in that area. Please tell me if you know more about this topic, knowledge is all about sharing the knowledge. More you share even more you get!

Thanks
Prashant Pandey