Database Full Backup Script – Sql Server

Following will run full backup of specified database to specified folder. This script will run copy_only backup with compression. It also includes setting for BUFFERCOUNT and MAXTRANSFERSIZE. 

Backup file will be in “databaseName_backup_yyyy_mm_dd_hhminss.bak” format.


DECLARE @backupfile varchar(100),
@db_name varchar(100), @backup_folder varchar(500);
SET @db_name = 'databaseName';
SET @backup_folder = 'backupLocation';
SET @backupfile = N'' + @backup_folder + @db_name + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(50), getdate(), 20), ' ', '_'), '-', '_'), ':', '') + N'.bak';

DECLARE @backup_cmd nvarchar(4000)
SET @backup_cmd = 'BACKUP DATABASE ' + @db_name + ' TO DISK=''' + @backupfile + ''' WITH COPY_ONLY, FORMAT, INIT,MEDIANAME = N''sql_jobs'',
NAME = N''sql_jobs-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS=5, CHECKSUM, BUFFERCOUNT=100, MAXTRANSFERSIZE=2194304'

EXEC [sp_executesql] @backup_cmd

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>