'TRUNCATE_ONLY' is not a recognized BACKUP option.
Sep
12
Written by:
9/12/2011 8:25 AM
In the past I wrote a short article on how to shrink an MS SQL Database. It seems the method I used is no longer valid in SQL Server 2008. To be able to shrink the database, you need to first set the database recovery mode to "simple" before you can shrink the database and log file. After doing so you can then shrink the files.
Here is a code sample that I found helpful.
USE dbname;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE dbname
SET RECOVERY FULL;
GO
Another way to shrink the files is to backup the file to the NULL location. This really doesnt backup anything at all but sort of fakes the process within SQL server. The NULL location is simply an empty location, nothing will actually be saved. I was told this is actually a better method because you are not compromising the recoverability of the database.
BACKUP LOG [DBNAME] TO DISK='NULL'
I found these websites to be helpful: