How to know when, where, what and who about a database backup set on a backup file.

Problem: How to get information related to database Files, log files from a backup set.

Solution: RESTORE FILELISTONLY (Transact-SQL)

Sometimes we need to peek into the database backup and find the backup version of a consolidated backup or backup date, compatibility and others. It also gives us the Log Sequence Numbers(LSN) numbers to identify the full backup and differential, gives us the backup type of database, who did and where did the backup happen.

If explored this has a lot of information and consuming this into an application can be helpful to audit the backups.

NOTE:

An optional WITH FILE is issued when you want a specific backup set number, if nothing is specified it gives the first backup set on the file.

Example:

In this article I am going to show you how we can retrieve header information of database, logs from a backup set on a disk.

I have backed up a database few times both differential and full to show you the difference between the database file backup version and backed up dates.

Example 1:

-- To view the details of the database version backuped up and when it is backed up.

RESTORE HEADERONLY FROM DISK = 'C:\Temp\Db.bak'

WITH FILE =1 – this shows information about the first file on the disk

Example 2:

RESTORE HEADERONLY FROM DISK = 'C:\Temp\Db.bak'

WITH FILE =2– this shows information about the second file on the disk

Example 3:

RESTORE HEADERONLY FROM DISK = 'C:\Temp\Db.bak'

WITH FILE =3– this shows information about the third file on the disk

OUTPUT:

The above output shows the different date and times when the backup occurred.

Featured Script to retrieve selected information.

DECLARE @HeaderInformation TABLE (

                BackupName NVARCHAR(128),

                BackupDescription NVARCHAR(255),

                BackupType SMALLINT,

                ExpirationDate DATETIME,

                Compressed TINYINT,

                Position SMALLINT,

                DeviceType TINYINT,

                UserName NVARCHAR(128),

                ServerName NVARCHAR(128),

                DatabaseName NVARCHAR(128),

                DatabaseVersion INT,

                DatabaseCreationDate DATETIME,

                BackupSize NUMERIC(20, 0),

                FirstLSN NUMERIC(25, 0),

                LastLSN NUMERIC(25, 0),

                CHECKPOINTLSN NUMERIC(25, 0),

                DatabaseBackupLSN NUMERIC(25, 0),

                BackupStartDate DATETIME,

                BackupFinishDate DATETIME,

                SortOrder SMALLINT,

                CodePage SMALLINT,

                UnicodeLocaleId INT,

                UnicodeComparisonStyle INT,

                CompatibilityLevel TINYINT,

                SoftwareVendorId INT,

                SoftwareVersionMajor INT,

                SoftwareVersionMinor INT,

                SoftwareVersionBuild INT,

                MachineName NVARCHAR(128),

                Flags INT,

                BindingID UNIQUEIDENTIFIER,

                RecoveryForkID UNIQUEIDENTIFIER,

                Collation NVARCHAR(128),

                FamilyGUID UNIQUEIDENTIFIER,

                HasBulkLoggedData BIT,

                IsSnapshot BIT,

                IsReadOnly BIT,

                IsSingleUser BIT,

                HasBackupChecksums BIT,

                IsDamaged BIT,

                BeginsLogChain BIT,

                HasIncompleteMetaData BIT,

                IsForceOffline BIT,

                IsCopyOnly BIT,

                FirstRecoveryForkID UNIQUEIDENTIFIER,

                ForkPointLSN NUMERIC(25, 0)  NULL,

                RecoveryModel NVARCHAR(60),

                DifferentialBaseLSN NUMERIC(25, 0)  NULL,

                DifferentialBaseGUID UNIQUEIDENTIFIER,

                BackupTypeDescription NVARCHAR(60),

                BackupSetGUID UNIQUEIDENTIFIER  NULL,

                CompressedBackupSize BIGINT,

                containment TINYINT  not NULL,

                KeyAlgorithm NVARCHAR(32),

                EncryptorThumbprint VARBINARY(20),

                EncryptorType NVARCHAR(32)

                )

INSERT INTO @HeaderInformation

EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\Temp\Db.bak'' WITH FILE =3') -- WITH FILE IS OPTIONAL AND to check the particular backup set on the file.

SELECT DatabaseName,

                CASE

                                WHEN Backuptype = 1                                    THEN 'Database'

                                WHEN Backuptype = 2                                    THEN 'Transaction log'

                                WHEN Backuptype = 4                                    THEN 'File'

                                WHEN Backuptype = 5                                    THEN 'Differential database'

                                WHEN Backuptype = 6                                    THEN 'Differential file'

                                WHEN Backuptype = 7                                    THEN 'Partial'

                                WHEN Backuptype = 8                                    THEN 'Differential partial'

                                END AS BackupType,

                DatabaseCreationDate,

                Backupsize/1024 as [Backupsize(MB)],

                backupstartdate,

                backupfinishdate,

                compatibilitylevel,

                position as poisitiononthebackupset,

                firstlsn,

                lastlsn,

                DifferentialBaseLSN

FROM @HeaderInformation

Read More

How to retrieve database backup, log backup file information.

Problem: How to see all database Files, log files from a backup set.

Solution: RESTORE FILELISTONLY (Transact-SQL)

Helps us to retrieve information of all the database, log files inside a backup set. This can be useful to restore when backing up only the files you want to from a comprehensive backup.

When databases are backed up using NOFORMAT option and specify the same path to backup or use the same file to backup for multiple databases, this will be very useful.

NOTE:

An optional WITH FILE is issued when you want a specific backup set number, if nothing is specified it gives the first backup set on the file.

Example:

In this article I am going to show you how we can retrieve database, logs information of backup set on a disk. 

I have backed up a database few times both differential and full to show you the difference between the WITH FILE and when nothing is specified.

Example 1:

-- Results of a backup

RESTORE FILELISTONLY FROM DISK = 'C:\Temp\Db.bak'

Example 2:

--Results using a backup_set_file_number

RESTORE FILELISTONLY FROM DISK = 'C:\Temp\Db.bak'

WITH FILE =3

OUTPUT:

IF you can see the output the differential GUIDs are different for the filelists.

Featured Script:

DECLARE @BackupLocation VARCHAR(512) = 'C:\Temp\Db.bak', -- your backup location here.

                @SqlServerVer DECIMAL(10, 2),

                @TableSQL VARCHAR(4000)

SET @SqlServerVer = (

                                SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), 1, CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) + 1) AS DECIMAL(10, 2))

                                )

--using Global temptable to create and access the table inside the query.

IF OBJECT_ID('tempdb..##FILELISTONLY') IS NOT NULL

                DROP TABLE ##FILELISTONLY

SET @TableSQL = + 'CREATE TABLE ##FILELISTONLY ('

SET @TableSQL = @TableSQL + ' LogicalName VARCHAR(64),'

SET @TableSQL = @TableSQL + ' PhysicalName VARCHAR(130),'

SET @TableSQL = @TableSQL + ' [Type] VARCHAR(1),'

SET @TableSQL = @TableSQL + ' FileGroupName VARCHAR(64),'

SET @TableSQL = @TableSQL + ' Size DECIMAL(20, 0),'

SET @TableSQL = @TableSQL + ' MaxSize DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' FileID bigint,'

SET @TableSQL = @TableSQL + ' CreateLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' DropLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' UniqueID UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' ReadOnlyLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' ReadWriteLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' BackupSizeInBytes DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' SourceBlockSize INT,'

SET @TableSQL = @TableSQL + ' filegroupid INT,'

SET @TableSQL = @TableSQL + ' loggroupguid UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' differentialbaseLSN DECIMAL(25,0),'

SET @TableSQL = @TableSQL + ' differentialbaseGUID UNIQUEIDENTIFIER,'

SET @TableSQL = @TableSQL + ' isreadonly BIT,'

SET @TableSQL = @TableSQL + ' ispresent BIT'

IF @SqlServerVer > 10

                AND @SqlServerVer < 13

BEGIN

                SET @TableSQL = @TableSQL + ', TDEThumbprint VARBINARY(32)' -- This column is introduced for versions later than SQL server 2008 R2

                PRINT @SqlServerVer

END

IF @SqlServerVer > = 13

BEGIN

                SET @TableSQL = @TableSQL + ', SNAPSHOTURL NVARCHAR(36)' -- This column is introduced for versions later than SQL Server 2012

                PRINT @SqlServerVer

END

SET @TableSQL = @TableSQL + '  )'

EXEC (@TableSQL)

INSERT INTO ##FILELISTONLY

--WITH File=1 is specified when you want the First Backup Set.

EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @BackupLocation + '''' + 'WITH FILE=1')

SELECT *

FROM ##FILELISTONLY

In the next article I will be showing, how to know when, where and who has backed up this database.

Read More

How to create Full, LOG backup of database.

Problem: How to create Full, LOG backup of database.

Solution: BACKUP (Transact-SQL)

Backs up a complete SQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG)

Some developers take the full back up before they run any nasty code on the database. So just in case if something screws up the database the can restore it back to previous state, also it’s a good maintenance plan to take full back up regularly (at least once a week).

Example:

In this article I am going to show you how we can create a database backup to a disk.

Also make sure to create the folder where you have to save the backup, below i have created a temp folder in C drive for my backup drops

Example1:

-- Backing up a database to disk with noformat  to add to existing file

BACKUP DATABASE DB to disk = 'C:\Temp\db.bak'

WITH NOFORMAT

--backing up a database to disk with clearing out the existing files.

BACKUP DATABASE DB to disk = 'C:\Temp\db.bak'

WITH FORMAT

--backing up the log files to a disk(set the recover mode to full and also take a full backup before you run the log backup)

ALTER DATABASE DB SET RECOVERY FULL

BACKUP LOG DB to disk = 'C:\Temp\db.bak'

WITH NOFORMAT

OUTPUT:

Processed 1760 pages for database 'DB', file 'DB' on file 1.

Processed 2 pages for database 'DB', file 'DB_log' on file 1.

BACKUP DATABASE successfully processed 1762 pages in 0.085 seconds (161.948 MB/sec).

100 percent processed.

Processed 5 pages for database 'DB', file 'DB_log' on file 1.

BACKUP LOG successfully processed 5 pages in 0.018 seconds (2.170 MB/sec).

Featured Script:

DECLARE @DBName varchar(255)

DECLARE @DateStamp VARCHAR(50)

DECLARE @Path VARCHAR(255)

BEGIN

      SET @DBName = 'DB'

      SET @DateStamp = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')

      SET @Path = 'C:\Temp\'

      SET @Path = @path + @DBNAME+@DateStamp + '.bak'

EXEC('ALTER DATABASE '+ @DBNAME+' SET RECOVERY  FULL')

BACKUP DATABASE @DBNAME

TO DISK = @Path

WITH FORMAT

BACKUP LOG @DBNAME

 TO DISK = @Path

 WITH  NOFORMAT, --uses the existing path to write the backup

NOINIT,  --appends to the backupset

SKIP,--skips the check the database backup set expiration

NOREWIND,-- will keep the tape open when multiple backups are run to a tape(increases performance)

NOUNLOAD, --

STATS = 10 – shows the progress for every 10 percent

END

GO

Read More

How to get Current Time Zone Information of SQL Server

Problem: How to get Current Time Zone Information of SQL Server.

Solution: 

Xp_regread Extended Stored procedures for different versions of SQL Server.

SQL Server does not store the time zone information by itself, it grabs the windows time where it has been used, to get the time zone information of where SQL Server is installed. We have to get into the Registry to see the time zone.

To see where registry resides on your computer, you can run regedit from Command prompt.

KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\TimeZoneInformation.

 Please see the below image to get a preview of the registry timezone information.

 sys.time_zone_info starting SQL Server 2016.

NOTE:

 Xp_regread Extended stored procedures are not recommended to be used by Microsoft, sometimes you may get access denied to this stored procedure as well.

Example:

In this article I am going to show you how we can retrieve the time zone information.

Example1:

Using Xp_regread we can navigate to the folders inside the registry and bring back the output of Time zone name.

DECLARE @TimeZone VARCHAR(50), @daylighttime VARBINARY(50)

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',

'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',

'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

'DynamicDaylightTimeDisabled',@daylighttime OUT

SELECT @daylighttime

OUTPUT:

 

Example 2:

In SQL Server 2016 they have created a system view which can show us all the time zone names, day light savings is active or not, time offset.

--Returns information about supported time zones. All time zones installed on the computer are stored in the following registry hive:

SELECT * FROM SYS.TIME_ZONE_INFO

OUTPUT:

Read More