Post reply

Selecting databases to backup - do not include tempdb

  • 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

  • September 28, 2009 at 3:45 pm

    #1059235

    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

  • September 28, 2009 at 7:29 pm

    #1059278

    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

  • September 30, 2009 at 4:21 am

    #1059868

    you can use where dbid <> 2

  • October 1, 2009 at 4:42 am

    #1060365

    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

  • October 1, 2009 at 8:02 am

    #1060455

    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.

  • October 1, 2009 at 2:13 pm

    #1060680

    @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 🙂

  • October 1, 2009 at 2:16 pm

    #1060684

    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:

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

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