You are here -> Home > Developer Center > Shrink and Backup Database

Shrink and Backup Database
By: Jason Nowicki This article has been viewed 618 times.

If you need to shrink a .mdf or a .ldf file and shrink database is not doing it this script will definately take care of it. You'll first need to make a backup device by clicking on backup and then choose new backup. Once you completed that step copy and paste the script below into Query Analyzer and replace the database name with yours and the backup name with the name you assigned to the backup device.

USE Northwind

CHECKPOINT

BACKUP LOG Northwind
   TO "Northwind Backup"
GO

DUMP TRAN Northwind WITH NO_LOG

DBCC SHRINKDATABASE (Northwind, 0)
GO
DBCC SHRINKFILE (Northwind_data, 0)
GO
DBCC SHRINKFILE (Northwind_log , 0)
GO

I had a translog that was 49GB and would not shrink with the Shrink menu option. I ran this and it went to almost nothing. Of course it took about 5 mins, but it worked.

 

How helpful was this article?