How to truncate the transaction log of a MS SQL database

Every wanted to truncate the transaction log of a MS SQL database? We do that all the time when moving a database from one server to the other. Remember that the transaction log can take up a LOT of space in the backup file which you don’t need when you are simply moving a database to a new server (and when you don’t have the option to simply DTS).
Open up Query Analyser and run the following, substituting real database, file and log names.

I’m using these names in the example:
Database name: [myDatabase]
Data file: [myDatabase_Data]
Log file: [myDatabase_Log]

SQL Code

Use Master

go

Backup Log [myDatabase] with truncate_only

go

Use [myDatabase]

go

DBCC shrinkfile ([myDatabase_Log], 2)

go

-- data

Use Master

go

Backup Log [myDatabase] with truncate_only

go

Use [myDatabase]

go

DBCC shrinkfile ([myDatabase_Data], 1)

go

-- Get fileid

use [myDatabase]

go

select * from sysfiles