Obtain list of backup-able databases

I am currently running a nightly script which obtains the list of databases
from master..sysdatabases and runs DBCC & BACKUP on each of them (including
master/model/msdb)
I have hardcoded the where clause to not select the tempdb, and one database
which I have set as "offline"..
Is it possible to obtain the list of "backupable" databases a little more
efficiently? The database "status" doesn't exactly do the trick, as tempdb
is the same as the other normal db's.
Basically I want to list master/msdb/model & all other ONLINE databases...
Many thanksSelect * from sysdatabases where dbid <>2 and status =24
TempDB always has the id 2.
HTH, Jens Suessmeyer.
"Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Ben
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
EXEC sp_MSforeachdb--Undocumented sp provided by MS
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
(including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>|||Hi Jens,
The status of my other databases isn't 24.. It differs depending on the
server.
Server A (SQL 2000) master/msdb = 24, model = 1073741840, other db's = 16,
the offline db = 528
Server B (SQL 7.0) master/msdb = 8, model = 0, other db's = 12 or 4, the
offline db = 1073742438
Thanks
Ben
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OiDe1ZwSFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Select * from sysdatabases where dbid <>2 and status =24
> TempDB always has the id 2.
> HTH, Jens Suessmeyer.
> "Ben Rum" <bundyrum75@.yahoo.com> schrieb im Newsbeitrag
> news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
tempdb[vbcol=seagreen]
databases...[vbcol=seagreen]
>|||Check out the DATABASEPROPERTYEX() in BooksOnLine.
SELECT DATABASEPROPERTYEX('Northwind', Status)
Andrew J. Kelly SQL MVP
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:uUlhpPwSFHA.2256@.tk2msftngp13.phx.gbl...
> I am currently running a nightly script which obtains the list of
> databases
> from master..sysdatabases and runs DBCC & BACKUP on each of them
> (including
> master/model/msdb)
> I have hardcoded the where clause to not select the tempdb, and one
> database
> which I have set as "offline"..
> Is it possible to obtain the list of "backupable" databases a little more
> efficiently? The database "status" doesn't exactly do the trick, as tempdb
> is the same as the other normal db's.
> Basically I want to list master/msdb/model & all other ONLINE databases...
> Many thanks
>