Full backup of a shrunk database - learning by monitoring

I grew up programming back in antiquity when all production was done by batch jobs. The concept can be foreign to some current system managers, but it sure beats working nights and weekends.

This job is designed to run in an hour or so every Sunday at 3 AM, well outside our SLA (service level agreement with users). It could be run monthly or as needed, but I find that jobs run less then weekly tend to be forgotten, and errors can easily be missed.

select * from sysfiles

ALTER DATABASE IRBDEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE IRBDEV SET RECOVERY SIMPLE

DBCC CHECKDB ('IRBDEV', repair_rebuild)

dbcc shrinkdatabase (IRBDEV , 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_Log, 10, truncateonly)

WAITFOR DELAY '00:00:05' --

ALTER DATABASE IRBDEV SET RECOVERY FULL

WAITFOR DELAY '00:00:05'

ALTER DATABASE IRBDEV SET multi_USER

PRINT N'IRBDEV file space '

select * from sysfiles

This works well in both SQL Server 2000 and 2005, I think.

I did find out that TEMPDB can not be shrunk in a simple batch job, as it can be on SQL Server 2000 - see How to shrink the tempdb database in SQL Server