January 10, 2014 at 8:23 am
Declare @backupcommand varchar(2000)
select @backupcommand = 'Backup database [' + @Databasename + '] to '
while @splits < @splits1
begin
select @backupcommand = @backupcommand +
'disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @splits + '.bak '''
select @splits = @splits + 1
end
print (@backupcommand)
So this works fine.. but as I set @backupcommand twice, in the results the second part of the @backupcommand starts with 'disk =...
How do i get rid of this? ''' won't work in this scenario
January 10, 2014 at 8:28 am
SQLSteve (1/10/2014)
Declare @backupcommand varchar(2000)select @backupcommand = 'Backup database [' + @Databasename + '] to '
while @splits < @splits1
begin
select @backupcommand = @backupcommand +
'disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @splits + '.bak '''
select @splits = @splits + 1
end
print (@backupcommand)
So this works fine.. but as I set @backupcommand twice, in the results the second part of the @backupcommand starts with 'disk =...
How do i get rid of this? ''' won't work in this scenario
Not enough information to really help. What is @splits? What is the end result supposed to look like?
January 10, 2014 at 8:37 am
As Lynn said there isn't enough information I got rid of the splits variable and guessed at the others and it works fine for me:Declare @backupcommand varchar(2000)
,@subdir varchar(200) = 'c:\'
,@databasename varchar(200) = 'test'
select @backupcommand = 'Backup database [' + @Databasename + '] to '
select @backupcommand = @backupcommand +
'disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak '''
select @backupcommand
January 10, 2014 at 8:38 am
Ignore this post... sorted it . Had a blonde moment
January 10, 2014 at 8:38 am
Thanks keith
January 10, 2014 at 8:40 am
Splits is for the amount of backup files you want to crearte by the way, having multiple files per backup speeds up the backup process.. 5 to 8 seems to give the best results 🙂
January 10, 2014 at 9:03 am
Declare @subdirinput nvarchar(200)
DECLARE @Databasename NVARCHAR(50);
DECLARE @Subdir NVARCHAR(150);
SELECT @Databasename = (SELECT DB_NAME());
select @subdirinput = (select variable from dba.dbo.variables where process = 'backuppath') -- Change this to Backup Path
declare @splits1 varchar(5)
select @splits1 = '5' -- Number of Backup Files you wish to have
declare @splits varchar(5)
select @splits = '1'
select @Subdir = @subdirinput + '\' + @Databasename;
EXECUTE master.dbo.xp_create_subdir @Subdir;
--Delete Backups older than 2 days
declare @dt datetime
select @dt = getdate()-2 -- Number of days to keep the backups on disk before deleting.
EXECUTE master.dbo.xp_delete_file 0, @Subdir , 'BAK', @dt
--Backup Database to subfolder with date\time stamp
Declare @backupcommand varchar(2000)
select @backupcommand = 'Backup database [' + @Databasename + '] to '
while @splits1 > @splits
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @splits + '.bak''' + ','
select @splits = @splits + 1
end
While @splits = @splits1
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @splits + '.bak''' + ''
select @splits = @splits + 1
end
exec (@backupcommand)
-- If anyone is interested here the full script.
I will be altering it slightly to manually input the number of splits through an input variable when creating the SP from a SP which creates all my maintenance jobs. But this works 🙂 Also.. should of called them stripes not splits, my mistake
January 10, 2014 at 9:43 am
Just for s & g's could you post a sample of what the final script generated looks like?
January 10, 2014 at 9:50 am
Stored Procedure which creates a scheduled backup job to run the SP that backs the database up..
To run this
Exec dbo.createbackupProc [Database name], [Number of stripes]
/****** Object: StoredProcedure [dbo].[CreateBackupProc] Script Date: 10/01/2014 16:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure procedure [dbo].[CreateBackupProc]
@DatabaseNameInput nvarchar(200) = null,
@splits nvarchar(5) = null
as
declare @jobname nvarchar(100)
declare @setDatabasename varchar(100)
declare @deletebackupscmd varchar(200)
declare @backupdatabaseCMD varchar(300)
select @setDatabasename = @DatabaseNameInput
SELECT @jobname = @DatabasenameInput + ' - backup Job'
select @backupdatabaseCMD = 'Exec dbo.BackupDatabase ' + @splits
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name= @jobname,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
EXEC msdb.dbo.sp_add_jobserver @job_name= @Jobname , @server_name =N'(local)'
EXEC msdb.dbo.sp_add_jobStep @job_name=@jobname , @step_name=N'Backup database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@BackupDatabaseCMD,
@database_name=@setDatabasename,
@flags=0
EXEC msdb.dbo.sp_update_job @job_name= @jobname,
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name = @Jobname, @name=N'Daily 2am',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130807,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
Stored Procedure which needs adding to each database, which backs the database up
/****** Object: StoredProcedure [dbo].[BackupDatabase] Script Date: 10/01/2014 14:59:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[BackupDatabase] @stripes1 nvarchar(5)
as
--Create sub folder
declare @stripes nvarchar(5)
Declare @subdirinput nvarchar(200)
DECLARE @Databasename NVARCHAR(50);
DECLARE @Subdir NVARCHAR(150);
SELECT @Databasename = (SELECT DB_NAME());
select @subdirinput = (select variable from dba.dbo.variables where process = 'backuppath') -- backup bath
select @Subdir = @subdirinput + '\' + @Databasename;
select @stripes = '1'
EXECUTE master.dbo.xp_create_subdir @Subdir;
--Delete Backups older than 2 days
declare @dt datetime
select @dt = getdate()-2
EXECUTE master.dbo.xp_delete_file 0, @Subdir , 'BAK', @dt
--Backup Database to subfolder with date\time stamp
Declare @backupcommand varchar(2000)
select @backupcommand = 'Backup database [' + @Databasename + '] to '
while @stripes1 > @stripes
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @stripes + '.bak''' + ','
select @stripes = @stripes + 1
end
While @stripes = @stripes1
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @stripes + '.bak''' + ''
select @stripes = @stripes + 1
end
exec (@backupcommand)
January 10, 2014 at 10:03 am
SQLSteve (1/10/2014)
Stored Procedure which creates a scheduled backup job to run the SP that backs the database up..To run this
Exec dbo.createbackupProc [Database name], [Number of stripes]
/****** Object: StoredProcedure [dbo].[CreateBackupProc] Script Date: 10/01/2014 16:50:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure procedure [dbo].[CreateBackupProc]
@DatabaseNameInput nvarchar(200) = null,
@splits nvarchar(5) = null
as
declare @jobname nvarchar(100)
declare @setDatabasename varchar(100)
declare @deletebackupscmd varchar(200)
declare @backupdatabaseCMD varchar(300)
select @setDatabasename = @DatabaseNameInput
SELECT @jobname = @DatabasenameInput + ' - backup Job'
select @backupdatabaseCMD = 'Exec dbo.BackupDatabase ' + @splits
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name= @jobname,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
EXEC msdb.dbo.sp_add_jobserver @job_name= @Jobname , @server_name =N'(local)'
EXEC msdb.dbo.sp_add_jobStep @job_name=@jobname , @step_name=N'Backup database',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@BackupDatabaseCMD,
@database_name=@setDatabasename,
@flags=0
EXEC msdb.dbo.sp_update_job @job_name= @jobname,
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name = @Jobname, @name=N'Daily 2am',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20130807,
@active_end_date=99991231,
@active_start_time=20000,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
Stored Procedure which needs adding to each database, which backs the database up
/****** Object: StoredProcedure [dbo].[BackupDatabase] Script Date: 10/01/2014 14:59:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[BackupDatabase] @stripes1 nvarchar(5)
as
--Create sub folder
declare @stripes nvarchar(5)
Declare @subdirinput nvarchar(200)
DECLARE @Databasename NVARCHAR(50);
DECLARE @Subdir NVARCHAR(150);
SELECT @Databasename = (SELECT DB_NAME());
select @subdirinput = (select variable from dba.dbo.variables where process = 'backuppath') -- backup bath
select @Subdir = @subdirinput + '\' + @Databasename;
select @stripes = '1'
EXECUTE master.dbo.xp_create_subdir @Subdir;
--Delete Backups older than 2 days
declare @dt datetime
select @dt = getdate()-2
EXECUTE master.dbo.xp_delete_file 0, @Subdir , 'BAK', @dt
--Backup Database to subfolder with date\time stamp
Declare @backupcommand varchar(2000)
select @backupcommand = 'Backup database [' + @Databasename + '] to '
while @stripes1 > @stripes
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @stripes + '.bak''' + ','
select @stripes = @stripes + 1
end
While @stripes = @stripes1
begin
select @backupcommand = @backupcommand +
' disk = ''' + @Subdir + '\' + @databasename + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '_SPLIT_' + @stripes + '.bak''' + ''
select @stripes = @stripes + 1
end
exec (@backupcommand)
Why is @stripes defined as nvarchar when you are obviously using numeric values?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply