August 7, 2013 at 2:03 am
Is this possible?
My aim behind it is to create a stored procedure which I save in master. When I deploy a new database on an instance, I will exec this SP passing it the database name as a variable. This SP will then create a new stored proc on the new database which will have the code to - Create folder structure for backups for the database name I passed, delete old backups, create a new full backup.
I have tried to do this but ran into issues with quotations.. so im wondering if it is actually possible. If so.. I will continue to tackle the quotations!
August 7, 2013 at 2:40 am
You can create this stored procedure in the model database
When you create a new database, the new database will automatically inherit this stored procedure from the model database
Check the link below for more information
http://technet.microsoft.com/en-us/library/ms186388.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 2:45 am
Kingston Dhasian (8/7/2013)
You can create this stored procedure in the model databaseWhen you create a new database, the new database will automatically inherit this stored procedure from the model database
Check the link below for more information
Never even thought of that. Its before 10am thats my excuse and I'm sticking to it. Thanks
But it would still be good to know if it is possible to do the above, for my own knowledge.
August 7, 2013 at 3:04 am
SQLSteve (8/7/2013)
Kingston Dhasian (8/7/2013)
You can create this stored procedure in the model databaseWhen you create a new database, the new database will automatically inherit this stored procedure from the model database
Check the link below for more information
Never even thought of that. Its before 10am thats my excuse and I'm sticking to it. Thanks
But it would still be good to know if it is possible to do the above, for my own knowledge.
I am not sure if you will have any quotation issues
But, you will have to use Dynamic SQL and I don't think you will be able to create a procedure in a different database using Dynamic SQL
If you give us a sample script showing what you are trying to do, we can probably help
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 8:10 am
Kingston, I took your advise. So now I am trying to create a PROC which will create a SQL job to 1) create folder structure 2) delete old backups 3) Backup database
Its getting stuck at the first step. When I run the steps outside of a job it all works. Step1 is failing in the job saying @subdir is not declared.
Do i need to declare the variables for each job step? if so, how? As you cant declare them more than once in the script.
ALTER procedure [dbo].[CreateBackupProc] @subdirinput nvarchar(200) = null
as
declare @thisserver nvarchar(50)
declare @databasename nvarchar(100)
declare @jobname nvarchar(100)
declare @subdir nvarchar(100)
SELECT @Databasename = (SELECT DB_NAME())
SELECT @JobName = (SELECT DB_NAME()) + ' - daily backup'
select @thisserver = (select @@SERVERNAME)
select @subdir = @subdirinput + @databasename
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 = @thisserver
EXEC msdb.dbo.sp_add_jobstep @job_name= @jobname, @step_name=N'Create folder structure',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE master.dbo.xp_create_subdir @subdir;',
@database_name= @Databasename,
@flags=0
August 7, 2013 at 1:31 pm
Yes, it's possible; I've done it several times. In fact, you don't even have to pass in the db name -- the proc will automatically run in the current db!
To do this, after you create the proc in master and named sp_<whatever>, use (undocumented) proc sp_ms_marksystemobject like so:
USE master
GO
CREATE PROCEDURE sp_CreateBackupProc
@param1 ... --,
--@param2 ...
AS
SET NOCOUNT ON
...
GO
EXEC sp_ms_marksystemobject 'sp_CreateBackupProc'
If you have further qs or concerns, just let me know.
Edit: Change generic proc name to one that matches your specific requirement.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 7, 2013 at 3:37 pm
SQLSteve (8/7/2013)Its getting stuck at the first step. When I run the steps outside of a job it all works. Step1 is failing in the job saying @subdir is not declared.
Of course. How could the job step, which is run by SQL Server agent, know about a variable which is only visible to your stored proedure?
So rather than
@command=N'EXECUTE master.dbo.xp_create_subdir @subdir;',
You need
@command = @createsubdircmd
and then you have assigned @createsubdircmd like this:
@createsubdircmd = N'EXECUTE master.dbo.xp_create_subdir ' + dbo.quotestring(@subdir)
You find dbo.quotestring here: http://www.sommarskog.se/dynamic_sql.html#quotestring.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 8, 2013 at 2:46 am
Appreciate your help with this.
Erland,
I have never used that before and theres not much to explain on the web how to use it properly..
declare @thisserver nvarchar(50)
declare @databasename nvarchar(100)
declare @jobname nvarchar(100)
declare @subdir nvarchar(100)
declare @createsubdircmd varchar(200)
SELECT @Databasename = (SELECT DB_NAME())
SELECT @JobName = (SELECT DB_NAME()) + ' - daily backup'
select @thisserver = (select @@SERVERNAME)
select @subdir = @subdirinput + @databasename
select @createsubdircmd = N'EXECUTE master.dbo.xp_create_subdir ' + dbo.quotestring(@subdir)
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 = @thisserver
EXEC msdb.dbo.sp_add_jobstep @job_name= @jobname, @step_name=N'Create folder structure',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command= @createsubdircmd,
@database_name= @Databasename,
@flags=0
Do I just set it as above? I get an error saying it cant find dbo.quotestring.
August 8, 2013 at 5:29 am
SQLSteve (8/8/2013)
Do I just set it as above? I get an error saying it cant find dbo.quotestring.
So did you read my post to the end and tried the link?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 8, 2013 at 6:50 am
Erland Sommarskog (8/8/2013)
SQLSteve (8/8/2013)
Do I just set it as above? I get an error saying it cant find dbo.quotestring.So did you read my post to the end and tried the link?
I did but clearly not properly. I don't wake up uptil the PM - Maybe I should refuse to work mornings!
I appologise! Thanks alot for your help. Step 1 now works and I will appy this to the later steps 🙂
Cheers
August 8, 2013 at 9:34 am
Back again! I changed step 2 to use the above and it worked fine.
Step 3 is proving more difficult..
select @backupcommand = ''Backup database '+ dbo.quotestring(@DatabaseName) + ' to disk = ''' + dbo.quotestring(@subdir) + '\' + dbo.quotestring(@DatabaseName) + '_'' + REPLACE(convert(nvarchar(20),GetDate(),120),'':'',''-'') + ''.bak''''''
exec (@backupcommand )
'
THe job step is being created with the below command
declare @backupcommand varchar
select @backupcommand = 'Backup database 'Steve' to disk = ''W:\MSSQL10_50.LIVE802MSSQL\MSSQL\Backups\Steve'\'Steve'_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak'''
exec (@backupcommand )
Its the quotations catching me out again. I have tried various ways and just cant get it to run through properly :@
The error when running the step is
Incorrect syntax near 'Steve'. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string '' exec (@backupcommand ) '. [SQLSTATE 42000] (Error 105). The step failed.
appreciate your help with this
August 8, 2013 at 10:54 am
I prefer a "template" approach. I find it much easier to write, follow and maintain. For example:
DECLARE @backupcommand_template varchar(8000)
DECLARE @backupcommand varchar(8000)
SET @backupcommand_template = '
BACKUP DATABASE [$db$]
TO DISK = ''$subdir$\$db$_$datetime$.bak''
'
SET @backupcommand = REPLACE(REPLACE(REPLACE(@backupcommand_template,
--"variables" in alphabetical order
'$datetime$', REPLACE(CONVERT(nvarchar(20), GETDATE(), 120), ':', '_')),
'$db$', @DatabaseName),
'$subdir$', @subdir)
--PRINT @backupcommand
EXEC(@backupcommand)
Edit: Changed $date$ to $datetime$ to better match the actual data being replaced.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 8, 2013 at 11:22 am
It's creating the backup command with single quotes around the database name, which causes the syntax error you are seeing. If you want to make sure the database name gets quoted properly (to avoid issues with databases that have symbols like '-' in them) then you can just use QUOTENAME(@DatabaseName) instead of the quotestring function, since you're not quoting a string there 🙂
Cheers!
August 8, 2013 at 2:17 pm
SQLSteve (8/8/2013)
Step 3 is proving more difficult..select @backupcommand = ''Backup database '+ dbo.quotestring(@DatabaseName) + ' to disk = ''' + dbo.quotestring(@subdir) + '\' + dbo.quotestring(@DatabaseName) + '_'' + REPLACE(convert(nvarchar(20),GetDate(),120),'':'',''-'') + ''.bak''''''
exec (@backupcommand )
'
You have to know what you are quoting. @DatabaseName is an identifier, so in this case you should use quotename() which by default quotes names with brackets. (It can be persuaded to use single quotes as well, but it will truncate strings to 129 characters.)
Furthermore:
dbo.quotestring(@DatabaseName) + '_'' + REPLACE(convert(nvarchar(20),GetDate(),120),'':'',''-'') + ''.bak''''''
It is the completely file name should quote. Thus:
dbo.quotestring(@DatabaseName + '_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak')
Although, I am not really sure that this is what you want, as it will set the filename from the current date, and if you plan to run the job several days, you will be disappointed.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply