29th
July
2009
Como mejorar el rendimiento de Visual Studio 2005 - VB.net
Aqui os dejo este link. Acabo de aplicar lo que dice y aun no puedo asegurar que funcione correctamente.
posted in Programacion | 0 Comments
Aqui os dejo este link. Acabo de aplicar lo que dice y aun no puedo asegurar que funcione correctamente.
posted in Programacion | 0 Comments
Bueno, como no podia ser de otra forma, hoy ha sido un dia movidito y hemos tenido un caso con uno de los clientes de correo interno. Concretamente thunderbird (mis alabanzas hacia mozilla) nos ha gastado una broma y ha hecho que al arrancarlo no tubiera una libreta de direcciones, concretamente la que usamos para enviar notificaciones a los clientes.
Pues nada, 5 min de google y resuelto. Existe un fichero llamado dentro del perfil de thunderbird (documents and settings\usuario\Datos de programa\thunderbird\profiles\nombreperfil) que se llama prefs.js. Al parecer, thunderbird (esto no se porque) lo ha renombrado a prefs1.js con lo que al iniciarlo te vuelve a pedir que lo configures. La solución ha sido tan sencilla como volver a renombrar prefs1.js por prefs.js.
posted in General, Linux, Redes, windows | 0 Comments
Bueno, esta forma no es tan bonita como la del 2005 y permite menos cosas, pero es igual de efectiva.
En sql server2000 o en msde sqlcmd no existe, con lo que debemos usar osql. Y nada mas facil que en un fichero .bat establecer una linea como esta:
osql -p -E -S servidor -Q ” BACKUP DATABASE NomBD TO DISK = ‘C:\bd.bak’
Lo mas engorroso es crearnos un fichero por lotes y una tarea por cada una de las copias que queramos hacer.
Ale a disfrutarlo.
posted in Programacion, Sql Server, windows | 0 Comments
En el desarrollo de nuestro software usamos servidores sqlserver 2005 versión express, lo cual implica que no podemos (mediante utilidades gráficas) programar copias de seguridad. La solución pasa por instalar el sql management studio express que tambien nos instala las utilidades de lineas de comandos de sql express.
Buscando por la red he encontrado un procedimiento almacenado que guardaremos en nuestra bd master. Posteriormente crearemos un fichero sql que llamara a ese procedimiento con la configuración deseada y posteriormente crearemos un fichero por lotes que llamaremos desde una tarea programada de Windows.
Procedimientos almacenado (Descargalo como fichero)
use master
GO
-- set required options
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure 'xp_cmdshell',1
reconfigure
go
exec sp_configure 'Ole Automation Procedures',1
reconfigure
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expressmaint]‘) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[expressmaint]
GO
CREATE PROCEDURE [dbo].[expressmaint]
(
@database sysname, — database name | ALL_USER | ALL_SYSTEM
@optype varchar(7), — LOG | DB | DIFF | REINDEX | REORG | CHECKDB
@backupwith varchar(500) = NULL, — additional backup options
@backupfldr varchar(200) = NULL, — folder to write backup to
@reportfldr varchar(200) = NULL, — folder to write text report
@verify bit = 1, — verify backup
@verifywith varchar(500) = NULL, — additional verify options
@dbretainunit varchar(10) = NULL, — minutes | hours | days | weeks | months | copies
@dbretainval int = 1, — specifies how many retainunits to keep backup
@report bit = 1, — flag to indicate whether to generate report
@rptretainunit varchar(10) = NULL, — minutes | hours | days | weeks | months | copies
@rptretainval int = 1, — specifies how many retainunits to keep reports
@checkattrib bit = 0, — check if archive bit is cleared before deleting
@delfirst bit = 0, — delete before backup (handy if space issues)
@debug bit = 0, — print commands to be executed
@compression bit = 0 — backup compression (SQL 2008 Enterprise Edition only)
)
AS
/*
ExpressMaintTSQL
see http://www.sqldbatips.com/showarticle.asp?ID=27 for documentation
Date Author Notes
24/07/2004 Jasper Smith Initial release
28/07/2008 Jasper Smith Fixed datepart issue with MONTHS retention unit
06/12/2008 Jasper Smith Added SQL 2008 support plus some minor fixes
*/
SET NOCOUNT ON
SET ARITHABORT ON
SET DATEFORMAT YMD
/************************
VARIABLE DECLARATION
************************/
DECLARE @fso int
DECLARE @file int
DECLARE @reportfilename varchar(500)
DECLARE @backupfilename varchar(500)
DECLARE @delfilename varchar(500)
DECLARE @cmd varchar(650)
DECLARE @backupfldrorig varchar(200)
DECLARE @databaseorig sysname
DECLARE @table nvarchar(600)
DECLARE @exists varchar(5)
DECLARE @err int
DECLARE @start datetime
DECLARE @finish datetime
DECLARE @runtime datetime
DECLARE @output varchar(200)
DECLARE @errormsg varchar(210)
DECLARE @datepart nchar(2)
DECLARE @execmd nvarchar(1000)
DECLARE @delcmd nvarchar(1000)
DECLARE @exemsg varchar(8000)
DECLARE @filecount int ; SET @filecount = 0
DECLARE @delcount int ; SET @delcount = 0
DECLARE @hr int ; SET @hr = 0
DECLARE @ret int ; SET @ret = 0
DECLARE @cmdret int ; SET @cmdret = 0
DECLARE @delbkflag int ; SET @delbkflag = 0
DECLARE @delrptflag int ; SET @delrptflag = 0
DECLARE @filecrt int ; SET @filecrt = 0
DECLARE @user sysname ; SET @user = SUSER_SNAME()
DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
DECLARE @jobstart char(12) ;
DECLARE @stage int ; SET @stage = 1
DECLARE @compressok bit ; SET @compressok = 0
DECLARE @versionmajor int
DECLARE @engineedition int
SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),’:',”),4)
IF RIGHT(@reportfldr,1)<>’\’ SET @reportfldr = @reportfldr + ‘\’
IF RIGHT(@backupfldr,1)<>’\’ SET @backupfldr = @backupfldr + ‘\’
SET @backupfldrorig = @backupfldr
SET @databaseorig = @database
SELECT @versionmajor = CAST(LEFT(CAST(SERVERPROPERTY(’ProductVersion’) as varchar(128)),CHARINDEX(’.',CAST(SERVERPROPERTY(’ProductVersion’) as varchar(128)))-1) as int),
@engineedition = CAST(SERVERPROPERTY(’EngineEdition’) as int)
IF (@versionmajor>=10 AND @engineedition = 3) SET @compressok = 1
CREATE TABLE #files(filename varchar(255))
CREATE TABLE #exists(exist int,isdir int,parent int)
CREATE TABLE #databases(dbname sysname)
/**********************************
INITIALIZE FSO IF @report = 1
***********************************/
IF @report = 1
BEGIN
EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR(’Error creating File System Object’,16,1)
SET @ret = 1
GOTO CLEANUP
END
END
/************************
CHECK INPUT
************************/
— check SQL2005 or higher
IF @versionmajor<9
BEGIN
RAISERROR(’SQL2005 or higher is required for sp_expressmaint’,16,1)
SET @ret = 1
GOTO CLEANUP
END
— check sysadmin
IF IS_SRVROLEMEMBER(’sysadmin’) = 0
BEGIN
RAISERROR(’The current user %s is not a member of the sysadmin role’,16,1,@user)
SET @ret = 1
GOTO CLEANUP
END
— check database exists and is online
IF @database NOT IN (’ALL_USER’,'ALL_SYSTEM’)
BEGIN
IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
BEGIN
RAISERROR(’Database %s is invalid or database status is not ONLINE’,16,1,@database)
SET @ret = 1
GOTO CLEANUP
END
END
— check @optype is valid
IF UPPER(@optype) NOT IN (’LOG’,'DB’,'DIFF’,'REINDEX’,'REORG’,'CHECKDB’)
BEGIN
RAISERROR(’%s is not a valid option for @optype’,16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
— check recovery mode is correct if trying log backup
IF @database NOT IN (’ALL_USER’,'ALL_SYSTEM’)
BEGIN
IF (@optype = ‘LOG’ and ((select recovery_model from sys.databases where name = @database) = 3))
BEGIN
RAISERROR(’%s is not a valid option for database %s because it is in SIMPLE recovery mode’,16,1,@optype,@database)
SET @ret = 1
GOTO CLEANUP
END
END
— no log backups for system databases
IF @database = ‘ALL_SYSTEM’
BEGIN
IF @optype = ‘LOG’
BEGIN
RAISERROR(’%s is not a valid option for the option ALL_SYSTEM’,16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
END
— check that @backupfldr exists on the server
IF @optype NOT IN (’REINDEX’,'CHECKDB’,'REORG’)
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,’FolderExists’,@exists OUT,@backupfldr
IF @exists <> ‘True’
BEGIN
RAISERROR(’The folder %s does not exist on this server’,16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
INSERT #exists
EXEC master.dbo.xp_fileexist @backupfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR(’The folder %s does not exist on this server’,16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END
— check that @reportfldr exists on the server
IF @reportfldr IS NOT NULL or @report = 1
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,’FolderExists’,@exists OUT,@reportfldr
IF @exists <> ‘True’
BEGIN
RAISERROR(’The folder %s does not exist on this server’,16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
DELETE #exists
INSERT #exists
EXEC master.dbo.xp_fileexist @reportfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR(’The folder %s does not exist on this server’,16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END
— check @dbretainunit is a vaild value
IF @optype NOT IN (’REINDEX’,'CHECKDB’,'REORG’)
BEGIN
IF UPPER(@dbretainunit) NOT IN (’MINUTES’,'HOURS’,'DAYS’,'WEEKS’,'MONTHS’,'COPIES’)
BEGIN
RAISERROR(’%s is not a valid value for @dbretainunit (”minutes | hours | days | weeks | months | copies”)’,16,1,@dbretainunit)
SET @ret = 1
GOTO CLEANUP
END
END
–check @dbretainval is a vaild value
IF @dbretainval<1
BEGIN
RAISERROR(’%i is not a valid value for @dbretainval (must be >0)’,16,1,@dbretainval)
SET @ret = 1
GOTO CLEANUP
END
— check @rptretainunit is a vaild value if present
IF UPPER(@rptretainunit) NOT IN (’MINUTES’,'HOURS’,'DAYS’,'WEEKS’,'MONTHS’,'COPIES’) and @rptretainunit IS NOT NULL
BEGIN
RAISERROR(’%s is not a valid value for @rptretainunit (”minutes | hours | days | weeks | months | copies”)’,16,1,@rptretainunit)
SET @ret = 1
GOTO CLEANUP
END
–check @rptretainval is a vaild value
IF @rptretainval<1
BEGIN
RAISERROR(’%i is not a valid value for @rptretainval (must be >0)’,16,1,@rptretainval)
SET @ret = 1
GOTO CLEANUP
END
/***********************************
list of databases to process
************************************/
IF @database IN (’ALL_USER’,'ALL_SYSTEM’)
BEGIN
IF @database = ‘ALL_USER’
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id > 4
AND (@optype <> ‘LOG’ OR recovery_model <> ‘3′)
ELSE
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id in (1,3,4)
END
ELSE
INSERT #databases(dbname) SELECT @database
/***********************************
INITIALIZE REPORT IF @report = 1
************************************/
— generate report filename
SELECT @reportfilename = @reportfldr + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_report_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_report_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_report_’
WHEN UPPER(@optype) = ‘REINDEX’ THEN ‘_Reindex_report_’
WHEN UPPER(@optype) = ‘REORG’ THEN ‘_Reorg_report_’
WHEN UPPER(@optype) = ‘CHECKDB’ THEN ‘_CheckDB_report_’
END + @jobstart + ‘.txt’
— if no report just set @reportfilename to NULL
IF @report = 0 SET @reportfilename = NULL
IF @debug = 1
BEGIN
PRINT ‘@reportfilename = ‘ + ISNULL(@reportfilename,’NULL’)
END
IF @report = 1
BEGIN
— create report file
EXEC @hr=sp_OAMethod @fso, ‘CreateTextFile’,@file OUT, @reportfilename
IF (@hr <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR(’Error creating log file’,16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
— set global flag to indicate we have created a report file
SET @filecrt = 1
— write header
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
SET @output = ‘Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ‘ + ‘[' + @user + ']‘
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
IF UPPER(@optype) NOT IN (’REINDEX’,'CHECKDB’,'REORG’)
BEGIN
SET @output = ‘Starting backup on ‘ + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) = ‘CHECKDB’
BEGIN
SET @output = ‘Starting CheckDB on ‘ + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) IN (’REINDEX’,'REORG’)
BEGIN
SET @output = ‘Starting Reindex on ‘ + convert(varchar(25),getdate(),100)
END
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
/************************
BACKUP ACTIONS
************************/
IF UPPER(@optype) = ‘CHECKDB’ GOTO CHECK_DB
IF UPPER(@optype) IN (’REINDEX’,'REORG’) GOTO REINDEX
— if @delfirst = 1 we need to delete prior backups that qualify
IF @delfirst = 1 GOTO DELFIRST
— this label is so that we can return here after deleting files if @delfirst = 1
DOBACKUP:
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
— set backup start time
SET @start = GETDATE()
— write to text report
IF @report = 1
BEGIN
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Database ‘ + @database + ‘: ‘ +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘Full Backup ‘
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘Differential Backup ‘
WHEN UPPER(@optype) = ‘LOG’ THEN ‘Log Backup ‘
END + ’starting at ‘ + CONVERT(varchar(25),@start,100)
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
— backup subfolder
SET @execmd = ‘IF NOT EXIST “‘ + @backupfldrorig + REPLACE(@database,””,”) + ‘\” MKDIR “‘ + @backupfldrorig + REPLACE(@database,””,”) + ‘\”‘
EXEC master.dbo.xp_cmdshell @execmd,no_output
SET @backupfldr = @backupfldrorig + REPLACE(@database,””,”) + ‘\’
SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_’
END + @jobstart +
CASE WHEN UPPER(@optype) = ‘LOG’ THEN ‘.TRN’ ELSE ‘.BAK’ END
/************************
FULL BACKUP
************************/
IF UPPER(@optype) = ‘DB’
BEGIN
IF @compression=1 AND @compressok=1
SET @execmd = N’BACKUP DATABASE [' + @database + '] TO DISK = ”’ + @backupfilename + ”” +
‘ WITH COMPRESSION’ + CASE WHEN @backupwith IS NULL THEN ” ELSE (’,’ + @backupwith) END
ELSE
SET @execmd = N’BACKUP DATABASE [' + @database + '] TO DISK = ”’ + @backupfilename + ”” +
CASE WHEN @backupwith IS NULL THEN ” ELSE (’ WITH ‘ + @backupwith) END
IF @debug = 1 PRINT ‘FULL BACKUP : ‘ + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH — backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = ‘Full backup of database ‘ + @database + ‘ failed with error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
SET @output = SPACE(4) + ‘Refer to SQL Error Log and NT Event Log for further details’
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
— backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + ‘Database backed up to ‘ + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
–calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘Full database backup completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
END
/************************
DIFFERENTIAL BACKUP
************************/
IF UPPER(@optype) = ‘DIFF’
BEGIN
IF @compression=1 AND @compressok=1
SET @execmd = N’BACKUP DATABASE [' + @database + '] TO DISK = ”’ + @backupfilename + ”” +
N’ WITH DIFFERENTIAL,COMPRESSION’ + CASE WHEN @backupwith IS NULL THEN N” ELSE (N’,’ + @backupwith) END
ELSE
SET @execmd = N’BACKUP DATABASE [' + @database + '] TO DISK = ”’ + @backupfilename + N”’ WITH DIFFERENTIAL’ +
CASE WHEN @backupwith IS NULL THEN N” ELSE (N’,’ + @backupwith) END
IF @debug = 1 PRINT ‘DIFFERENTIAL BACKUP : ‘ + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH — backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = ‘Differential backup of database ‘ + @database + ‘ failed with error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
SET @output = SPACE(4) + ‘Refer to SQL Error Log and NT Event Log for further details’
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
— backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + ‘Database backed up to ‘ + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
–calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘Differential database backup completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
END
/************************
LOG BACKUP
************************/
IF UPPER(@optype) = ‘LOG’
BEGIN
IF @compression=1 AND @compressok=1
SET @execmd = N’BACKUP LOG [' + @database + '] TO DISK = ”’ + @backupfilename + ”” +
‘ WITH COMPRESSION’ + CASE WHEN @backupwith IS NULL THEN ” ELSE (’,’ + @backupwith) END
ELSE
SET @execmd = N’BACKUP LOG [' + @database + '] TO DISK = ”’ + @backupfilename + ”” +
CASE WHEN @backupwith IS NULL THEN ” ELSE (’ WITH ‘ + @backupwith) END
IF @debug = 1 PRINT ‘LOG BACKUP : ‘ + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH — backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = ‘Log backup of database ‘ + @database + ‘ failed with error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
SET @output = SPACE(4) + ‘Refer to SQL Error Log and NT Event Log for further details’
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
— backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + ‘Log backed up to ‘ + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
–calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘Log backup completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
/************************
VERIFY BACKUP
************************/
IF @verify = 1
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @backupfilename = @backupfldrorig + REPLACE(@database,””,”) + ‘\’ + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_’
END + @jobstart +
CASE WHEN UPPER(@optype) = ‘LOG’ THEN ‘.TRN’ ELSE ‘.BAK’ END
SET @start = GETDATE()
— write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Database ‘ + @database + ‘: Verify Backup File…’
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
SET @execmd = N’RESTORE VERIFYONLY FROM DISK = ”’ + @backupfilename + ”” +
CASE WHEN @verifywith IS NULL THEN ” ELSE (’ WITH ‘ + @verifywith) END
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = ‘Verify of ‘ + @backupfilename + ‘ failed with Native Error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
— verify success
SET @finish = GETDATE()
SET @output = SPACE(4) + ‘Backup file ‘ + @backupfilename + ‘ verified’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
–calculate verify runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘Verify backup completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
END
/************************
DELETE OLD FILES
************************/
— we have already deleted files so skip to the end
IF @delfirst = 1 GOTO CLEANUP
— this label is so that we can delete files prior to backup if @delfirst = 1
DELFIRST:
/************************
DELETE OLD BACKUPS
************************/
SET @datepart = CASE
WHEN UPPER(@dbretainunit) = ‘MINUTES’ THEN N’mi’
WHEN UPPER(@dbretainunit) = ‘HOURS’ THEN N’hh’
WHEN UPPER(@dbretainunit) = ‘DAYS’ THEN N’dd’
WHEN UPPER(@dbretainunit) = ‘WEEKS’ THEN N’ww’
WHEN UPPER(@dbretainunit) = ‘MONTHS’ THEN N’mm’
END
IF @debug = 1 PRINT ‘@datepart for backups = ‘ + @datepart
— write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files…’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
SET @backupfldr = + @backupfldrorig + REPLACE(@database,””,”) + ‘\’
SELECT @backupfilename = @backupfldr + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_’
END + @jobstart +
CASE WHEN UPPER(@optype) = ‘LOG’ THEN ‘.TRN’ ELSE ‘.BAK’ END
— load files in @backupfldr
IF @checkattrib = 1
SET @cmd = ‘dir /B /A-D-A /OD “‘ + @backupfldr + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_’ END + ‘*’ +
CASE WHEN UPPER(@optype) = ‘LOG’ THEN ‘.TRN’ ELSE ‘.BAK’ END + ‘”‘
ELSE
SET @cmd = ‘dir /B /A-D /OD “‘ + @backupfldr + REPLACE(REPLACE(@database,’ ‘,’_'),””,’_') +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_’ END + ‘*’ +
CASE WHEN UPPER(@optype) = ‘LOG’ THEN ‘.TRN’ ELSE ‘.BAK’ END + ‘”‘
IF @debug = 1 PRINT ‘@cmd = ‘ + @cmd
DELETE #files
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,”),’nothing’)
IF @debug = 1 SELECT * FROM #files
— get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX(’%File Not Found%’,filename) = 0
AND PATINDEX(’%The system cannot find%’,filename) = 0
— remove files that don’t meet retention criteria if there are any files that match pattern
IF UPPER(@dbretainunit) <> ‘COPIES’
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N’DELETE #files WHERE DATEADD(’ + @datepart + N’,’ + CAST(@dbretainval as nvarchar(10)) + N’,’ +
‘CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),7,2) +”/”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),5,2) +”/”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),1,4) +” ”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),9,2) +”:”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),11,2)),103)) > ”’ + CAST(@jobdt as nvarchar(25)) + N””
IF @debug = 1 PRINT ‘@delcmd=’ + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE — number of copies not date based (include current backup that’s not in #files)
BEGIN
IF @filecount>0
BEGIN
IF @dbretainval>1
BEGIN
SET @delcmd = N’DELETE #files WHERE filename IN(SELECT TOP ‘ + CAST((@dbretainval-1) as nvarchar(10)) +
N’ filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(”_”,reverse(filename))),12) DESC)’
IF @debug = 1 PRINT ‘@delcmd=’ + @delcmd
EXEC master.dbo.sp_executesql @delcmd
END
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
IF @debug = 1 PRINT ‘@delcount = ‘ + STR(@delcount)
— if there are any matching files
IF @filecount>0
BEGIN
— are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = ‘DEL /Q “‘ + @backupfldr + @delfilename + ‘”‘
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
— log failure to delete but don’t abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + ‘*** Error: Failed to delete file ‘ + @backupfldr + @delfilename + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
ELSE
BEGIN
SET @output = SPACE(4) + ‘Deleted file ‘ + @backupfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
END
FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END
— write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ‘ file(s) deleted.’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
— clear temporary table and variables
DELETE #files
SET @cmd = ”
SET @delcmd = ”
SET @delfilename = ”
SET @datepart = ”
SET @filecount = 0
SET @delcount = 0
SET @cmdret = 0
SET @stage = @stage + 1
/************************
DELETE OLD REPORTS
************************/
DELREPORTS:
IF @rptretainunit IS NOT NULL
BEGIN
SET @datepart = CASE
WHEN UPPER(@rptretainunit) = ‘MINUTES’ THEN N’mi’
WHEN UPPER(@rptretainunit) = ‘HOURS’ THEN N’hh’
WHEN UPPER(@rptretainunit) = ‘DAYS’ THEN N’dd’
WHEN UPPER(@rptretainunit) = ‘WEEKS’ THEN N’ww’
WHEN UPPER(@rptretainunit) = ‘MONTHS’ THEN N’mm’
END
IF @debug = 1 PRINT ‘@datepart for reports = ‘ + @datepart
— write to text report
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files…’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
— load files in @reportfldr
SET @cmd = ‘dir /B /A-D /OD “‘ + @reportfldr + REPLACE(REPLACE(@databaseorig,’ ‘,’_'),””,”) +
CASE WHEN UPPER(@optype) = ‘DB’ THEN ‘_FullDBBackup_report_’
WHEN UPPER(@optype) = ‘DIFF’ THEN ‘_DiffDBBackup_report_’
WHEN UPPER(@optype) = ‘REINDEX’ THEN ‘_Reindex_report_’
WHEN UPPER(@optype) = ‘CHECKDB’ THEN ‘_CheckDB_report_’
WHEN UPPER(@optype) = ‘REORG’ THEN ‘_Reorg_report_’
WHEN UPPER(@optype) = ‘LOG’ THEN ‘_LogBackup_report_’ END + ‘*.txt”‘
IF @debug = 1 PRINT ‘@cmd = ‘ + @cmd
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL
IF @debug = 1 SELECT * FROM #files
— get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX(’%File Not Found%’,filename) = 0
AND PATINDEX(’%The system cannot find%’,filename) = 0
— remove files that don’t meet retention criteria if there are any files that match pattern
IF UPPER(@rptretainunit) <> ‘COPIES’
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N’DELETE #files WHERE DATEADD(’ + @datepart + N’,’ + CAST(@rptretainval as nvarchar(10)) + N’,’ +
‘CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),7,2) +”/”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),5,2) +”/”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),1,4) +” ”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),9,2) +”:”
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(”_”,REVERSE(filename)))+2),12),11,2)),103)) > ”’ + CAST(@jobdt as nvarchar(25)) + N””
IF @debug = 1 PRINT ‘@delcmd=’ + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE — number of copies not date based
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N’DELETE #files WHERE filename IN(SELECT TOP ‘ + CAST(@rptretainval as nvarchar(10)) +
N’ filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(”_”,reverse(filename))),12) DESC)’
IF @debug = 1 PRINT ‘@delcmd=’ + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
IF @debug = 1 PRINT STR(@delcount)
— if there are any matching files
IF @filecount>0
BEGIN
— are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = ‘DEL /Q “‘ + @reportfldr + @delfilename + ‘”‘
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
— log failure to delete but don’t abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + ‘*** Error: Failed to delete file ‘ + @reportfldr + @delfilename + ‘ ***’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
BEGIN
SET @output = SPACE(4) + ‘Deleted file ‘ + @reportfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
END
FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END
— write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ‘ file(s) deleted.’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
— update stage
SET @stage = @stage + 1
END
— if we got here due to @delfirst = 1 go back and do the backups
IF @delfirst = 1
GOTO DOBACKUP
ELSE
GOTO CLEANUP
/************************
CHECKDB
************************/
CHECK_DB:
IF @optype = ‘CHECKDB’
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
— write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Database ‘ + @database + ‘: Check Data and Index Linkage…’
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
— set backup start time
SET @start = GETDATE()
SET @execmd = N’DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS’
IF @debug = 1 PRINT ‘DBCC Command : ‘ + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = ‘CheckDB of ‘ + @database + ‘ failed with Native Error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
SET @finish = GETDATE()
–calculate checkdb runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘CheckDB completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
— delete reports
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
GOTO DELREPORTS
END
/************************
REINDEX/REORG
************************/
REINDEX:
IF @optype in (’REINDEX’,'REORG’)
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
— write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
IF @optype = ‘REINDEX’
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Database ‘ + @database + ‘: Index Rebuild (using original fillfactor)…’
ELSE
SET @output = ‘[' + CAST(@stage as varchar(10)) + '] Database ‘ + @database + ‘: Index Reorganize…’
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
— set start time
SET @start = GETDATE()
— all user tables
CREATE TABLE #tables(tablename sysname)
EXEC(N’INSERT #tables(tablename) SELECT DISTINCT(”['' + s.[name] + ”].['' + t.[name] + ”]”) FROM [' + @database + N'].sys.tables t ‘ +
N’JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ‘ +
N’JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ‘ +
N’WHERE t.is_ms_shipped = 0 AND i.type>0′)
DECLARE tcur CURSOR LOCAL FAST_FORWARD
FOR SELECT tablename FROM #tables ORDER BY tablename
OPEN tcur
FETCH NEXT FROM tcur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
IF @report = 1
BEGIN
IF @optype = ‘REINDEX’
SET @output = SPACE(4) + N’Rebuilding indexes for table ‘ + @table
ELSE
SET @output = SPACE(4) + N’Reorganizing indexes for table ‘ + @table
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
IF @optype = ‘REINDEX’
SET @execmd = N’ALTER INDEX ALL ON [' + @database + N'].’ + @table + N’ REBUILD’
ELSE
SET @execmd = N’ALTER INDEX ALL ON [' + @database + N'].’ + @table + N’ REORGANIZE’
IF @debug = 1 PRINT ‘Reindex Command : ‘ + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = ‘Rebuild of indexes on [' + @database + N'].’ + @table + ‘ failed with Native Error : ‘ + CAST(@err as varchar(10))
SET @output = SPACE(4) + ‘*** ‘ + @errormsg + ‘ ***’
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
END
CLOSE tcur
DEALLOCATE tcur
DROP TABLE #tables
GOTO CLEANUP
END CATCH
FETCH NEXT FROM tcur INTO @table
END
CLOSE tcur
DEALLOCATE tcur
SET @finish = GETDATE()
–calculate runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + ‘Index maintenance completed in ‘
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ‘ hour(s) ‘
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ‘ min(s) ‘
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ‘ second(s)’
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
END
DROP TABLE #tables
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
— delete reports
GOTO DELREPORTS
END
/************************
CLEAN UP
************************/
CLEANUP:
DROP TABLE #files
DROP TABLE #exists
DROP TABLE #databases
— if we encountered errors deleting old backups return failure
IF @delbkflag<>0
BEGIN
SET @errormsg = ‘Expressmaint encountered errors deleting old backup files’ + CHAR(13)
+ CASE WHEN @report = 1 THEN (’Please see ‘ + @reportfilename + CHAR(13) + ‘ for further details’) ELSE ” END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END
— if we encountered errors deleting old reports return failure
IF (@delrptflag<>0 AND @delbkflag = 0)
BEGIN
SET @errormsg = ‘Expressmaint encountered errors deleting old report files’ + CHAR(13)
+ CASE WHEN @report = 1 THEN (’Please see ‘ + @reportfilename + CHAR(13) + ‘ for further details’) ELSE ” END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END
— if we created a file make sure we write trailer and destroy object
IF @filecrt = 1
BEGIN
— write final part of report
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
SET @output = ‘Expressmaint processing finished at ‘ + CONVERT(varchar(25),GETDATE(),100)
+ ‘ (Return Code : ‘ + CAST(@ret as varchar(10)) + ‘)’
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,’WriteLine’,NULL,@output
EXEC sp_OAMethod @file,’WriteLine’,NULL,”
— destroy file object
EXEC @hr=sp_OADestroy @file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
END
IF @report = 1
BEGIN
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
END
RETURN @ret
GO
PRINT ‘Stored Procedure created successfully’
Script con la configuración (descargalo como fichero):
exec expressmaint @database = ‘ALL_USER’, @optype = ‘DB’, @backupfldr = ‘c:\backups’, @reportfldr = ‘c:\reports’, @verify = 1, @dbretainunit = ‘weeks’, @dbretainval = 1, @rptretainunit = ‘weeks’, @rptretainval = 1, @report = 1
Fichero por lotes(Descargalo como fichero):
sqlcmd -S .\SQLExpress -i c:\expressmaint.sql
Ahora solo nos queda crear una nueva tarea programda en Windows.
posted in Programacion, Sql Server, windows | 1 Comment
Interesante artículo de Parallels para saber quien ha enviado un correo en concreto de forma que si se trata de SPAM podamos acotar el problema.
El servidor está saturado de SPAM. Hay muchos mensajes en la cola. El correo se envía muy lentamente.
Article ID: 766
Last Review: Jan,26 2009
Author:
Last updated by: Meritxell Sevilla
APPLIES TO:
Plesk 7.5.x Reloaded
Plesk 9.x for Linux/Unix
Plesk 8.x for Linux/Unix
Resolución
Primero compruebe que todos los dominios tienen la opción ‘Correo para usuario inexistente’ definida a ‘Rechazar’ pero no a reenviar. Puede cambiar este ajuste para todos los dominios usando “Operaciones en Grupo” en la página “Dominios” del CP de Parallels Plesk Panel. La prestación ‘Rechazar correo para usuario inexistente’ está disponible a partir de Parallels Plesk Panel 7.5.3.
Asimismo, compruebe que todas las redes e IPs incluidas en la lista blanca son de su confianza.
Compruebe cuántos mensajes hay en la cola de Qmail con:
# /var/qmail/bin/qmail-qstat
messages in queue: 27645
messages in queue but not yet preprocessed: 82
Si la cola tiene demasiados mensajes, intente descubrir la procedencia del SPAM.
Si el correo está siendo enviado por un usuario autorizado pero no desde el script PHP, puede ejecutar el comando que aparece a continuación para descubrir el usuario que envió la mayoría de mensajes (desde Plesk 8). Tenga en cuenta que es necesario tener activada la opción ‘Autorización SMTP’ en el servidor para poder ver estos registros:
# cat /usr/local/psa/var/log/maillog |grep -I smtp_auth |grep -I user |awk ‘{print $11}’ |sort |uniq -c |sort -n
La ruta a ‘maillog’ puede cambiar en función del SO que esté usando.
El próximo paso es la utilidad `qmail-qread`, que puede usarse para leer las cabeceras de los mensajes:
# /var/qmail/bin/qmail-qread
18 Jul 2005 15:03:07 GMT #2996948 9073
done remote user1@domain1.com
done remote user2@domain2.com
done remote user3@domain3.com
….
Esta muestra los remitentes y destinatarios de los mensajes. Si el mensaje incluye demasiados destinatarios, probablemente se tratará de SPAM. Ahora intente encontrar este mensaje en la cola por su ID #2996948:
# find /var/qmail/queue/mess/ -name 2996948
examine el mensaje y encuentre la primera línea “Recibido” para saber desde dónde se envió la primera vez, por ejemplo, si encuentra:
Received: (qmail 19514 invoked by uid 10003); 13 Sep 2005 17:48:22 +0700
Significa que este mensaje fue enviado a través de algún CGI por el usuario con UID 10003. Usando este UID puede encontrar el dominio correspondiente:
# grep 10003 /etc/passwd
Si la línea ‘Recibido’ contiene un UID de un usuario ‘apache’ (por ejemplo “invoked by uid 48″) - significa que el SPAM fue enviado a través de algún script PHP. En este caso, puede intentar conocer el spammer usando la información de los correos spam (direcciones de/para, asunto o cualquier otro dato). Generalmente es muy difícil descubrir la fuente de SPAM. Si está completamente seguro de que en este momento hay algún script enviando SPAM (la cola crece rápidamente sin motivo aparente), puede usar el siguiente script para saber qué scripts PHP se están ejecutando en este momento:
# lsof +r 1 -p `ps axww | grep httpd | grep -v grep | awk ‘ { if(!str) { str=$1 } else { str=str”,”$1}}END{print str}’` | grep vhosts | grep php
También puede aplicar el artículo 1711, que describe el procedimiento para conocer desde qué dominios se está enviando el correo a través de scripts PHP.
Líneas recibidas como:
Received: (qmail 19622 invoked from network); 13 Sep 2005 17:52:36 +0700
Received: from external_domain.com (192.168.0.1)
significan que el mensaje ha sido aceptado y entregado a través de SMTP y que el remitente es un usuario de correo autorizado.
IMPORTANTE: descubra cómo recrear la cola de Qmail en http://kb.swsoft.com/es/252
posted in General | 0 Comments
Por fin, tras meses de rumores, google ha sacado su sistema operativo.. bueno realmente aun no esta finalizado y lo han basado en linux.. CHROME OS.
Recién hecho público el anuncio en el que Google afirmaba estar
trabajando en un sistema operativo, la comunidad informática ha generado un aluvión de preguntas que desde el blog de Google intentan responder (de momento han sido unas pocas).
A continuación paso a traducir el pequeño FAQ con estas preguntas que han publicado en el blog:
En el enlace de abajo tienes más información sobre el último punto (si te interesa claro).
Vía: Google Blog
Post obtenido desde http://www.dosbit.com
posted in General | 0 Comments
Desde hace aproximadamente un mes hemos tenido unos correos extraños en nuestro servidor, teniamos miles de correos dirigidos a postmaster. Investigando hemos visto que se trata de un problema de double bounce, es decir un spamer envia un correo a una dirección que no existe desde una dirección que no existe.
Bien, esto provoca dos problemas, el primero es que como el destinatario no existe, el servidor intenta devolver una notificación al remitentes, pero claro el remitente tampoco existe con lo que se genera otro aviso por parte del servidor hacia el post master. Os podeis imaginar el problema que esto constituye cuando se acumulan 8000 correos en la cola de qmail.
Pues nada, la solución basicamente pasa por decirle que los correos double bounce los reenvie a una direccion de correo a la cual le vamos a decir excluya todos los correos recibidos. ¿como sehace esto? pues al lio… Por un lado creamos un fichero dentro de /var/qmail/control llamada doublebonceto donde indicaremos la dirección del correo que vamos a usar a este respecto:
echo nombrecuentacorreo > /var/qmail/control/doublebounceto
Obviamente la dirección tiene que estar creada… Posteriormente creamos otro fichero en el que vamos a indicar el host por defecto al que van los correos de este tipo en el fichero doublebouncehost:
echo dominio.dom > /var/qmail/control/doublebouncehost
Debemos crearlos como root y con permisos 644.
Bueno ya tenemos el trabajo casi terminado, ahora solo nos falta editar el fichero .qmail del directorio del buzon de correo (”/var/qmail/mailnames/domino.dom/bounce”). ESte fichero esta oculto, pero podemos editarlo sin problemas. Pues bien , la primera linea que seguramente sera “| true” la sustituimos por “| exit 0″
Y ya esta… a mi me ha funcionado, pero he tenido que indagar entre varias webs y echar mano de un pdf que tambien he colgado en el blog donde se definen los ficheros que usa qmail en el directorio control (aqui).
Espero que os solvente el problema como a mi.
posted in Linux, Redes | 0 Comments
Aqui os dejo un Manual bastante interesante sobre qmail , sus ficheros, sus variables, etc. Guia QMail
Realmente es una guia de 4PSA Qmail Management, pero tienen una sección sobre los ficheros del directorio control donde se controla gran parte del funcionamiento de qmail.
Que lo disfruteis.
posted in Linux, Redes | 0 Comments
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Jun | Aug » | |||||
| 1 | 2 | 3 | 4 | 5 | ||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | 30 | 31 | ||