Full Database Backup

ByDr. SubraMANI Paramasivam

Full Database Backup

This post is to cover some of the backup options that we have. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.

In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…


Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and you see “Full” is selected. If you click on the other options you will then see options for other types of backups to be chosen.


Then I have highlighted “Backup set”, which means I am giving a name for it. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the provided default location.

You can also use below T-SQL Scripts to do the same manually instead of using GUI screens.


–Syntax for Full Database Backup
BACKUP DATABASE databasename
TO backup_device [ ,…n ] –Device and Location
[ WITH with_options [ ,…o ] ] ;

–Simple Script to take Full Database Backup
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\AdventureBackup.bak’

–Craete a Full database backup by using WITH FORMAT to overwrite any exisiting backups and Create a new media set
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\\AdventureWorksSimpleRM.bak’

Happy going.

About the Author

Dr. SubraMANI Paramasivam administrator

Dr.SubraMANI Paramasivam is a Data Platform MVP, Power BI Group Leader, International Speaker, Microsoft Certified Trainer and Principal Solutions Architect at DAGEOP Ltd, having 20+ years of DB, BI & Analytics experience. Dr.SubraMANI Paramasivam is a frequent speaker in SQLBITS, PASS Summit, PASS SQL Saturdays, SQLRelay, Data Platform Summit, MCT Summit, SQLServerGeeks Summit, INSIDESQL UK, Data Awareness Programmes, Data Day Events. He likes to enlighten the education & data and started his own journey with FREE Data Awareness Programme (www.dataap.org), in remote areas who are in need. Mani speaks a lot about Visualization, Data Science, R Analytics, Azure concepts, Performance Tuning, DB & SQL Server Architecture, DBA, Partitioning, Resource Governor, Disaster Recovery, High Availability, Development, T-SQL Programming and Microsoft certifications.

Comments Are Closed!!!