x

Database status = ONLINE or OFFLINE

I have a process where when I move a database from a server to another I set the original to OFFLINE while the new one is coming online, that way, if the transfer fails I can bring the original back ONLINE and the application suffers less downtime.

I am doing some tidy-up work and have a couple of OFFLINE databases on one of my servers and I looked to get the file locations of those databases as part of the process.

SELECT
    *
FROM
    [sys].[database_files] AS df

is only any good from the database you want to find out about and the databases are OFFLINE so thats no good. So a bit of digging and this was created

SELECT
    [d].[name] ,
    [mf].[name],
    [mf].[state],
    [mf].[state_desc],
    [mf].[physical_name],
    [d].[state_desc]
FROM
    [sys].[master_files] AS mf
INNER JOIN [sys].[databases] AS d
ON  [mf].[database_id] = [d].[database_id]
WHERE
    [d].[state_desc] = 'OFFLINE'
ORDER BY
    d.[name] ,
    [mf].[physical_name]

It does what I want perfectly but I am confused as to the masterfiles state/status desc. The values there say 'ONLINE'. SSMS shows OFFLINE, sys.databases says OFFLINE, I know that the offline ones are the ones I would expect but why is masterfiles out of date? I could have written the script wholly on that table and not found any OFFLINE databases ...

Also, if I type master_files with an underscore in the name more than once it all goes italic in between the master_files ... you can get away with one master_files in a sentence but not two. :-/

more ▼

asked Feb 08, 2010 at 08:44 AM in Default

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

BOL does state that

In SQL Server, the state of a database file is maintained independently from the state of the database

It appears that the database state depends on the file state (as in all the files need to be online for the db to be online), but you can set the database to be offline independently of the files.

more ▼

answered Feb 08, 2010 at 09:14 AM

avatar image

Kev Riley ♦♦
66.3k 48 65 81

(comments are locked)
10|1200 characters needed characters left

I think this is because of partial availability. A database can be online but some of its files can be offline independently.

As for the italics thing, I'm guessing it's just Intellisense freaking out a bit.

more ▼

answered Feb 08, 2010 at 08:58 AM

avatar image

David Wimbush
10.7k 31 34 43

Ah, the italics is on the question, not in SSMS. Just something I fought with to get the question formatted reasonalby. I didnt use the italics format at all in the question!

Feb 08, 2010 at 09:04 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22
x13
x-7

asked: Feb 08, 2010 at 08:44 AM

Seen: 2760 times

Last Updated: Feb 08, 2010 at 08:44 AM

Copyright 2018 Redgate Software. Privacy Policy