RE: What can I do to an offline database?

SSC Guru

Points: 323507

July 8, 2013 at 8:52 am

Jason both your suspicions are correct:

1 you cannot backup a database that is set offline;

you simply get this error:

Msg 942, Level 14, State 4, Line 2
Database 'MyDatabase' cannot be opened because it is offline.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

2. yes,you can restore over-top a DB that is in an offline,WITH REPLACE.

The advantage when it's offline, is there's no way for a fast-connecting application or user to reconnect to the database when you are trying to do a restore process.

setting a database to single_user or restricted_user still leaves possible avenues for connecting to the database, which can interfere with a restoration process, especially if it was scripted and assumed to be able to complete without any errors.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!