Powershell : Script to delete files older than N days

I recently setup a job to backup transaction logs for live databases every 15 mins. To prevent these files filling up the drive, I needed to to write a script to delete old log files so that it can be automated.

Here is the Powershell script I have written to delete files older than 5 days. The no of days and the path are parameters, so the values can be changed as required.

Powershell

This then needed to be added as a scheduled task in windows scheduler. When a Powershell script needs to be scheduled, in the action window write Powershell.exe in program and specify the script file name with path in the argument text box as below.

Scheduler

Here is the text from Powershell script for anyone to copy and paste.

## This deletes files older than 7 days
## You can alter the parameter values to change the path and no of days
$Path = "I:\TransactionLogBackup\UK_NAV"
$days = -7
Get-ChildItem -path $Path | where {$_.Lastwritetime -lt (date).adddays($days)} | remove-item

Advertisements

SQl Server DBA : Transaction Log Backup

Here is the stored procedure to backup the transaction log files:
backupThis procedure takes the database name and backupPath as input parameters. So this procedure can be used for any databases on the server. Place this procedure in a job and schedule it to run every ‘N’ minutes.

Make sure you copy the transaction log backup files to a different server so that can be used for disaster recovery. Also write a batch file to delete the old files otherwise it will fill your drive.

Here is the code if you want to copy and paste:

if object_id('dbo.BackupDatabaseTransactionLogs','P') is not null
drop procedure dbo.BackupDatabaseTransactionLogs
go
create procedure dbo.BackupDatabaseTransactionLogs
(
@databaseName nvarchar(100),
@backupPath nvarchar(500)
)
as
begin
set nocount on;
declare    @date nvarchar(20),
@backupFileName nvarchar(100),
@script    nvarchar(500),
@logFileName nvarchar(100)
set @date = convert(varchar(10),getdate(),112) +'_' + replace(convert(varchar(10),getdate(),108),':','');
set @backupFileName = @databaseName + @date +'.trn';
set @script = 'BACKUP LOG ['+@databaseName+'] TO  DISK = N'''+ @backupPath + '' + @backupFileName + ''' WITH COMPRESSION';
exec(@script);
end
go

SQL Server DBA : Reduce the size of a large database log file

I recently came across a situation where one of the drives in production server is almost full.

There are two large databases and they were backed up daily once. No other backup plans were in place. That drive is filled by the log files of these two databases (~200GB each).

This has happened because the databases were in full recovery mode but transaction logs are not backed up.

Here are the steps to solve this issue:

  1. Take a full backup of the databases (Not required but as a precaution)
  2. Set the recovery model to simple
  3. Shrink the log file
  4. Set the recovery model back to full
  5. Take a full backup
  6. Setup a job to backup transaction log frequently

Note: Shrinking the log file is not a good option to do it regularly. I had to use the shrinking option because this is one-off and the log file has grown that big is due to lack of log backups.

Here is the script to perform the above tasks:

-- Full Backup
backup database TestDB to  disk = N'H:\Backup\TestDB_20150922_1308.bak'
with name = N'TestDB-Full Database Backup', compression,  stats = 10
go
alter database TestDB
set recovery simple;
go
-- See the name of the log file
sp_helpfile
go
-- shrink the truncated log file to 100 mb.
dbcc shrinkfile (TestDB_log, 100);
go
-- reset the database recovery model.
alter database TestDB
set recovery full;
go

SQl Server : Backups

There are three types of backups:

  1. Full backup
  2. Differential backup
  3. Transaction log backup

Full backup

A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.

Differential Backup

A “Differential” backup is a backup of any extent that has changed since the last “Full” backup was created.

Each time a new differential backup created it will contain every extent changed since the last full backup (NOT since the last differential backup).

When the database needs to be restored to the most recent time the user only need to restore the full backup and the most recent differential backup. All of the other differential backups can be ignored.

Transaction log backup

The transaction log is a serial record of all the transactions that have been performed against the database since the last transaction log back up.

With transaction log backups, you can recover the database to a specific point in time.
(see http://www.mssqltips.com/sqlservertutorial/119/sql-server-point-in-time-restore/)

Database should be set to “Full” or “Bulk-logged” recovery model for “Transaction Log” backups.

If the database is set to the “Bulk-logged” recovery model and a bulk operation was issued, then the entire transaction log needs to be restored.

A transaction log backup allows the user to back up the active part of the transaction log.  So after a “Full” or “Differential” backup the transaction log backup will have any transactions that were created after those other backups completed.  After the transaction log backup is issued, the space within the transaction log can be reused for other processes.  If a transaction log backup is not taken, the transaction log will continue to grow.

Reference:
http://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-backups/
http://www.todo-backup.com/backup-resource/sql-backup-software/sql-backup-types.htm
http://msdn.microsoft.com/en-us/library/ms187048.aspx