How to backup data in MS SQL Server

Microsoft’s SQL Server is a strong competitor in the market of Data Base Management Systems. It is a complete data management system as well as data analysis tool that is highly reliable and secure. It is used by several organizations for managing their critical databases.

DBA’s need to take regular backups of the critical database in order to ensure there is no loss of data. Here is how it can be done.

Taking Backup

SQL Server Management Studio

MS SQL Server comes with a SQL Server Management Studio (SSMS) very similar to the Enterprise Manager of its predecessors. In it you can right click on a database and click on Tasks | Backup Database to bring up the shown screen. You need to select the database, the backup type whether you want a full backup or a selective backup and backup file name. In options you can also specify if the backup is to be appended on the existing backup file or overwrite it.

Through SQL statements

Here is a simple query that would take back up of a database ‘dbname’ to the path ‘d:\dbbackups\’ with the name ‘dbname_backup.bak’:

        BACKUP DATABASE dbname

        TO DISK = ‘d:\dbbackups\dbname_backup.bak’

Restoring the Backup

SQL Server Management Studio

To restore from a backup right click on databases in SSMS and click on Tasks | Restore Database. You will again have to select the backup file from which the data will be restored. When you do, on clicking next you will be asked whether you want to overwrite the existing data, append to the existing data, select the options based on your requirements and press ok. The database will then be restored.

Through SQL Statements

Here is a simple query that would restore a database ‘dbname’ from ‘c:\dbbackups\’ from the file ‘dbname_back.bak’

        RESTORE DATABSE dbname

        FROM DISK = ‘d:\dbbackups\dbname_backup.bak’

        WITH

        MOVE ‘dbname_data’ TO ‘d:\SQLServer2005\Data\dbname_new.mdf’

        MOVE ‘dbname_log’ TO ‘d:\SQLServer2005\Data\dbname_log.ldf’