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.
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
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. :-/
asked Feb 08, 2010 at 08:44 AM in Default
BOL does state that
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.
answered Feb 08, 2010 at 09:14 AM
Kev Riley ♦♦
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.
answered Feb 08, 2010 at 08:58 AM