Selecting databases to backup - do not include tempdb

Question

Selecting databases to backup - do not include tempdb

Scozzard

SSC-Addicted

Points: 420
More actions
September 28, 2009 at 3:40 pm

#205202

Hi Guys,
I have created a maintenance plan which runs nightly. One of the tasks it does is backup a bunch of databases on the server (sql server 2005).
I have had to use a T-SQL task to backup the databases as the "Backup database task" does a few annoying things like backup snapshots.
anyways I created a script to backup databases that i want to be backed up:
SELECT dbid, name
FROM master.dbo.sysdatabases
WHERE status&512 = 0 AND
status&1024 = 0
Order By name
This is fine but it includes the tempdb in the selection. Any ideas on how to exlcude the tempdb from the selection? I see an obvious option is to just append "name != 'tempdb' to the WHERE clause but this seems a little tacky, is there a better way?
Thanks
Scott

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 1

Scozzard (9/28/2009)
is there a better way?
Thanks
Scott

Probably, but I can't think of one. Tempdb is not distinguishable from any other of the system databases, at least in the query MS uses to build the Database Tree in SSMS. Meh, I would hard-code it.

I reserve the right to be wrong.

MJM

Scozzard SSC-Addicted Points: 420 More actions · Answer 2

Cool, thanks for the reply.

I have ended up just hardcoding the 'tempdb' for now. If a better way comes up I will update it but I see no real problem for the time being.

Cheers,

Scott

jamesvgeorge SSC-Addicted Points: 434 More actions · Answer 3

jamesvgeorge

SSC-Addicted

Points: 434

September 30, 2009 at 4:21 am

#1059868

you can use where dbid <> 2

philcart SSC-Forever Points: 47797 More actions · Answer 4

Or you could forget using maintenance plans altogether and simple T-SQL commands. You could roll your own, or use one of the many stored procedures in the script library.

--------------------
Colt 45 - the original point and click interface

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 5

jamesvgeorge (9/30/2009)
you can use where dbid <> 2

This is a way of doing it that will work all of the time. Tempdb is always dbid 2.

Scozzard SSC-Addicted Points: 420 More actions · Answer 6

@philcart Writing scripts would definitely be a good idea, if it becomes any more complex or any more issues come up I will probably go down that road.

@jamesvgeorge and @Gé Brander - cheers guys

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 7

Scozzard (10/1/2009)
@philcart Writing scripts would definitely be a good idea, if it becomes any more complex or any more issues come up I will probably go down that road.
@jamesvgeorge and @Gé Brander - cheers guys

Well, there is always this:

http://www.sqlservercentral.com/scripts/Maintenance/66864/

:w00t:

Selecting databases to backup - do not include tempdb

Cookies on SQLServerCentral