Tag Archives: TableVariable

Find If Mix Backup Types are Present

Backups, Reports, ,

There are mainly two types of backups:

  • Native SQL backups (either stored to local disk or a network location)
  • API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

  


/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db

Thank you Andrey for the idea to create this script

Shrink All Transaction Logs

InsideDatabases, Script Generator

The following script loops through all the Tlogs and attempts to shrink them. Remember that a Tlog can be shrunk only if there is empty space at the end. In order to tell where the empty space is, run: 


use [database]
DBCC LOGINFO

If the Status column is 0, then it's empty. If you do not have empty space at the end, it will not shrink, so you need to run Tlog backups. Sometimes you need to run Tlog backups several times for the portion that's empty to move to the end of the file. More on that here:

http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

Now, for the script: 

/*
Get a list of Databases and its Tlogs, and shrink them
Do the following.
1- Run Tlog Backups
2- Run this script

Author: Miguel Quintana

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

Does not work:
MSSQL2000

*/




DECLARE @numRec INT
DECLARE @counter INT
DECLARE @DBName SYSNAME
DECLARE @Tlog SYSNAME
DECLARE @CMD NVARCHAR(MAX)

DECLARE @table TABLE
(RowID INT IDENTITY(1, 1), dbname SYSNAME, tlogname SYSNAME)

-- create a table with the DBname and its Tlog file
-- skip offline and system DBs
INSERT INTO @table 
SELECT db_name(database_id), name FROM sys.master_files 
WHERE type_desc = 'LOG' and state_desc ='ONLINE' and database_id > 4

-- Set and initialize counters
SELECT @numRec=count(*), @counter=1 FROM @table

WHILE  @counter <= @NumRec
BEGIN 

SELECT @DBName = dbname, @Tlog = tlogname FROM @table WHERE RowID = @counter

SET @CMD='USE ' + quotename(@DBName, N'[') + 
                        N'
CHECKPOINT
DBCC SHRINKFILE (['+@Tlog+'], 0, TRUNCATEONLY)'

print @CMD
-- EXECUTE (@CMD)

set @counter=@counter+1
END

 

Ok.. well… what happen if you are forced to shrink the Tlog and you don't have space for backups? Now what?

There is a way where you can take a backup "into the ether"… tricking the database into dumping all pages and forcing to mark the Tlog as empty pages.

Doing this will not cause data loss, however it will invalidate the backup chain, and a full backup should be taken as soon as possible.

 

  


BACKUP LOG [AdventureWorks2012] TO  DISK = N'NUL'

 

Restore Generator for all BAK files in one folder

restore, Script Generator, ,

Planning restore operations ahead of time is important to determine the space needed and in automation task Here, we provide the actual path to the folder where the BAK files are kept, and we generate a resotre script using the RESTORE HEADERONLY and FILELISTONLY operations for all databases on that folder. 

/*
DESCRIPTION Script Generator
It creates the RESTORE scripts for all backup (bak) files on a directory
and lists the space needed AT THE BOTTOM of output.
 
Configuration At the top:
 
Compatibility list:
MSSQL2008
MSSQL2012
 
 
Does not work:
MSSQL2005 for SQL2005 to work, the two tables for restore fileheaderonly and filelistonly need to be modified.
 
*/
set nocount on
 
DECLARE @pathname VARCHAR(1024)
DECLARE @DataPath NVARCHAR(3000)
DECLARE @TlogPath NVARCHAR(3000)
DECLARE @RELOCATE INT
DECLARE @OVERRIDE INT
 
 
--************** CONFIGURATION **************
 
 
-- Set the pathname for the location of the backup files
-- must end wit ha backslash \
 
set @pathname = '"t:\bk"'

-- Overrider: Say 1 if the database already exist and needs to be over written
-- Overrider: Say 0 if the database is new to this server
 
set @OVERRIDE = 1
 
--RELOCATE: If you want to relocate the data files, say 1 and fill in the following:
-- if not, say 0 and skip.
 
set @RELOCATE = 1
-- Data files must end wit ha backslash
set @DataPath = 'E:\MSSQL\DATA\'
-- Tlog Files must end wit ha backslash
set @TlogPath = 'F:\TlogFolder\'
 
--************** END CONFIGURATION **************
 
 
/*
**************************************************
**************************************************
Declare Variables DO NOT MODIFY BELOW
**************************************************
**************************************************
*/
DECLARE @databasename NVARCHAR(1000)
DECLARE @physicalName NVARCHAR(1000)
DECLARE @filename NVARCHAR(1000)
DECLARE @filepath NVARCHAR (1000)
DECLARE @temp1 NVARCHAR (1000)
DECLARE @temp2 NVARCHAR (1000)
DECLARE @temp3 NVARCHAR (1000)
DECLARE @move NVARCHAR (4000)
DECLARE @CMD VARCHAR(512)
DECLARE @bksizeTotal BIGINT
DECLARE @DATAsizeTotal BIGINT
DECLARE @TLOGsizeTotal BIGINT
 
 
-- initialize size counters
set @bksizeTotal = 0
set @DATAsizeTotal = 0
set @TLOGsizeTotal = 0


-- Create Tables to store data
  IF OBJECT_ID('tempdb..#filelist') IS NOT NULL
DROP TABLE #filelist
IF OBJECT_ID('tempdb..#header') IS NOT NULL
DROP TABLE #header

CREATE TABLE #filelist
(
LogicalName NVARCHAR(1024),PhysicalName NVARCHAR(1024),[Type] char(1),FileGroupName NVARCHAR(128),Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0),Fileid BIGINT, CreateLSN numeric(25,0),
DropLSN numeric(25, 0),UniqueID UNIQUEIDENTIFIER,ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),BackupSizeInBytes BIGINT,SourceBlocSize INT,FileGroupId INT,
LogGroupGUID UNIQUEIDENTIFIER,DIFferentialBaseLSN NUMERIC(25,0),DIFferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,IsPresent BIT
)
CREATE TABLE #header
(
BackupName NVARCHAR(512),BackupDescription NVARCHAR(255),[BackupType] SMALLINT,ExpirationDate DATETIME,Compressed BIT,
Position SMALLINT,DeviceType TINYINT,UserName NVARCHAR (128)
,ServerName NVARCHAR (128),DatabaseName NVARCHAR(512),DatabaseVersion INT
,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN NUMERIC(25,0),CheckPointLSN NUMERIC (25,0),
DBBKLSN NUMERIC (25,0),BackupStartDate DATETIME,BackupFinishDate DATETIME
,SortOrder SMALLINT,[CodePage] INT,UnicodeLocal INT,UnicodeComp INT,CompLevl INT,softwVendId INT,SoftVersionMajor INT,
SoftVersionMinor INT,SoftVersionBuild INT,MachineName NVARCHAR(128)
,flag INT,BindingId UNIQUEIDENTIFIER,RecoveryForkID UNIQUEIDENTIFIER,collation Nvarchar(128)
,FamilyGUID UNIQUEIDENTIFIER,hasbulkloggedata BIT,issnapshot BIT,isreadonly BIT
,issingleuser bit,hadbackupchecksums bit, isdamaged bit, beginslogchain bit, HasIncomplMetaData bit
, isforceoffline INT,iscopyonly INT,FirstRecoveryForkID UNIQUEIDENTIFIER, forkpointlsn NUMERIC (25,0), RecoveryModel NVARCHAR(60),
dIFfBaseLSN NUMERIC (25,0), dIFfbaseGUID UNIQUEIDENTIFIER
,backupTypeDesc NVARCHAR (50),BKSETGUID UNIQUEIDENTIFIER
)

 
DECLARE @Version numeric(18,10)
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

-- If this is SQL2008 or  higher, add more columns to support compression
IF @VERSION >= 10
BEGIN
ALTER TABLE #filelist
ADD TDEThumbPRINT bit
 
ALTER TABLE #header
ADD CompressedBackupSize BIGINT 
END
-- If this is SQL2012 or  higher, add more columns to support contained database
IF @Version >= 11
BEGIN
ALTER TABLE #Header
ADD containmnet bit
END

IF OBJECT_ID('tempdb..#CommandShell') IS NOT NULL
DROP TABLE #CommandShell


CREATE TABLE #CommandShell
(	Line VARCHAR(1024)	)
-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
 
-- these variables keep track if we changed these options, we'll change them back before finishing
 

DECLARE @chkOLE as sql_variant
DECLARE @adv_opt as sql_variant
DECLARE @op_ch1 as INT
DECLARE @op_ch2 as INT
print '/* 
Ignore this commented block'
set @op_ch1=0
set @op_ch2=0
select @adv_opt = value from sys.configurations where name = 'show advanced options'
if @adv_opt = 0
begin
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;
set @op_ch1 = 1
end
select @chkOLE = value from sys.configurations where name = 'xp_cmdshell'
if @chkOLE = 0
begin
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE;
set @op_ch2 = 1
end


-- Adds CMD compatible quotation marks if there is none
IF ((SELECT CHARINDEX('"',@pathname))=0)
SET @pathname = '"'+@pathname+'"'

-- Set the DIRECTORY to search for backups
SET @CMD = 'DIR ' + @PathName + ' *.bak /A /B'
 
-- Populate table with the files in directory
 
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell @CMD
-- Delete lines not containing a filename
DELETE FROM #CommandShell WHERE Line is null
 
-- checking if we change settings, and reverting them
 
if @op_ch2 = 1
begin
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE;
end
if @op_ch1 = 1
begin
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;
end
print '
Ignore this commented block*/ 
'

IF EXISTS (select TOP 1 (Line) from #CommandShell where Line = 'Access is denied.')
BEGIN
Print '
Either the SQL Server does not have access to the path, or it does not exist'
print @pathname
GOTO EOF
END
--- end CMDshell

-- Removing double quotations from pathname
set @pathname = REPLACE(@pathname,'"','')

-- Makes sure path ends with a slash, and adds one if needed
IF ((SELECT CHARINDEX('\',reverse(@pathname)))>1)
SET @pathname = @pathname+'\'

-- Removing Last line of table
delete from #CommandShell where Line = 'File Not Found' 

-- First Cursor, grabs a file, and extracts the database name
-- **********************************************
-- **********************************************
-- ********************************************** 
DECLARE LIST CURSOR FOR
SELECT @pathname+Line FROM #CommandShell
 
 
OPEN LIST
FETCH NEXT from LIST INTo @temp3
WHILE (@@fetch_status = 0)
BEGIN
 
-- populate the tables with the BAK file info
insert #header
exec ('restore headeronly from disk = ''' + @temp3 + '''')
 
insert #filelist
exec ('restore filelistonly from disk = ''' + @temp3 + '''')
 
-- Grabs DATABASE name for the BAK file
 
select @databasename=DatabaseName from #header
 
-- start adding size info
select @bksizeTotal=@bksizeTotal+backupsize from #header
 
set @move = '
-- *************************************************
-- Restore Script For Database ' + @databasename + '
-- *************************************************
'
if @OVERRIDE=1
set @move=@move+'ALTER DATABASE ['+@databasename+'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
set @move=@move+
'
RESTORE DATABASE ['+@databasename+']
FROM DISK ='''+@temp3+'''
WITH FILE = 1,
'
 
-- Second Cursor for data files, nested.
 
DECLARE cur CURSOR FOR
SELECT LogicalName,
reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) from #filelist
where [type]= 'D'
 
OPEN cur
FETCH NEXT from cur INTO @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
SELECT @DATAsizeTotal=@DATAsizeTotal+size FROM #filelist WHERE LogicalName =@temp1
 
-- Set the new path
SET @temp2 = @DataPath+@temp2
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName FROM #filelist
WHERE LogicalName = @temp1
 
-- concatenante the move portion of the restore script.
SET @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
CLOSE cur
DEALLOCATE cur
 
-- Third cursor for Tlog files. Nested.
DECLARE cur cursor for
SELECT LogicalName, reverse(left(reverse(PhysicalName),charindex('\',reverse(PhysicalName),1) - 1)) FROM #filelist
WHERE [type]= 'L'
 
OPEN cur
FETCH NEXT FROM cur INTo @temp1 ,@temp2
WHILE (@@fetch_status = 0)
BEGIN
 
-- start adding size info
select @TLOGsizeTotal=@TLOGsizeTotal+size from #filelist where LogicalName =@temp1
 
-- If Relocate is 0, gets the current physical path and assign it to @temp2
IF @RELOCATE=0
SELECT @temp2 = PhysicalName from #filelist
where LogicalName = @temp1
 
 
set @temp2 = @TlogPath+@temp2
set @move = @move + 'Move N'''+ @temp1 + ''' to N'''+ @temp2 + ''',
'
FETCH NEXT from cur INTo @temp1 ,@temp2
END
close cur
deallocate cur
print @move +'
NOUNLOAD, REPLACE, STATS = 10
GO'
if @OVERRIDE=1 set @move=@move+'ALTER DATABASE ['+@databasename+'] SET MULTI_USER
GO'
 
 
truncate table #filelist
truncate table #header
FETCH NEXT FROM LIST INTO @temp3
END
CLOSE LIST
DEALLOCATE LIST


-- **********************************************
-- **********************************************
-- **********************************************
 
print ' /* ***********************--'
 
Print 'Total Size Needed in all disks '+ cast (@BkSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for DATA files '+ cast (@DATASizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
Print 'Total Size Needed for TLOG files '+ cast (@TLOGSizeTotal/8/1024/1024/1024 as VARCHAR(10))+ ' GB'
print ' --'
print ' *********************** */'
 
 


--Clean up
drop table #CommandShell
drop table #filelist
drop table #header


-- */

EOF:

Restore Full Backup and Tlog Generator

restore, Script Generator

This script is a classic dynamic TSQL.

It uses the MSDB information to build a Restore script with all the backups available on MSDB. 

/*
DESCRIPTION:
Looks inside MSDB history information for a particular Database.
Gets the newest FULL backup and Tlogs
and generates a script to restore the Full BK + Diff+ Tlogs
Assumes the backup files are still in original location.
 
Configuration:
@dbname = the DB that we want to restore
@newdbname = The name of the new DB. If no name change, make this values the same, or leave null.
 
Compatibility list: MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000
 
*/

SET NOCOUNT ON
DECLARE @dbname VARCHAR(500)
DECLARE @newdbname VARCHAR(500)
SET @dbname = 'SALES_ODS'
SET @newdbname = NULL			-- if target DB name will be the same, leave NULL


/*
do not modify below
*/

set @newdbname = coalesce(@newdbname,@dbname)


-- gets the latest FULL backup and all Tlog after that.

DECLARE @bkfiles table
( id int identity(1,1),physical_device_name nVARCHAR (512), [type] char)

INSERT @bkfiles (physical_device_name, [type])
	SELECT m.physical_device_name, s.[type]
	FROM msdb.dbo.backupSET s
	inner join msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
	WHERE
	 s.database_name like @dbname
	 and s.[backup_finish_date] >=
		(SELECT MAX(msdb.dbo.backupSET.backup_finish_date)
		FROM msdb.dbo.backupmediafamily
		INNER JOIN msdb.dbo.backupSET ON msdb.dbo.backupmediafamily.media_SET_id = msdb.dbo.backupSET.media_SET_id
		WHERE msdb..backupSET.type = 'D' and msdb.dbo.backupSET.database_name = @dbname and is_copy_only = '0'
		GROUP BY msdb.dbo.backupSET.database_name
		)
and is_copy_only = '0' -- does not include copy_only backups
 
-- Remove Tlogs and Diff before very last Diff from the table
DELETE FROM @bkfiles
	WHERE ID < (SELECT TOP 1 (ID) FROM @bkfiles
				WHERE TYPE = 'I' ORDER BY ID DESC) 
		and type <> 'D'
-- creates table to hold our Tsql scripts

DECLARE @cmd table
(id int identity(1,1),script NVARCHAR (4000))

INSERT @cmd (script)
	Select 'RESTORE DATABASE ['+@newdbname+'] FROM DISK =N'''+physical_device_name+''''
	from @bkfiles
	where [type]='D'

INSERT @cmd (script)
SELECT 'WITH FILE = 1,'
 
-- Prepare the MOVE Statements

select 'MOVE N'''+name+''''+' to N'''+physical_name+''','  
FROM sys.master_files where database_id=db_id(@dbname) and type_desc = 'ROWS'
 
insert @cmd (script)
select ' NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'

-- Restores the Differential
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'I'
 
 
-- Build the Tlog chain
insert @cmd (script)
select 'RESTORE LOG ['+@newdbname+']
FROM DISK = N'''+ physical_device_name +'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
from @BKFiles
where [TYPE] = 'L'

-- Set DB with recovery mode
insert @cmd (script)
select 'RESTORE DATABASE ['+@newdbname+'] WITH RECOVERY'
-- Display the info
select script from @cmd order by id

Backup – Total Size Full Backups

Backups, Reports, Space-Issues,

This script is useful to find the size all last backups and an estimate on how long they took the last time they run.

It displays two queries. The first one gets the start time of the first database and end time of the last one. Naturally, if all databases were not backup at the same time this value will be off.

The second query is more reliable as it presents information for each database

It queries the MSDB for last backup on each database.

  


/*
DESCRIPTION: Queries the MSDB database for the size of the last full backup of each database
Configuration: No Configuration needed
 
Two queires: individual sizes, and Total sizes PLUS DURATION
 
Compatibility list:
MSSQL2005 - MSSQL2008 - MSSQL2008R2 - MSSQL2012
 
Does not work: MSSQL2000
 Update Log:
*/
 
 DECLARE @t TABLE
(dbname VARCHAR(500), physical_device_name VARCHAR(2000)
, sizeMB NUMERIC(20,0), StartDate DATETIME
, FinishDate DATETIME,age_in_Days INT)
 
INSERT INTO @t
EXEC sp_MSforEachDb '
SELECT
TOP 1 (s.database_name)
,m.physical_device_name
,s.backup_size/1000000
,s.backup_start_date
,s.backup_finish_date
,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) AS [age_in_days]
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
s.database_name = ''?''
and s.type =''D''
ORDER BY s.backup_finish_date desc
'
SELECT
SERVERPROPERTY('servername') 'ServerName',
SUM(sizeMB) 'TotalBackupMB',
MIN(StartDate),
sum(datediff(mi,(StartDate),(FinishDate))) 'DurationMinutes' -- This adds individaul backup times
FROM @t
 
SELECT dbname, physical_device_name, sizeMB,
Age_in_Days, StartDate, FinishDate,datediff(mi,(StartDate),(FinishDate)) 'DurationMinutes'
 
FROM @t