July 11, 2013 at 9:55 am
Hi
I have a an SP that returns the size of the backup files into a table. To execute the Sp i have to pass in the paramaters @db and @type.
@db is the database name and @type is the type of backupfile
So for example i would type
exec dbo.Sp_GetBackup 'adventureworks','d'
This would return the adventureworks full backup history.
My question is how can i automate this so i dont have to manually enter the database name and the type of backupfile i want it to return.
July 11, 2013 at 10:06 am
PearlJammer1 (7/11/2013)
HiI have a an SP that returns the size of the backup files into a table. To execute the Sp i have to pass in the paramaters @db and @type.
@db is the database name and @type is the type of backupfile
So for example i would type
exec dbo.Sp_GetBackup 'adventureworks','d'
This would return the adventureworks full backup history.
My question is how can i automate this so i dont have to manually enter the database name and the type of backupfile i want it to return.
Not really sure what you mean here. You could hardcode those values in your function. Pretty hard to offer much advice because we don't have a lot of details yet.
_______________________________________________________________
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/
July 11, 2013 at 11:36 am
To automate,
You can use function DB_Name() instead of typing dbname to pass the first parameter to your proc.,
For backup device type; you have to fetch/derive that value from table Sysdevices, column Cntrltype and store it into variable and then pass to your proc.
July 11, 2013 at 1:41 pm
Ok. I'll try to explain a little better.
I have 20 servers, each one with about 10 - 15 databases on each.
I want to set a weekly scheduled job to run. The job step will be to execute the stored proc i mentioned. So my question is how to execute and automatically pass a different database name into the @db.
Ive been looking into using the sp_foreachdb ? Maybe that is the way forward
July 11, 2013 at 1:57 pm
PearlJammer1 (7/11/2013)
Ok. I'll try to explain a little better.I have 20 servers, each one with about 10 - 15 databases on each.
I want to set a weekly scheduled job to run. The job step will be to execute the stored proc i mentioned. So my question is how to execute and automatically pass a different database name into the @db.
Ive been looking into using the sp_foreachdb ? Maybe that is the way forward
Or you could just look at this query. No need to loop through each database since it there is a table with all the backups for the instance in one spot. 😛
select * from msdb..backupset
http://msdn.microsoft.com/en-us/library/ms186299.aspx
_______________________________________________________________
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply