Articles by "Backup"

Showing posts with label Backup. Show all posts

February 07, 2025

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  

DECLARE @fileName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used for file name  

DECLARE @DeleteBakDate DATETIME = Dateadd(day, -5, Getdate()); -- 5-day retention for .bak files

DECLARE @DeleteTxtDate DATETIME = Dateadd(day, -30, Getdate()); -- 30-day retention for .txt files

DECLARE @outputFile VARCHAR(256) -- output log file


-- specify database backup directory

SET @path = '\\servername\foldername\'


-- specify filename format for backup

SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_' + Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')


-- specify output file for job with date format

SET @outputFile = @path + 'Backup_Job_Output_' + @fileDate + '.txt'


DECLARE db_cursor CURSOR read_only FOR  

  SELECT NAME  

  FROM master.dbo.sysdatabases  

  WHERE NAME NOT IN ('master', 'model', 'msdb', 'tempdb') -- exclude these databases


OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name  


WHILE @@FETCH_STATUS = 0  

  BEGIN  

      SET @fileName = @path + @name + '_' + @fileDate + '.bak'  

      

      -- Backup database and capture the output using xp_cmdshell

      DECLARE @cmd VARCHAR(1024)

      SET @cmd = 'ECHO Backing up database ' + @name + ' to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd


      BACKUP DATABASE @name TO DISK = @fileName  

      WITH copy_only, noformat, noinit, skip, norewind, nounload, compression,

           STATS = 10


      -- Log the backup operation result to the output file

      SET @cmd = 'ECHO Backup of database ' + @name + ' completed successfully to ' + @fileName + ' >> ' + @outputFile

      EXEC xp_cmdshell @cmd

      

      FETCH NEXT FROM db_cursor INTO @name  

  END  


-- Delete old .bak files (older than 5 days)

EXEC master.sys.Xp_delete_file 0, @path, 'BAK', @DeleteBakDate, 0


-- Delete old .txt files (older than 30 days)

EXEC master.sys.Xp_delete_file 0, @path, 'TXT', @DeleteTxtDate, 0


CLOSE db_cursor  

DEALLOCATE db_cursor  


-- Final log entry

DECLARE @cmdFinal VARCHAR(1024)

SET @cmdFinal = 'ECHO Backup process completed at ' + CONVERT(VARCHAR(20), GETDATE(), 120) + ' >> ' + @outputFile

EXEC xp_cmdshell @cmdFinal


Read more »

September 09, 2024 ,

 Try this script 

======

DECLARE @BackupPath NVARCHAR(500) = 'D:\Backups\' -- Change this to your desired path

DECLARE @BackupRetentionDays INT = 10

DECLARE @LogRetentionDays INT = 30

DECLARE @CurrentDate DATETIME = GETDATE()

DECLARE @LogPath NVARCHAR(500) = @BackupPath + 'Logs\'

DECLARE @FileName NVARCHAR(500)

DECLARE @DatabaseName NVARCHAR(128)

DECLARE @LogMessage NVARCHAR(MAX)

DECLARE @BackupDeleteDate DATETIME = DATEADD(DAY, -@BackupRetentionDays, @CurrentDate)

DECLARE @LogDeleteDate DATETIME = DATEADD(DAY, -@LogRetentionDays, @CurrentDate)


-- Create directories if they don't exist

DECLARE @CreateDirSQL NVARCHAR(1000)

SET @CreateDirSQL = 'xp_cmdshell ''if not exist "' + @BackupPath + '" mkdir "' + @BackupPath + '"'''

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

EXEC(@CreateDirSQL)

EXEC('xp_cmdshell ''if not exist "' + @LogPath + '" mkdir "' + @LogPath + '"''')


-- Log file setup

DECLARE @LogFile NVARCHAR(500) = @LogPath + 'BackupLog_' + 

                                REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.txt'


-- Initialize log

SET @LogMessage = 'Backup process started at: ' + CONVERT(NVARCHAR(30), @CurrentDate, 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% > "' + @LogFile + '"', no_output


-- Cursor to iterate through all user databases

DECLARE db_cursor CURSOR FOR 

SELECT name 

FROM sys.databases 

WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') 

AND state = 0 -- Online databases only


OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName


WHILE @@FETCH_STATUS = 0

BEGIN

    BEGIN TRY

        -- Generate backup file name

        SET @FileName = @BackupPath + @DatabaseName + '_Full_' + 

                       REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20), @CurrentDate, 120), ':', ''), '-', ''), ' ', '_') + '.bak'

        

        -- Backup command

        BACKUP DATABASE @DatabaseName 

        TO DISK = @FileName

        WITH INIT, STATS = 5, COMPRESSION

        

        -- Log success

        SET @LogMessage = 'SUCCESS: Backup completed for database: ' + @DatabaseName + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

        

    END TRY

    BEGIN CATCH

        -- Log error

        SET @LogMessage = 'ERROR: Failed to backup database: ' + @DatabaseName + ' - ' + ERROR_MESSAGE() + ' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

        EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

    END CATCH

    

    FETCH NEXT FROM db_cursor INTO @DatabaseName

END


CLOSE db_cursor

DEALLOCATE db_cursor


-- Cleanup old backup files (older than backup retention period)

BEGIN TRY

    DECLARE @DeleteBackupCmd NVARCHAR(1000)

    SET @DeleteBackupCmd = 'forfiles /p "' + @BackupPath + '" /s /m *.bak /d -' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteBackupCmd

    

    SET @LogMessage = 'Cleanup: Old backup files deleted (older than ' + CAST(@BackupRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during backup cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH


-- Cleanup old log files (older than log retention period)

BEGIN TRY

    DECLARE @DeleteLogCmd NVARCHAR(1000)

    SET @DeleteLogCmd = 'forfiles /p "' + @LogPath + '" /s /m *.txt /d -' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' /c "cmd /c del /q @path"'

    EXEC xp_cmdshell @DeleteLogCmd

    

    SET @LogMessage = 'Cleanup: Old log files deleted (older than ' + CAST(@LogRetentionDays AS NVARCHAR(3)) + ' days)' + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END TRY

BEGIN CATCH

    SET @LogMessage = 'ERROR during log cleanup: ' + ERROR_MESSAGE() + CHAR(13) + CHAR(10)

    EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output

END CATCH


-- Final log entry

SET @LogMessage = 'Backup process completed at: ' + CONVERT(NVARCHAR(30), GETDATE(), 120) + CHAR(13) + CHAR(10)

EXEC master.dbo.xp_cmdshell 'echo %LogMessage% >> "' + @LogFile + '"', no_output


-- Reset xp_cmdshell for security

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE


PRINT 'Backup process completed. Check log file at: ' + @LogFile

======


Read more »

December 04, 2022 1 ,
Monitoring Estimated Completion Times for Backup, Restore, and DBCC Commands in SQL Server In the world of database administration, long-running operations like backups, restores, and DBCC commands can leave us staring at the screen, wondering how much longer they’ll take. To alleviate this uncertainty, I’ve got you covered with a script that monitors the progress and provides estimated completion times for these critical tasks. 

 [code type="SQL"] SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id, s.text, start_time, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')[/code] 
This code can be executed SQL server 2008 or later.


Tsql code: 

SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,
s.text,
start_time,
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in  ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

May 05, 2020 ,

 we have use below TSQL script to take all database backup jobs 


DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DeleteDate DATETIME = Dateadd(hh, -22, Getdate());
-- specify database backup directory
SET @path =
'\\servername\foldername\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20), Getdate(), 112) + '_'
                   + Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '')
DECLARE db_cursor CURSOR read_only FOR
  SELECT NAME
  FROM   master.dbo.sysdatabases
  WHERE  NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb'
                     ) -- exclude these databases
OPEN db_cursor
FETCH next FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
      BACKUP DATABASE @name TO DISK = @fileName WITH copy_only, noformat, noinit
      ,
      skip, norewind, nounload, compression, stats = 2
      FETCH next FROM db_cursor INTO @name
  END
EXEC master.sys.Xp_delete_file
  0,
  @path,
  'BAK',
  @DeleteDate,
  0;
CLOSE db_cursor
DEALLOCATE db_cursor 


Read more »

February 26, 2020 , ,

 we can use the below script to check database backup restore percentage details and many more.



use master;

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, 
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 
FROM sys.dm_exec_sql_text(sql_handle))) 
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

December 29, 2019 ,

Below script can be used to get information about the SQL databases,

select backup_start_date, backup_finish_date, database_name,type,name,user_name,description
from msdb.dbo.backupset
where type='D' ----- I for diff, D for full and L for log
and database_name='master'
order by backup_start_date desc








we will need to use D, I and L for the backup type and should also be change for the required database name.
Read more »

July 04, 2019 ,
  • Use below TSQL script to get a complete history of single database backup history, you will need to change the backup type from D to I  and L to check Differential and Log backup history respectively. 


select @@servername [Server Name],backup_start_date, backup_finish_date, database_name,type,name,user_name,description
,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB
,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB
from msdb.dbo.backupset 
where type='D' ----- I for diff, D for full and L for log
and database_name='msdb'  
order by backup_start_date desc 


  • Below script can be used when want to see whole 7 days database backup history of whole week for all the databases of SQL instance.
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

  • Below Script can be used to get database backup history of one database for 40 days,
select backup_start_date, backup_finish_date, database_name,type,name,user_name,description
,(backup_size /1024.00 /1024.00 /1024.00) as backup_size_GB
,(compressed_backup_size /1024.00 /1024.00 /1024.00) as Compressed_backup_size_GB
from msdb.dbo.backupset 
where database_name='msdb'  
and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 40) 
order by backup_start_date desc


Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.