T-SQL script to Backup database SQL Server with TimeStamp

The T-SQL script takes a full backup of all databases(System databases and user databases) in the format of DataaseName-YYYY-MM-DD-HH-MM-AM/PM.


The script will create a backup file with the name [DatabaseName]_[YYYYMMDD]_[HH]MMSS.bak in the folder specified (by default).

use Master;
set nocount on
 
declare @timestamp varchar(120)
declare @backuppath varchar(max)
declare @command varchar(max)
set     @backuppath = ('C:\SQL\')
set     @timestamp = (select replace(left(convert(char, getdate(), 120), 10) + '-' + replace(replace(right(getdate(), 8), ' ', ''), ':', '-'), ' ', ''))
set     @command = ('if (''?'') not in (''tempdb'') begin backup database [?] to disk = ''' + @backuppath + '?' + '-' + @timestamp + '.bak'' with INIT,FORMAT,COMPRESSION; end')
exec    master..sp_msforeachdb @command
Note: SQL Server cannot take backup of TempDB system database.

The following screenshot shows the backups taken from the DB script.

Backup database SQL Server with TimeStamp


191 views0 comments

Recent Posts

See All