Hello Guys, My backup file got to big and I am having a space issue. The database is set to a simple recovery mode. we have tried to change the size from management studio but the back up file is still big. is shrinking the file the only option I got? And also is there any query to verify that my db has a differential back up or not? Thanks for your help in advance.
What version of SQL Server are you using? Are you using native backup compression if available? Shrinking the database will only move pages around, not reduce the size of the backup. As stated by Paul Randal here:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-3030-backup-myths/ Look item 30-27 Also, you could look at striping the backups (into smaller multiple chunks) if you have multiple target locations (for the files) to support that. Disclaimer: All of the striped backup files have to be intact for a restore to occur. Definitely consider this against your current recovery strategy. This query should help find any differential backups: SELECT database_name , CASE [type] WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFFERENTIAL' WHEN 'L' THEN 'LOG' END AS 'Backup_Type' , backup_start_date, backup_finish_date, recovery_model FROM msdb.dbo.backupset WHERE database_name = '[Database_Name_Goes_Here' AND type = 'I'; --Diff Hope that helps!