This week I’m going to share a handy full database backup script which I find useful when I have to create an out-of-schedule full database backup. All I do is change the database name, the folder where the backup will be stored and execute. Of course this script does not perform and checks such as:
- that the current user is a member of the sysadmin, db_owner or a db_backupoperator fixed server and database roles respectively;
- that the database exists;
- that the destination folder is a valid path;
- that the database is not the “tempdb”;
- that the database is not in one of Read-Only, Offline, Suspect, or other invalid states;
- any other necessary checks.
USE [master] GO SET NOCOUNT ON; DECLARE @databasename nvarchar(128), -- database name @destfolder nvarchar(256), -- destination folder to store the backup file @fileName nvarchar(256), -- backup file filename @fileDate nvarchar(20), -- date formatted and used for file name @fileTime nvarchar(20), -- time formatted and used for file name @backupsetdescription nvarchar(256); -- backup set identifier SET @databasename = N'AdventureWorks'; SET @destfolder = N'D:\TEMP\'; SET @backupsetdescription = N'AD-Hoc Database Backup'; -- set date and description SET @fileDate = CONVERT(VARCHAR(20),CURRENT_TIMESTAMP,112); SET @backupsetdescription = @backupsetdescription + ' ' + CONVERT(VARCHAR(20),CURRENT_TIMESTAMP,120); -- set time SET @fileTime = CONVERT(VARCHAR(20),CURRENT_TIMESTAMP,108); SET @fileTime = REPLACE(@fileTime, ':', ''); -- backup! SET @fileName = @destfolder + @databasename + '_' + @fileDate + @fileTime + '.BAK'; BACKUP DATABASE @databasename TO DISK = @fileName WITH NAME = @backupsetdescription, INIT, STATS = 25, COPY_ONLY; GO
As you can see the script is quite simple. The backup command used the COPY_ONLY option so that we won’t break the backup chain – note that this option is available in SQL Server 2005 and later only. The script will create a BAK file in the destination folder defined and whose file name will be in the following format:
The script can also be downloaded from here.