Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server BACKUP DATABASE command



By:
Overview

There are only two commands for backup, the primary is BACKUP DATABASE.  This allows you to do a complete backup of your database as well as differential, file, etc. backups depending on the options that you use.

Explanation

The BACKUP DATABASE command gives you many options for creating backups.  Following are different examples.

Create a full SQL Server backup to disk

The command is BACKUP DATABASE databaseName.  The "TO DISK" option specifies that the backup should be written to disk and the location and filename to create the backup is specified.

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

Create a differential SQL Server backup

This command adds the "WITH DIFFERENTIAL" option.

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

Create a file level SQL Server backup

This command uses the "WITH FILE" option to specify a file backup.  You need to specify the logical filename within the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILE = 'TestBackup' 
TO DISK = 'C:\TestBackup_TestBackup.FIL'
GO

Create a filegroup SQL Server backup

This command uses the "WITH FILEGROUP" option to specify a filegroup backup.  You need to specify the filegroup name from the database which can be obtained by using the command sp_helpdb 'databaseName', specifying the name of your database.

BACKUP DATABASE TestBackup FILEGROUP = 'ReadOnly' 
TO DISK = 'C:\TestBackup_ReadOnly.FLG'
GO

Create a full SQL Server backup to multiple disk files

This command uses the "DISK" option multiple times to write the backup to three equally sized smaller files instead of one large file.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks_1.BAK',
DISK = 'D:\AdventureWorks_2.BAK',
DISK = 'E:\AdventureWorks_3.BAK'
GO

Create a full SQL Server backup with a password

This command creates a backup with a password that will need to be supplied when restoring the database.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH PASSWORD = '[email protected]#R$'
GO

Create a full SQL Server backup with progress stats

This command creates a full backup and also displays the progress of the backup.  The default is to show progress after every 10%.

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

Here is another option showing stats after every 1%.

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

Create a SQL Server backup and give it a description

This command uses the description option to give the backup a name.  This can later be used with some of the restore commands to see what is contained with the backup.  The maximum size is 255 characters.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
WITH DESCRIPTION = 'Full backup for AdventureWorks'
GO

Create a mirrored SQL Server backup

This option allows you to create multiple copies of the backups, preferably to different locations.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.BAK'
WITH FORMAT
GO

Specifying multiple options for SQL Server Backups

This next example shows how you can use multiple options at the same time.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.BAK'
WITH FORMAT, STATS, PASSWORD = '[email protected]#R$'
GO

Last Update: 3/12/2009




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, October 15, 2018 - 12:49:40 PM - Ricardo Back To Top

 I have SQL 2017 and try to get backup from SQLCMD (in scheduler, or cmd, or task.bat) . I can connect on SQL with cmd perfect but If I try this:

SQLCMD -E -S LOCALHOST\SQLEXPRESS -Q "BACKUP DATABASE mydatabasename TO DISK='D:\backup\test\backup\mydatabasename_Mon.bak' WITH FORMAT" 

Nothing happens can you help me?


Friday, December 16, 2016 - 12:49:50 AM - akhil Back To Top

 how to run the same sql query in command prompt

 

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

Wednesday, June 22, 2016 - 5:51:19 AM - khaleel zourob Back To Top

 

 this  code not work in MS SQL Server 2012

 

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
MIRROR TO DISK =  'D:\AdventureWorks_mirror.BAK'
WITH FORMAT, STATS, PASSWORD = '[email protected]#R$'
GO
Password not supported


Wednesday, February 17, 2016 - 9:39:18 AM - Greg Robidoux Back To Top

Hi Gehad,

One option would be to create a SQL Agent job as a one time run.  You could have your VB.Net app create the SQL job and take the database and datetime as parameters.  Another option would be to create a Windows Scheduled Task on the fly and do the same thing and use a SQLCMD file to run the backip.

-Greg


Wednesday, February 17, 2016 - 9:11:34 AM - Gehad Back To Top

How i can create schedule Database back up by vb.net to specify the time and the date of back up
or try to access maintenance plan of sql server by vb.net

any solution to give user the permission to enter the date and time of backup his database


Learn more about SQL Server tools