SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Backup Database in SQL Server

Step 11. Under Compression section, you can select the Compress backup from the drop down list as shown in the below snippet. Microsoft introduced Database Backup Compression Feature in SQL Server 2008 for Enterprise Edition. To know more about Backup Compression read the article titled Implement Backup Compression Feature In SQL Server 2008.

Step 12. To generate T-SQL Script for the database backup click Scripts and choose Script Action to your choice from the different options which are available as shown in the snippet below.

Generate Database Backup Script Using SSMS

Step 13. Once the database is successfully backed up you will get a popup message similar to the one shown in below snippet.

Successful Database Backup Message in SSMS

How to Take Full Database Backup in SQL Server Using T-SQL Script

Below TSQL script can be used to take the Full Backup of AdventureWorks database. You need to set the path of the folder where you want to take the full back by modifying DatabaseBackupLocation.

DECLARE @BackupDatabaseName          SYSNAME
DECLARE @DatabaseBackupLocation      NVARCHAR(120) 

SET @BackupDatabaseName = 'AdventureWorks'
SET @DatabaseBackupLocation = 'C:\DBBackups\AdventureWorks.BAK'

/* Database Backup Script Using Compression Feature of SQL Server 2008 */
BACKUP DATABASE  @BackupDatabaseName
TO  DISK = @DatabaseBackupLocation
WITH NOFORMAT, NOINIT, 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */
RESTORE VERIFYONLY
FROM DISK = @DatabaseBackupLocation
GO
TSQL Script to Take Full Database Backup in SQL Server

Conclusion

This article explains the steps you need to follow to take a Full Database Back of an SQL Server Database.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement