-- DECLARE @R AS DATETIME
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=2, @D='20100615'
-- EXECUTE dbo.FirstLastMonthDayDate @R=@R,@FL=1, @D='20100615'
CREATE PROCEDURE FirstLastMonthDayDate
@d AS DATETIME=NULL,
@FL AS INT=1,
@R AS DATETIME OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @d IS NULL
BEGIN
SELECT @d=GETDATE()
END
IF @FL=1
BEGIN
-- Return the first day date
SELECT @R=dateadd(day,-(datepart(day,@d)-1),@d)
END
ELSE
BEGIN
-- Return the last day date
SELECT @R=dateadd(day,-(datepart(day,dateadd(mm,1,@d))),dateadd(mm,1,@d))
END
SELECT @R
END
marți, 15 iunie 2010
Automated SQL Job to Backup All Database(s)
USE [msdb]
GO
IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO
/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
/*
@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
*/
@BackupServerNameInput VARCHAR(50)= N'LOCALSQLSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'D:\ICAS\BACKUP', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)
SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + '_' + @CI
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName
/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/
/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK= @BackupShare with init'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080929,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/
END
/*Create the Full Backup job - end */
/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Diff Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK=@BackupShare with differential'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
@enabled=1,
@freq_type=8,
@freq_interval=95,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Diff Backup job - end*/
/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
BACKUP LOG @Db_Name TO DISK=@BackupShare'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=010000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Log Backup job - end*/
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
/*****************************************************************************************************************************/
OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7 SET @Backup_Var = 'STOP' ELSE SET @Backup_Var = (SELECT LEFT(@JobName, 7)) IF (@Backup_Var = 'Backup_') BEGIN SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName))) IF (SELECT RIGHT(@JobName2, 3)) = 'Log' BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END ELSE BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2) BEGIN SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1 PRINT @JobName + ' is deleted' END END FETCH NEXT FROM JobName_Cursor INTO @JobName END CLOSE JobName_Cursor DEALLOCATE JobName_Cursor /*****************************************************************************************************************************/ END GO /******************************************************************************************************************************************/ /* */ /* Here begins the job creation */ /* */ /******************************************************************************************************************************************/ USE [master] DECLARE @BackupServerName VARCHAR(50) DECLARE @BackupShare VARCHAR(1000) --DECLARE @Backup_Var VARCHAR(100) --DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs --DECLARE @JobName2 VARCHAR(200) DECLARE @JobName VARCHAR(200) DECLARE @CommandString VARCHAR(4000) ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 start - The section below is the one you need to look at each time you run the script ----------------------------------------------------------------------------------------------------------- /* SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename */ SET @BackupServerName = N'LOCALSQLSERVER' -- Type the backup server name SET @BackupShare = N'D:\ICAS\BACKUP' -- Type the IP address of the backup server's backup NIC and the sharename --SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 end ----------------------------------------------------------------------------------------------------------- USE [msdb] /****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Creates backup jobs for all the databases',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + ''_'' + @CI
/* Get server and instance name end */
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName
/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END
/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
go'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CommandString,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=55710,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
go
/* Uncomment the below code if you like to create Backup jobs now*************/
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs
GO
IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO
/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
/*
@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
*/
@BackupServerNameInput VARCHAR(50)= N'LOCALSQLSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'D:\ICAS\BACKUP', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)
SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + '_' + @CI
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName
/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/
/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK= @BackupShare with init'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080929,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/
END
/*Create the Full Backup job - end */
/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Diff Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK=@BackupShare with differential'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
@enabled=1,
@freq_type=8,
@freq_interval=95,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Diff Backup job - end*/
/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION
SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
BACKUP LOG @Db_Name TO DISK=@BackupShare'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=010000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
END
/*Create the Log Backup job - end*/
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
/*****************************************************************************************************************************/
OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7 SET @Backup_Var = 'STOP' ELSE SET @Backup_Var = (SELECT LEFT(@JobName, 7)) IF (@Backup_Var = 'Backup_') BEGIN SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName))) IF (SELECT RIGHT(@JobName2, 3)) = 'Log' BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END ELSE BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2) BEGIN SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1 PRINT @JobName + ' is deleted' END END FETCH NEXT FROM JobName_Cursor INTO @JobName END CLOSE JobName_Cursor DEALLOCATE JobName_Cursor /*****************************************************************************************************************************/ END GO /******************************************************************************************************************************************/ /* */ /* Here begins the job creation */ /* */ /******************************************************************************************************************************************/ USE [master] DECLARE @BackupServerName VARCHAR(50) DECLARE @BackupShare VARCHAR(1000) --DECLARE @Backup_Var VARCHAR(100) --DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs --DECLARE @JobName2 VARCHAR(200) DECLARE @JobName VARCHAR(200) DECLARE @CommandString VARCHAR(4000) ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 start - The section below is the one you need to look at each time you run the script ----------------------------------------------------------------------------------------------------------- /* SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename */ SET @BackupServerName = N'LOCALSQLSERVER' -- Type the backup server name SET @BackupShare = N'D:\ICAS\BACKUP' -- Type the IP address of the backup server's backup NIC and the sharename --SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 end ----------------------------------------------------------------------------------------------------------- USE [msdb] /****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Creates backup jobs for all the databases',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------
/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + ''_'' + @CI
/* Get server and instance name end */
/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName
/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END
/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
go'
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CommandString,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=55710,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
go
/* Uncomment the below code if you like to create Backup jobs now*************/
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs
SQL SERVER - Verifica ce modificari s-au facut dupa o anumita ora
SELECT name,
TYPE,
type_desc,
create_date,
modify_date
FROM sys.objects
WHERE TYPE IN ('U','V','PK','F','D','P')
AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))
ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure
TYPE,
type_desc,
create_date,
modify_date
FROM sys.objects
WHERE TYPE IN ('U','V','PK','F','D','P')
AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))
ORDER BY modify_date
--U - Table
--V - View
--PK - Primary Key
--F - Foreign Key
--D - Default Constraint
--P - Procedure
SQL SERVER - Cautare sir de caractere in toate coloanele
USE test2010
--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'CACAO'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select table_name, table_schema, column_name from information_schema.columns where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
begin
select @sql = sql from @statements where rowID = @rowID
exec sp_executeSQL @sql
set @rowID = @rowID + 1
end
--view results and cleanup
select * from #results
drop table #results
--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'CACAO'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select table_name, table_schema, column_name from information_schema.columns where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
begin
select @sql = sql from @statements where rowID = @rowID
exec sp_executeSQL @sql
set @rowID = @rowID + 1
end
--view results and cleanup
select * from #results
drop table #results
Sql Server - Tabele fara Index
--Tables without PK on them
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK')
--To Find Heap tables.
select a.name, b.object_id, b.object_id from sys.tables a
inner join sys.indexes b
on a.object_id = b.object_id
and b.type_desc='HEAP'
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK')
--To Find Heap tables.
select a.name, b.object_id, b.object_id from sys.tables a
inner join sys.indexes b
on a.object_id = b.object_id
and b.type_desc='HEAP'
Calculeaza MAX(Camp Caracter)
SELECT MAX(CONVERT(int, dbo.STRFILTER(NumarDoc, '0123456789')))+1 FROM Intrari
-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN, cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) , @i = @i + 1 return @cExpressionSearched */ CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256)) returns nvarchar(4000) as begin declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000) select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode while @i <= @len begin select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO
-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @len begin if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @StrFiltred
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
-- CHRTRAN() User-Defined Function
-- Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
-- CHRTRAN (@cExpressionSearched, @cSearchExpression, @cReplacementExpression)
-- Return Values nvarchar
-- Parameters
-- @cSearchedExpression Specifies the expression in which CHRTRAN( ) replaces characters.
-- @cSearchExpression Specifies the expression containing the characters CHRTRAN( ) looks for in @cSearchedExpression.
-- @cReplacementExpression Specifies the expression containing the replacement characters.
-- If a character in cSearchExpression is found in cSearchedExpression, the character in @cSearchedExpression is replaced by a character from @cReplacementExpression
-- that is in the same position in @cReplacementExpression as the respective character in @cSearchExpression.
-- If @cReplacementExpression has fewer characters than @cSearchExpression, the additional characters in @cSearchExpression are deleted from @cSearchedExpression.
-- If @cReplacementExpression has more characters than @cSearchExpression, the additional characters in @cReplacementExpression are ignored.
-- Remarks
-- CHRTRAN() translates the character expression @cSearchedExpression using the translation expressions @cSearchExpression and @cReplacementExpression and returns the resulting character string.
-- CHRTRAN is similar to a function Oracle PL/SQL TRANSLATE
-- Example
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZ') -- Displays XBYDZF
-- select dbo.CHRTRAN('ABCDEF', 'ACE', 'XYZQRST') -- Displays XBYDZF
-- See Also STRFILTER()
-- UDF the name and functionality of which correspond to the same built-in function of Visual FoxPro
/* -- this algorithm does not work as
-- select dbo.CHRTRAN2('eaba','ba','a') -- Displays e Error !!!
-- select dbo.CHRTRAN('eaba','ba','a') -- Displays ea Correctly
while @i <= @len select @cExpressionSearched = replace(cast(@cExpressionSearched as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(substring(@cSearchExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN, cast(substring(@cReplacementExpression, @i, 1) as nvarchar(1)) COLLATE Latin1_General_BIN ) , @i = @i + 1 return @cExpressionSearched */ CREATE function CHRTRAN (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(256), @cReplacementExpression nvarchar(256)) returns nvarchar(4000) as begin declare @len smallint, @i smallint, @j smallint, @cExpressionTranslated nvarchar(4000) select @cExpressionTranslated = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode while @i <= @len begin select @j = dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) if @j > 0
select @cExpressionTranslated = @cExpressionTranslated + substring(@cReplacementExpression, @j , 1)
else
select @cExpressionTranslated = @cExpressionTranslated + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @cExpressionTranslated
end
GO
-- STRFILTER() User-Defined Function
-- Removes all characters from a string except those specified.
-- STRFILTER(@cExpressionSearched, @cSearchExpression)
-- Return Values nvarchar
-- Parameters
-- @cExpressionSearched Specifies the character string to search.
-- @cSearchExpression Specifies the characters to search for and retain in cString.
-- Remarks
-- STRFILTER( ) removes all the characters from @cExpressionSearched that are not in @cSearchExpression, then returns the characters that remain.
-- Example
-- select dbo.STRFILTER('asdfghh5hh1jk6f3b7mn8m3m0m6','0123456789') -- Displays 516378306
-- select dbo.STRFILTER('ABCDABCDABCD', 'AB') -- Displays ABABAB
-- See Also CHRTRAN()
-- UDF the name and functionality of which correspond to the same functions of Foxtools ( Foxtools is a Visual FoxPro API library)
CREATE function STRFILTER (@cExpressionSearched nvarchar(4000), @cSearchExpression nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @len smallint, @i smallint, @StrFiltred nvarchar(4000)
select @StrFiltred = N'', @i = 1, @len = datalength(@cExpressionSearched)/(case SQL_VARIANT_PROPERTY(@cExpressionSearched,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while @i <= @len begin if dbo.CHARINDEX_BIN(substring(@cExpressionSearched, @i, 1), @cSearchExpression, default) > 0
select @StrFiltred = @StrFiltred + substring(@cExpressionSearched, @i, 1)
select @i = @i + 1
end
return @StrFiltred
end
GO
-- Is similar to the built-in function Transact-SQL charindex, but regardless of collation settings,
-- executes case-sensitive search
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
CREATE function CHARINDEX_BIN(@expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location smallint = 1)
returns nvarchar(4000)
as
begin
return charindex( cast(@expression1 as nvarchar(4000)) COLLATE Latin1_General_BIN, cast(@expression2 as nvarchar(4000)) COLLATE Latin1_General_BIN, @start_location )
end
GO
marți, 1 iunie 2010
Backup SQL SERVER
Local loserver, lnCounter, loBackupDevice
loserver=Createobject("SQLDMO.SqlServer")
loserver.Connect("MiServidor","MiUserName","MiPassword")
If loserver.BackupDevices.Count = 0
loBackupDevice = Createobject("SQLDMO.BackupDevice")
With loBackupDevice
.Name = "MiNuevoBackup"
.PhysicalLocation = "C:\MisBackups\BackupFile.bak"
.Type = 2 && SQLDMODevice_DiskDump
Endwith
loserver.BackupDevices.Add(loBackupDevice)
Else
For lnCounter = 1 To loserver.BackupDevices.Count
? loserver.BackupDevices(lnCounter).PhysicalLocation
Next lnCounter
Endif
loserver=Createobject("SQLDMO.SqlServer")
loserver.Connect("MiServidor","MiUserName","MiPassword")
If loserver.BackupDevices.Count = 0
loBackupDevice = Createobject("SQLDMO.BackupDevice")
With loBackupDevice
.Name = "MiNuevoBackup"
.PhysicalLocation = "C:\MisBackups\BackupFile.bak"
.Type = 2 && SQLDMODevice_DiskDump
Endwith
loserver.BackupDevices.Add(loBackupDevice)
Else
For lnCounter = 1 To loserver.BackupDevices.Count
? loserver.BackupDevices(lnCounter).PhysicalLocation
Next lnCounter
Endif
Compactare Baza de date ACCESS din VFP
OJE = CreateObject ("JRO.JetEngine)
oJE.CompactDatabase("Data Source =" + "d:\DB_OLD.mdb", "Data Source =" + "D:\DB_NEW.mdb)
oJE.CompactDatabase("Data Source =" + "d:\DB_OLD.mdb", "Data Source =" + "D:\DB_NEW.mdb)
Windows User is Administrator?
Declare Integer IsUserAnAdmin In shell32
RETURN IIF(IsUserAnAdmin () = 1, "DA","NU")
RETURN IIF(IsUserAnAdmin () = 1, "DA","NU")
Abonați-vă la:
Postări (Atom)