Articles by "Database"

Showing posts with label Database. Show all posts

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 »

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 »

December 19, 2019 ,

Below is the super script to use



create table #dbfiles (

[Database] sysname, 

Name sysname, 

[Type] sysname, 

[Filename] nvarchar(1024), 

Allocated int, 

Used int, 

Available int

) exec sp_msforeachdb 'use [?];insert into #dbfiles

select

''?'' as [Database]

, a.Name

, dbf.type_desc as [Type]

, a.Filename

, convert(int,round(a.Size/128.000,0)) as Allocated

, convert(int,round(fileproperty(a.Name,''SpaceUsed'')/128.000,0)) as Used

, convert(int,round((a.Size-fileproperty(a.Name,''SpaceUsed''))/128.000,0)) as Available

from

dbo.sysfiles a (nolock)

inner join sys.database_files dbf (nolock)

on a.fileid = dbf.file_id

where

db_id(''?'') not in (1,2,3,4)';

select 

  *, 

  'use [' + [Database] + ']; dbcc shrinkfile (''' + [Name] + ''', ' + cast(

    (Used + 1) as nvarchar(16)

  ) + ');' 

from 

  #dbfiles where [type]='LOG'

order by 

  Available desc 

drop 

  table #dbfiles


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 »

June 11, 2019
We can use below script to get database complete free space with allocated and un-allocated details. 
 
This line specifies the table from which the data will be retrieved. The sysfiles table is a system table in SQL Server that contains information about the database files for each database on the server. Overall, this query provides useful information about the size and usage of the database files on a SQL Server instance. 
 SELECT @@servername,getdate (),RTRIM(name) AS [Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]

FROM sysfiles

-- dbcc freesystemcache ('all' ) with mark_in_use_for_removal
-- dbcc opentran 
/*
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 4639)
GO
*/

Test format of the query:

SELECT @@servername,getdate (),RTRIM(name) AS [Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
   CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]

FROM sysfiles

-- dbcc freesystemcache ('all' ) with mark_in_use_for_removal
-- dbcc opentran 
/*
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 4639)
GO
*/

Read more »

February 03, 2018 1

How to get database complete size details

We can use below script to get complete details of SQL database

use master
go
BEGIN  SET nocount ON
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('tempdb..#DBFileInfo'))  BEGIN  DROP TABLE #dbfileinfo  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#LogSizeStats'))  BEGIN  DROP TABLE #logsizestats  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#DataFileStats'))  BEGIN  DROP TABLE #datafilestats  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#FixedDrives'))  BEGIN  DROP TABLE #fixeddrives  END
 CREATE TABLE #fixeddrives  (  DriveLetter VARCHAR(10),  MB_Free DEC(20, 2)  )
 CREATE TABLE #datafilestats  (  DBName VARCHAR(255),  DBId INT,  FileId TINYINT,  [FileGroup] TINYINT,  TotalExtents DEC(20, 2),  UsedExtents DEC(20, 2),  [Name] VARCHAR(255),  [FileName] VARCHAR(400)  )
 CREATE TABLE #logsizestats  (  DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED,  DBId INT,  LogFile REAL,  LogFileUsed REAL,  Status BIT  )
 CREATE TABLE #dbfileinfo  (  [ServerName] VARCHAR(255),  [DBName] VARCHAR(65),  [LogicalFileName] VARCHAR(400),  [UsageType] VARCHAR (30),  [Size_MB] DEC(20, 2),  [SpaceUsed_MB] DEC(20, 2),  [MaxSize_MB] DEC(20, 2),  [NextAllocation_MB] DEC(20, 2),  [GrowthType] VARCHAR(65),  [FileId] SMALLINT,  [GroupId] SMALLINT,  [PhysicalFileName] VARCHAR(400),  [DateChecked] DATETIME  )
 DECLARE @SQLString VARCHAR(3000)  DECLARE @MinId INT  DECLARE @MaxId INT  DECLARE @DBName VARCHAR(255)  DECLARE @tblDBName TABLE (  RowId INT IDENTITY(1, 1),  DBName VARCHAR(255),  DBId INT)
 INSERT INTO @tblDBName  (DBName,  DBId)  SELECT [Name],  DBId  FROM master..sysdatabases  WHERE ( Status & 512 ) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/  ORDER BY [Name]
 INSERT INTO #logsizestats  (DBName,  LogFile,  LogFileUsed,  Status)  EXEC ('DBCC sqlperf(logspace) WITH no_infomsgs')
 UPDATE #logsizestats  SET DBId = Db_id(DBName)
 INSERT INTO #fixeddrives  EXEC master..Xp_fixeddrives
 SELECT @MinId = Min(RowId),  @MaxId = Max(RowId)  FROM @tblDBName
 WHILE ( @MinId <= @MaxId )  BEGIN  SELECT @DBName = [DBName]  FROM @tblDBName  WHERE RowId = @MinId
 SELECT @SQLString = 'SELECT ServerName = @@SERVERNAME,' +  ' DBName = ''' +  @DBName +  ''',' +  ' LogicalFileName = [name],' + ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,' +  ' Size_MB = [size]*8/1024.00,' +  ' SpaceUsed_MB = NULL,' + ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize/1024.00*8 END,'+ ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+ ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+ ' FileId = [fileid],' + ' GroupId = [groupid],' +  ' PhysicalFileName= [filename],' +  ' CurTimeStamp = GETDATE()' + 'FROM [' + @DBName +  ']..sysfiles'
 PRINT @SQLString
 INSERT INTO #dbfileinfo  EXEC (@SQLString)
 UPDATE #dbfileinfo  SET SpaceUsed_MB = Size_MB / 100.0 * (SELECT LogFileUsed  FROM #logsizestats  WHERE DBName = @DBName)  WHERE UsageType = 'Log'  AND DBName = @DBName
 SELECT @SQLString = 'USE [' + @DBName +  '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
 INSERT #datafilestats  (FileId,  [FileGroup],  TotalExtents,  UsedExtents,  [Name],  [FileName])  EXECUTE(@SQLString)
 UPDATE #dbfileinfo  SET [SpaceUsed_MB] = S.[UsedExtents] * 64 / 1024.00  FROM #dbfileinfo AS F  INNER JOIN #datafilestats AS S  ON F.[FileId] = S.[FileId]  AND F.[GroupId] = S.[FileGroup]  AND F.[DBName] = @DBName
 TRUNCATE TABLE #datafilestats
 SELECT @MinId = @MinId + 1 END
 SELECT [ServerName],  [DBName],  [LogicalFileName],  [UsageType]  AS SegmentName,  B.MB_Free  AS FreeSpaceInDrive,  [Size_MB],  [SpaceUsed_MB],  [Size_MB] - [SpaceUsed_MB]  AS FreeSpace_MB,  Cast(( [Size_MB] - [SpaceUsed_MB] ) / [Size_MB] AS DECIMAL(4, 2))  AS  FreeSpace_Pct,  [MaxSize_MB],  [NextAllocation_MB],  ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )  AS alert_switch,  ( B.MB_Free ) + ( ( [Size_MB] - [SpaceUsed_MB] ) -  ( [NextAllocation_MB] ) ) AS  will_be_on_drive,  CASE MaxSize_MB  WHEN -1 THEN Cast(Cast(( [NextAllocation_MB] / [Size_MB] ) * 100 AS INT  ) AS  VARCHAR(10)) +  ' %'  ELSE 'Pages'  END  AS [GrowthType],  [FileId],  [GroupId],  [PhysicalFileName],  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Status'))  AS Status,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Updateability'))  AS Updateability,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Recovery'))  AS RecoveryMode,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'UserAccess'))  AS UserAccess,  CONVERT(SYSNAME, Databasepropertyex([DBName], 'Version'))  AS Version,  [DateChecked]  FROM #dbfileinfo AS A  LEFT JOIN #fixeddrives AS B  ON Substring(A.PhysicalFileName, 1, 1) = B.DriveLetter  ORDER BY ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#DBFileInfo'))  BEGIN  DROP TABLE #dbfileinfo  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#LogSizeStats'))  BEGIN  DROP TABLE #logsizestats  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#DataFileStats'))  BEGIN  DROP TABLE #datafilestats  END
 IF EXISTS (SELECT 1  FROM tempdb..sysobjects  WHERE [Id] = Object_id('Tempdb..#FixedDrives'))  BEGIN  DROP TABLE #fixeddrives  END
 SET nocount OFF END

Below is the query results screen shot captured from the server 


This script is used and tested in 2012 server

Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.