Creating a cleanup script in Visual C#.NET

Creation Code 128C in Visual C#.NET Creating a cleanup script

Creating a cleanup script
Painting Code 128 Code Set A In Visual C#
Using Barcode maker for VS .NET Control to generate, create Code 128 Code Set B image in .NET applications.
www.OnBarcode.com
Recognizing Code 128 In C#.NET
Using Barcode scanner for VS .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
E:\LogShipFolder\scripts\SQL Location of the dynamically generated T-SQL script files, which will contain the RESTORE LOG command. This SQL script file will be run by a command-line call to sqlcmd.exe (or osql.exe for SQL Server 2000 or MSDE), the command-line utility for SQL Server. This folder should be created in the standby server and will also store the query execution results file, which will be generated using the sqlcmd.exe command-line utility. The results file is for records and troubleshooting.
Code-39 Printer In C#
Using Barcode generator for VS .NET Control to generate, create USS Code 39 image in Visual Studio .NET applications.
www.OnBarcode.com
Make 2D In Visual C#
Using Barcode printer for VS .NET Control to generate, create Matrix 2D Barcode image in .NET applications.
www.OnBarcode.com
Creating the T-SQL script
Draw Barcode In Visual C#.NET
Using Barcode encoder for Visual Studio .NET Control to generate, create Barcode image in VS .NET applications.
www.OnBarcode.com
Generating UCC.EAN - 128 In Visual C#.NET
Using Barcode creator for Visual Studio .NET Control to generate, create UCC-128 image in VS .NET applications.
www.OnBarcode.com
First, you need to create a T-SQL script that generates transaction log backups. Let s say, we ll back up the Northwind database in our SQL Server 2005 Express instance, as shown in listing 1. We ll name the file backupLog.sql.
UPCA Maker In Visual C#
Using Barcode printer for Visual Studio .NET Control to generate, create UPC Symbol image in VS .NET applications.
www.OnBarcode.com
Encoding MSI Plessey In C#
Using Barcode drawer for Visual Studio .NET Control to generate, create MSI Plessey image in .NET applications.
www.OnBarcode.com
Listing 1 T-SQL script (backupLog.sql) generates transaction log backups
Code128 Generator In .NET Framework
Using Barcode encoder for .NET Control to generate, create Code-128 image in .NET framework applications.
www.OnBarcode.com
Making Code 128A In C#.NET
Using Barcode encoder for Visual Studio .NET Control to generate, create Code 128B image in Visual Studio .NET applications.
www.OnBarcode.com
DECLARE @strTimeStamp NVARCHAR(12) --variable for timestamp value DECLARE @SQL VARCHAR(1000) --variable for dynamic SQL SET @strTimeStamp=convert(char(8), getdate(), 112) SET @strTimeStamp=@strTimeStamp + REPLACE(convert(char(8), getdate(), 108),':','') SELECT @SQL = 'BACKUP LOG [Northwind] TO DISK = ''E:\LogShipFolder\Northwind\Northwind_tlog_' + @strTimeStamp +'.trn'' WITH INIT' EXEC (@SQL)
UPC Symbol Encoder In Objective-C
Using Barcode generator for iPad Control to generate, create UPC Symbol image in iPad applications.
www.OnBarcode.com
Creating EAN / UCC - 14 In Java
Using Barcode maker for Android Control to generate, create UCC-128 image in Android applications.
www.OnBarcode.com
When you run this backup script, make sure that you have no other log backups running, as this would break the log sequence that is necessary to restore the transaction logs.
Printing Barcode In None
Using Barcode encoder for Software Control to generate, create Barcode image in Software applications.
www.OnBarcode.com
GS1 128 Encoder In .NET
Using Barcode creation for ASP.NET Control to generate, create GS1-128 image in ASP.NET applications.
www.OnBarcode.com
Creating a cleanup script
DataMatrix Decoder In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Barcode Reader In .NET Framework
Using Barcode scanner for VS .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
We ll need to create a cleanup script that will delete the log backups older than a specified number of days or whatever retention period you have set as a corporate policy. The script will also log the delete activities in a file for reference. To accomplish this task, a VBScript will be used, as shown in listing 2.
Barcode Printer In None
Using Barcode creation for Microsoft Word Control to generate, create Barcode image in Office Word applications.
www.OnBarcode.com
Barcode Generation In None
Using Barcode generation for Office Word Control to generate, create Barcode image in Word applications.
www.OnBarcode.com
Listing 2 VBScript (deleteTRN.vbs) cleans up the transaction log backups
PDF 417 Decoder In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Create Code 39 Extended In None
Using Barcode creation for Online Control to generate, create Code 39 Full ASCII image in Online applications.
www.OnBarcode.com
On Error Resume Next Dim fso, folder, files, sFolder, sFolderTarget, intDaysOld Set fso = CreateObject("Scripting.FileSystemObject") 'The number of days would be the value passed to the script as a parameter intDaysOld= Wscript.Arguments.Item(0) strDatabaseName= Wscript.Arguments.Item(1) 'Location of the database backup files sFolder = "E:\LogShipFolder\" & strDatabaseName & "\" Set folder = fso.GetFolder(sFolder) Set files = folder.Files
The poor man s SQL Server log shipping
'Variable used for writing to textfile-generate report on database backups deleted Const ForAppending = 8 'Create a folder named "scripts" for ease of file management & 'a file inside it named "LOG.txt" for delete activity logging Set objFile = fso.OpenTextFile(sFolder & "scripts\LOG.txt", ForAppending) objFile.Write objFile.Write objFile.Write objFile.Write objFile.Write "==================================" & VBCRLF & VBCRLF "DATABASE BACKUP FILE REPORT " & VBCRLF "DATE: " & FormatDateTime(Now(),1) & "" & VBCRLF "TIME: " & FormatDateTime(Now(),3) & "" & VBCRLF & VBCRLF "=======================================" & VBCRLF
'iterate thru each of the files in the database backup folder For Each itemFiles In files 'retrieve complete path of file for the DeleteFile method and to extract 'file extension using the GetExtensionName method a=sFolder & itemFiles.Name 'retrieve file extension b = fso.GetExtensionName(a) 'check if the file extension is TRN If uCase(b)="TRN" Then 'check if the database backups are older than intDaysOld days If DateDiff("d",itemFiles.DateCreated,Now()) >= intDaysOld Then 'Delete any old BACKUP files to cleanup folder fso.DeleteFile a objFile.WriteLine "BACKUP FILE DELETED: " & a End If End If Next objFile.WriteLine "======================================="&VBCRLF & VBCRLF objFile.Close Set Set Set Set objFile = Nothing fso = Nothing folder = Nothing files = Nothing
Creating a batch file
We need to create the batch file that will call both the T-SQL script and the VBScript file. The contents of the batch file will be a simple call to sqlcmd.exe and a call to the VBScript file using either wscript.exe or calling the file, as shown in listing 3. Save the file as E:\LogShipFolder\scripts\databaseBackup.cmd and save it in the scripts subfolder.
Listing 3 Batch file (databaseBackupLog.cmd) calls the backup and cleanup scripts
REM Run TSQL Script to backup database transaction logs sqlcmd -S<PRIMARYINSTANCENAME> -E -i"E:\LogShipFolder\scripts\backupLog.sql" REM Run database backup cleanup script, passing the number of days REM old and the database name E:\LogShipFolder\scripts\deleteTRN.vbs 2 Northwind
Creating a batch file
Figure 1 Selecting a program to schedule
We ll now create a Scheduled Task to run this batch file every 15 minutes or so, depending on the frequency of your transaction log backups. This is the alternative to SQL Server Agent, as it is not available on the Express Editions. Scheduled Tasks can be found in the Control Panel > Scheduled Tasks or under Start > All Programs > Accessories > System Tools > Scheduled Tasks (this is the same as the Task Scheduler if you re running on Windows Vista). Because we are using Windows authentication to run the T-SQL script, we should use a Windows account that is a member of the db_backupoperator role of all the databases, as outlined in the list and figures that follow:
1 2 3
Launch Scheduled Tasks. Click on Add Scheduled Task. Browse to the E:\LogShipFolder\scripts\ folder and select databaseBackupLog.cmd (see figure 1). Select the frequency and time for running the backups (see figures 2 and 3).
Copyright © OnBarcode.com . All rights reserved.