SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems