I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLM\Software\Microsoft\MSSQLServer\Setup
It seems that a SQL2005 installation does not have the same default
directory.
Is there a way to query this from the server itself so I don't have to read the registry?With the usual caveat that it is not a 'best practice' to query the system tables, you can find the answer by executing:
SELECT filename
FROM sysfiles
while connected to the master database. You will get two records. One is the data file, one is the log file. You can narrow the query by adding:
WHERE name = 'master'
Regards,
hmscott
Edit to add: I did verify this against a SQL 2005 instance we are running in development, but it will also work in SQL2K.
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
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 Süßmeyer" <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...
> >
> > 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
> >
> >
>|||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
>
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 Süßmeyer" <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...
> >
> > 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
> >
> >
>|||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
>
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
>
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
>
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 thanks
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...
> 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...[vbcol=seagreen]
> 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...
>
|||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
>
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
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...
> 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...[vbcol=seagreen]
> 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...
>
|||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
>
Subscribe to:
Posts (Atom)