Skip to content

Instantly share code, notes, and snippets.

@derjabkin
Created November 11, 2013 07:16
Show Gist options
  • Save derjabkin/7409161 to your computer and use it in GitHub Desktop.
Save derjabkin/7409161 to your computer and use it in GitHub Desktop.
Daily backup of full logged databases with log backup.
if not OBJECT_ID('dbo.Proc_BackupDatabaseDailyWithLog') is null
Drop Proc dbo.Proc_BackupDatabaseDailyWithLog
go
Create Proc dbo.Proc_BackupDatabaseDailyWithLog(
@DbName sysname,
@TargetPath nvarchar(1000)) as
Declare @FileName nvarchar(1000)
Declare @DirPath nvarchar(1000)
Declare @Sql nvarchar(2000)
Declare @Exists int
Select @DirPath = @TargetPath + '\' + @DbName + '\' +
cast(datepart(yyyy,getdate()) as nvarchar(10)) +
(Right('00' + cast(DATEPART(mm, getdate()) as nvarchar(20)),2)) +
(Right('00' + cast(DATEPART(dd, getdate()) as nvarchar(20)),2))
Select @FileName = @DirPath + '\' + @DbName + '.bak'
exec xp_create_subdir @DirPath
exec xp_fileexist @FileName, @Exists OUT
if (@Exists = 0)
Select @Sql = 'backup database ' + QUOTENAME(@DbName) + ' to disk=''' + @FileName + ''''
else
begin
Select @FileName = @DirPath + '\' + @DbName + 'log_' +
right('00' + cast(datepart(HOUR,getdate()) as nvarchar(10)),2) +
right('00' + cast(DATEPART(MINUTE, getdate()) as nvarchar(20)),2) +
right('00' + cast(DATEPART(SECOND, getdate()) as nvarchar(20)),2) +
'.bak'
Select @Sql = 'backup log' + QUOTENAME(@DbName) + ' to disk=''' + @FileName + ''''
end
EXEC sp_executesql @Sql
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment