January 30, 2015 at 1:57 pm
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
Any ideas?
Thanks!
January 30, 2015 at 2:06 pm
Steve_Wechsler (1/30/2015)
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().Any ideas?
Thanks!
Why does it have to be a function? And how can you not know which server you are querying?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 2:44 pm
I'm writing code that will send an alert if a database on a remote server hasn't been backed up recently. A job will run on the local server that will load the backup into a local database, but only if the backup is recent. I want it to be flexible enough that I can use it with any combination of source and target servers without having to hardcode the server names (and I will probably have future unrelated uses for it as well).
Example (SQL pseudo-code):
select @last_backup_time=myfunction(@databasename, @remoteservername)
if datediff(hour, @last_backup_time, getdate()) > 24
exec send_mail(@recipients, @subject='Database ' + @databasename + ' has not been backed up in the last 24 hours')
January 30, 2015 at 2:53 pm
Steve_Wechsler (1/30/2015)
I'm writing code that will send an alert if a database on a remote server hasn't been backed up recently. A job will run on the local server that will load the backup into a local database, but only if the backup is recent. I want it to be flexible enough that I can use it with any combination of source and target servers without having to hardcode the server names (and I will probably have future unrelated uses for it as well).Example (SQL pseudo-code):
select @last_backup_time=myfunction(@databasename, @remoteservername)
if datediff(hour, @last_backup_time, getdate()) > 24
exec send_mail(@recipients, @subject='Database ' + @databasename + ' has not been backed up in the last 24 hours')
That doesn't explain why it has to be a function. This whole thing would need to be in a loop in a stored procedure in the first place. Why not just run some dynamic sql in your procedure and then you don't need a procedure and a function for this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply