Operation Manual

ManualsBrandsAcronis ManualsSoftwareBackup Advanced Version 11.5 Update 6

291

292

293

294

295

296

297

298

299

300

3. Switch the database back to the Full or Bulk-logged recovery model in the same manner as in

step 1.

Automating log truncation and shrinking

You can automate the above truncation procedure by using a script and (optionally) add log file

shrinking. If you add the script to the Post-backup command (p. 123), the logs will be truncated and

shrunk immediately after a backup. This method assumes that you have Transact-SQL scripting skills

and are familiar the sqlcmd utility.

For more information about Transact-SQL and sqlcmd refer to the following articles:

 Using Transact-SQL: http://technet.microsoft.com/en-us/library/ms189826(v=sql.90)

 Using the sqlcmd utility: http://technet.microsoft.com/en-us/library/ms170572(SQL.90).aspx

To automate transaction log truncation and shrinking for an SQL instance

1. Using the following template, create a script that will truncate and shrink the log files for the

databases of the instance:

USE database_name

ALTER DATABASE database_name SET RECOVERY SIMPLE;

DBCC SHRINKFILE(logfile_name);

ALTER DATABASE database_name SET RECOVERY FULL;

In the last string, the SET RECOVERY value depends on the original recovery model of the

particular database and could be FULL or BULK_LOGGED.

Example for an instance having two databases (TestDB1 and TestDB2):

USE TestDB1;

ALTER DATABASE TestDB1 SET RECOVERY SIMPLE;

DBCC SHRINKFILE(TestDB1_log);

ALTER DATABASE TestDB1 SET RECOVERY FULL;

USE TestDB2;

ALTER DATABASE TestDB2 SET RECOVERY SIMPLE;

DBCC SHRINKFILE(TestDB2_log);

ALTER DATABASE TestDB2 SET RECOVERY BULK_LOGGED;

2. Add the following sqlcmd command to the Post-backup command (p. 123):

sqlcmd -S myServer\instanceName -i C:\myScript.sql

Where:

 myServer - name of the server

 instanceName - name of the instance

 C:\myScript.sql - path to the script file created in the step 1.

To automate transaction log truncation and shrinking for multiple SQL instances

If you have more than one instance on the machine and want to apply the above procedure to these

instances, proceed as follows.

1. Create a separate script file for each instance (e.g. C:\script1.sql and C:\script2.sql).

2. Create a batch file (e.g. C:\truncate.bat) that will contain the commands for the corresponding

instance:

sqlcmd -S myServer\instance1 -i C:\script1.sql

sqlcmd -S myServer\instance2 -i C:\script2.sql

3. In the Post-backup command, specify the path to the batch file.