April 28, 2009 at 7:30 am
I wondered if I could use BACKUP DATABASE in a set-statement, something like:
BACKUP DATABASE...
where DBID > 4
The conditions are not the problem, I'm looking for an option to use BACKUP DATABASE in this way, not use a WHILE statement, an UDF or cursor.
Greetz,
Hans Brouwer
April 28, 2009 at 7:41 am
I don't see how this would be possible. The backup operation is not like a sql statement. Think of it more like a sproc. It performs one function based on the parameters you pass it. That being said you could write a sproc that could receive a min dbid and execute the backup command in a loop. It sounds like maybe you are trying to do some sort of automated backup procedure for all databases on the server?
_______________________________________________________________
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/
April 29, 2009 at 12:50 am
Tnx for answering.
Nah, this is an intellectual excercise to see how far 1 can go to eliminate loops/cursors. I think I can do something with a UDF, but that would just be a hidden loop, so that's not a proper solution to my mind.
Tnx again.
Greetz,
Hans Brouwer
April 29, 2009 at 1:14 am
You could use a command like the following to create a backup script then execute the resultant script:
select 'backup database ' + name + ' to disk = ''D:\BackupDir\' + name + '.bak'';'
from sys.databases
where database_id > 4
(NB. this has not been tested, but it should give you a basis to work from).
April 29, 2009 at 2:55 am
Adding to above,
Create your own stored procedure and embed whatever logic you want inside that and call it...
Ex usp_BackupDBs '4' --> To backup DB ID 4 or DBID>4
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
April 29, 2009 at 11:58 am
Hi, tnx for answering all.
Glenn,
I tried something like you suggest, could not get it working. I'll have another try next week, gotta do some work for the next few days:-P
Tnx again, all.
Greetz,
Hans Brouwer
April 29, 2009 at 12:59 pm
we just use sqlagent to take scheduled backups.
Because dbs are implemented according to a prepared schedule, we just refresh the needed jobs and allerts (% log full for incremental log backups).
This all fits in planned space consumption and capacity management.
For sure it is no problem to dynamically generate the backup statements, but sqlagent also provides us follow up means.
The "undocumented" sp_msforeachdb may easily generate the correct command.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 30, 2009 at 11:55 am
Hi ALZDBA,
Have you ever benchmarked this MS_ForEachDB? I have, it's not good. And basically it's just another loop.
But as I mentioned before: it is not about a 'realistic' situation, just an intelectual excersize.
Greetz,
Hans Brouwer
April 30, 2009 at 1:50 pm
Loops are not always "evil". The time spent backing up the DB is orderS of magnitude higher than the little looping time to get next DB name.
Now, for querying data we have a different beast though 😉
* Noel
May 4, 2009 at 2:41 am
FreeHansje (4/30/2009)
Hi ALZDBA,Have you ever benchmarked this MS_ForEachDB? I have, it's not good. And basically it's just another loop.
But as I mentioned before: it is not about a 'realistic' situation, just an intelectual excersize.
No I didn't perform benchmark tests for it.
I rarely use it. (Certainly not in a scheduled/recurring way)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply