SQL Server Full Backups



By:
Overview

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

Explanation

A full backup can be completed either using T-SQL or by using SSMS.  The following examples show you how to create a full backup.

Create full SQL Server database backup to one disk file

T-SQL

This will create a full backup of the AdventureWorks database and write the backup contents to file "C:\AdventureWorks.BAK".  The .BAK extension is commonly used for identifying that the backup is a full database backup.

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

SQL Server Management Studio

  • Right click on the database name
  • Select Tasks > Backup
  • Select "Full" as the backup type
  • Select "Disk" as the destination
  • Click on "Add..." to add a backup file and type "C:\AdventureWorks.BAK" and click "OK"
  • Click "OK" again to create the backup
backup database with ssms

Here is what this looks like in SSMS 17.

backup database with ssms

Last Update: 2/22/2019






Comments For This Article




Wednesday, January 29, 2020 - 8:56:12 AM - Greg Robidoux Back To Top

Hi Tim, you need to check to make sure the SQL Server account that runs the Database Engine has permissions to write to that location.

You could try to use this command to see what comes back:

xp_cmdshell 'dir c:\users\transactions'

-Greg


Wednesday, January 29, 2020 - 7:49:36 AM - Tim Back To Top

I'm trying to do a backup (bak) file of my database, when i change the file location i get this error message "TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'LAPTOP-*******\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Users\transactions'. Operating system error 5(Åtkomst nekad.). (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&LinkId=20476
------------------------------


Wednesday, February 14, 2018 - 9:01:06 AM - Pawan Back To Top

 hi ,

Take a example where recovery mode is in full and we change in between it to simple mode when the TR space is 50%(ex-32 GB) full.

What i have noticed is that the time you change the recovery mode , space goes to 0% full. so , what is happing with 32 GB data ?

 

Regards

Pawan

 

 

 


Wednesday, April 20, 2016 - 3:24:07 PM - Amit yadav Back To Top

 thanks a lot :)

very helpfull article

 

:)

 



download


get free sql tips
agree to terms


Learn more about SQL Server tools