MS SQL Server Backup Script
Microsoft SQL Server database administrators and T-SQL programmers can use SQL Server backup script to take sql backup of their sql databases.
Of course, Microsoft SQL Server data platform professionals can also use MS SQL Server Management Studio SQL Server Backup Database Tool (SQL Server Backup Database Wizard) for taking sql backups of sql databases.
Here in this sql tutorial, the given sql backup script is very simple to use in informal backup processes.
Important : I strongly suggest to use database maintenance plans to take reqular sql backups of your databases using scheduled sql jobs.
SQL admins and developers will realize that the only parameters or variables passed to the sql backup script is the SQL Server database name and the full path of the sql backup file.
BACKUP DATABASE [SQL Database]
TO DISK = 'D:\SQLDatabase.bak'
As t-sql developers can see easily, the dbname for sql backup database for this sample script is "SQL Database".
It is in brackets since it is better to use brackets in case the sql database name has spaces in its name as it is in this example.
After the SQL Server backup script is executed, the SQL engine will issue an output informing the details of sql backup command.
Here is the information about and the status of sql database backup command execution.
Processed 21576 pages for database 'SQL Database', file 'SQL Database' on file 1.
Processed 2 pages for database 'SQL Database', file 'SQL Database_log' on file 1.
BACKUP DATABASE successfully processed 21578 pages in 5.141 seconds (32.789 MB/sec).
Alternative SQL Backup Script
Of course, if you want to create a dynamic sql backup script where you do not want to modify the database name and sql backup file name and file path each time, you can create an alternative sql backup script.
DECLARE @BackupSQLScript nvarchar(max)
DECLARE @SQLBackupFileName nvarchar(400)
DECLARE @DatabaseName sysname
DECLARE @SQLBackupFolder nvarchar(400)
SET @DatabaseName = 'SQL Database'
SET @SQLBackupFolder = 'D:\'
SET @SQLBackupFileName =
REPLACE(@DatabaseName,' ','-') +
CONVERT(VARCHAR,GETDATE(),112) + '.bak'
SET @BackupSQLScript = '
BACKUP DATABASE [' + @DatabaseName + ']
TO DISK = ''' + @SQLBackupFolder + @SQLBackupFileName + ''''
print @BackupSQLScript
EXEC sp_executesql @BackupSQLScript
The dynamic sql creates the following t-sql backup script command where if you require add hour and minute information.
BACKUP DATABASE [SQL Database]
TO DISK = 'D:\SQL-Database20100707.bak'
If you want to use BACKUP DATABASE command in a sql backup script which will help you take sql backup for all SQL Server databases, you can review sql tutorial SQL Backup Script for All Databases in SQL Server Instance