Wednesday, October 30, 2013

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

Friday, October 25, 2013

Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

When you try to insert rows to identity column , you should include set identity_insert is ON 

SET IDENTITY_INSERT Table_Name ON 
GO 
INSERT INTO dbo.Table_Name ( col1, col2, col3) 
SELECT col1, Col2 col3 FROM dbo.Table2 
GO 
SET IDENTITY_INSERT Table_Name OFF 
GO