Find Backup History from SQL Server Database Server

The below scripts which allow you to find SQL Server Database Last night backup history. If you want see more files in this list please change date.

SELECT 

 [Server_name],

 [Database_name],

 [Type] AS Backup_type,

 case

    when [type]= 'D' THEN 'Full Backup'

when [type]= 'I' THEN 'Incremental Backup'

when [type]= 'L' THEN 'Log Backup'

 end  as Backup_types ,

 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  

AS  [Compressed_Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))/1024  AS  [Compressed_Backup_Size (GB)],

 100- ((compressed_backup_size/backup_size)*100)   AS 'Compressed%',   

 [backup_start_date],

 [backup_finish_date]

FROM msdb.dbo.backupset

WHERE [Type]='D'

  and backup_finish_date> getdate()-1 -- '2025-04-01 00:00:00.000'

ORDER BY backup_set_id desc

Shrining Database Transaction Log file is not working Transaction Log file size from 42 GB to 100MB

It is interesting to fix one of major database issue which causing a problem to reduce Transaction Log file size from 42 GB to 100MB.

Database file (MDF) 760MB

Database Log file (LDF) 42GB

 -- STEP 1

DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

-- not working above statement as we expected

I took database backup followed by Transaction Log backup then again tried not working neither T-SQL nor SSMS.

What a day

 DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

 -- STEP 2

  DBCC OPENTRAN()

Transaction information for database 'Adventuredb'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (73283:414:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- STEP 3

select log_reuse_wait_desc,* from sys.databases  where name = 'Adventuredb'

log_reuse_wait_desc 

REPLICATION

EXEC sp_removedbreplication  'Adventuredb' 

--Commands completed successfully.

select log_reuse_wait_desc from sys.databases where name = 'Adventuredb'

log_reuse_wait_desc

NOTHING

Finally i have managed shrink the log file to 100MB

Error: 17053, Severity: 16, State: 1. Restore failed

When you RESTORE database if you see the below SQL error Operation System 112

Then you must look at the first point to have a enough disk free space on specified drive where database is going to restore.

Message

Error: 17053, Severity: 16, State: 1.

E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

Find SQL Server database backup compression

If you would like to know about the backup file compression on Sql Server 2008R2 and later version you can use the below scripts. 

Note: Systems databases are not compressed for backup (master,model and msdb)

select 
 server_name,
 database_name,
 [type] AS Backup_type,
 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],
 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  AS  [Compressed_Backup_Size (MB)],
 100- ((compressed_backup_size/backup_size)*100)   as "Compressed%",   
 backup_finish_date
from msdb.dbo.backupset
--where database_name ='db1' and [Type]='D'
order by backup_set_id desc

Find Backup history for one week

use msdb

go
SELECT
SERVERPROPERTY('Servername') AS Server_Name,
bs.Database_name,
bs.Backup_start_date,
bs.Backup_finish_date,
bs.Expiration_date,
CASE bs.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential '
WHEN 'L' THEN 'Log'
END AS Backup_type,
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name,
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
bs.backup_finish_date
Reference :http://msdn.microsoft.com/en-us/library/ms186299.aspx

Restore Database to Point in Time (STOPAT)

RESTORE LOG ... WITH RECOVERY, STOPAT option allows you to restore database backup to a point in time.  This option gives you the ability to restore a database backup file prior to an event that occurred.
Each of these transactions has a LSN (log sequence number) along with a timestamp, so when you restoring the transaction log you have the ability to tell SQL Server where to stop reading transactions that need to be restored.

For expample my database got currupted at -- 13:58.
I have full backup at 13:00 and Transaction log backup at  14:06
I want to restore the log at this point 'Nov 07, 2012 01:57:00 PM'

Use AdventureWorks

go

-- Full Backup has taken 13:00

Select * from Emp where EmployeeID =5;

--Title =Tool Designer
--13:46 

UPDATE Emp SET Title ='Tool Designer Engineer' where EmployeeID =5;

GO

Select * from Emp where EmployeeID =6;

--Title =Marketing Manager
-- 13:52

UPDATE Emp SET Title ='Assit Marketing Manager' where EmployeeID =6;

GO

-- Database has been corrupted
-- 13:58

Select * from Emp where EmployeeID =7;

--Title =Production Supervisor - WC60
-- 14:01

UPDATE Emp SET Title ='Production Supervisor' where EmployeeID =7 ;

GO

-- 14:06 Transaction Backup completed

-- Restore Full Database @ 13:00 with NORECOVERY

use master

GO

RESTORE DATABASE AdventureWorks FROM

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_1.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_2.BAK',

DISK = 'E:\MSSQL\Backup\FULL\AdventureWorks_7_Nov2012_13h0m_Full_3.BAK'

WITH NORECOVERY , STATS = 5

GO

-- Point in time restore

-- Restoring now Transaction log backup WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM'

RESTORE LOG AdventureWorks

FROM DISK ='E:\MSSQL\Backup\TRAN\AdventureWorks_7_Nov2012_14h6m_TRAN_1.BAK'

WITH RECOVERY, STOPAT = 'Nov 07, 2012 01:57:00 PM' , STATS = 5
GO

select EmployeeID,NationalIDNumber, ManagerID,Title

from Emp where EmployeeID IN(5,6,7)

Server: Msg 3013 RESTORE DATABASE is terminating abnormally.

Server: Msg 3013
RESTORE DATABASE is terminating abnormally.

Server: Msg 3159
The tail of the log for the database "DBTest" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Solution : If you want to restore the database on to new server where you created the database was never backup before then you need to take backup of your database on new server ( mostly empty db) before restore the database from the backup file.

Stripe SQL Backups on multiple files

-- FULL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 



--DIFFERENTIAL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   
SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH DIFFERENTIAL,
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 

Database Recovery model and backup types

When you try to configure the Database recovery option for backup strategy make sure to select the correct recovery model . If you need further more about this read from microsoft web.

Database	Backup Type
Recovery_Model	Full	Differential	Transaction
FULL	Yes	Yes	Yes
Bulk-logged	Yes	Yes	Yes
Simple	Yes	Yes	No

How to restore database from RedGate SQLBackup Files

If you are using RedGate Sql backup to restore the database then you can use following t-sql code

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [AdventureWorks] 
FROM
   DISK = ''\\myserver\Backup\AdventureWorks_01.sqb'',
   DISK = ''\\myserver\Backup\AdventureWorks_02.sqb''
WITH RECOVERY,
    MOVE ''AdventureWorks''     TO ''H:\MSSQL\Data\AdventureWorks.mdf'',
    MOVE ''AdventureWorks_log'' TO ''I:\MSSQL\Logs\AdventureWorks_log.ldf'',
REPLACE"'

How to get cleanup old sql backup files

If you want to keep last 7 days sql backups files only there here you go

declare @cDate varchar(20)
select  @cdate =   LEFT( CONVERT(nvarchar(30), (GETDATE()-7), 126),10) -- make sure -7 days
--print @cDate
execute master.dbo.xp_delete_file 0,N'\\mssql\backups\',N'bak',@cdate

Thanks for visiting this blog

More than 10000 users visited so for this SQL Server Blog, 

Find the datbase restore history for your server

-- Find the restore history for your database

use msdb
go
select rh.restore_history_id ,rh.restore_date, rh.user_name, rh.destination_database_name,
rh.restore_type,rf.restore_history_id, rf.file_number, rf.destination_phys_drive,rf.destination_phys_name
from restorehistory rh
join restorefile rf
on rh.restore_history_id = rf.restore_history_id
where rh.destination_database_name = 'mytestdb'
order by rh.restore_date desc

-- Please note Restore_Type value D = Full and L= Log

All SQL Server Backups taken Last time

-- All SQL Server Backups taken Last time
SELECT d.name AS DatabaseName,
MAX(b.backup_finish_date) AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D' -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model GROUP BY d.name
ORDER BY 2 DESC
--Show details of the successful Backup operations performed on this server in the past

SELECT d.name AS DatabaseName,
b.backup_finish_date AS Last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type IN( 'D') -- Full Backup
WHERE d.database_id NOT IN (2, 3) -- Ignore tempdb and model ORDER BY 2 DESC

RESTORE cannot operate on database because it is configured for database mirroring.

Msg 3104, Level 16, State 1, Line 1
You come to know mirror database not sync while, If you decided to break the mirror and re-setup mirror.

1. ALTER DATABASE AdventureDB SET PARTNER OFF ( You remove the mirror first)
2. Take the full backup from the principal server copy over to mirror
3. You will see mirror database is disconnected

RESTORE DATABASE AdventureDB WITH RECOVERY

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AdventureDB' because it is configured for database mirroring.
Use ALTER DATABASE to remove mirroring if you intend to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You found this above strange error.

4. You have reboot the SQL Server. Make sure sql server services are running after re-boot.
 -- Mirror Server restarted
5. Now re-run the command on mirror server
RESTORE DATABASE AdventureDB WITH RECOVERY
RESTORE DATABASE successfully processed 0 pages in 20.281 seconds (0.000 MB/sec).
-- Database is now accessible on mirror servers

6. Start restore database backup Full, Logs with norecovery mode
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.BAK' WITH NORECOVERY
 -- Restoring now
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.Log' WITH NORECOVERY, REPLACE, STATS = 5
If you have more than one log backup do the same
7. Ready resetup the mirror again.

error 3624, status 1, severity 20. Database mirroring has been suspended

The remote mirroring partner for database 'Adventuredb', encountered
error 3624, status 1, severity 20. Database mirroring has been suspended.
Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

when you see this error on sql server logs (or) if you notice your mirror database is suspended.

Solution:

First try to resume mirroring, If mirror become suspended again please use the below steps to resovle

1. Break mirror from Principal server
2. Take a full backup of mirrored database from Principal database server
3. Restored the full backup on mirror server with NORECOVERY
4. Take a Tranactional log backup of mirrored database from Principal database server
5. Restored the Log backup on mirror server with NORECOVERY
6. Re-setup mirroring between principal and mirror server.

Hope this will help

Monitor database mirroring servers

--Simple way to monitor database mirror servers

SELECT d.name as dbname, d.database_id,

@@servername as Principal_Server,
m.mirroring_role_desc,
m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device Operating system error 53

When you are in the situation to restore the database if you wrongly typed path name of backup device you will come accross the following error.

USE master
go
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

--Error
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\SQLSVR001\Backup\Adventureworks.BAK'. Operating system error
53(error not found).

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution:
-- Make sure to include the correct path name of backup file
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\MSSQL\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

At the end you will see
Msg: RESTORE DATABASE successfully processed 760350 pages in 144.614 seconds (43.071 MB/sec).

Advanced Troubleshooting with Extended Events

Paul S.Randal has written wonderful article on Technet about Troubleshooting SQL Server using extended events in SQL Server 2008.

• Why troubleshooting is necessary
• Troubleshooting tools in SQL Server
• Overview and architecture of Extended Events
• Using Extended Events

Source : http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

Restore the Database from the Backup file

-- PART 1
-- Find the Backup file details like Machinename, softwareBuild, collation, backupsize, BackupStartDate
USE MASTER
go
RESTORE HEADERONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE FILELISTONLY
FROM DISK = H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE VERIFYONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Restore the database from the backup file
-- eg. Development database refresh from production copy
RESTORE DATABASE AdventuresDb
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY, REPLACE, STATS = 10

------------------------------------------------------------------ PART 2
-- Find the Backup file orignal File path and Logical filename, Physical filename
USE MASTER
go
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
-- Create/Restore the Database from the backup file with move to different locations
-- eg. Development database refresh from production copy

RESTORE DATABASE AdventuresDb
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY,
MOVE 'AdventuresDb_Data' TO 'D:\MSSQL\Data\AdventuresDb.MDF',  MOVE 'AdventuresDb_Log' TO 'E:\MSSQL\Log\AdventuresDb_log.LDF',
STATS = 10

-- sp_helpdb AdventuresDb