States of database files

Online: Files will be online if the DB is in online/offline/emergency/read-only state.

Restoring: If the db/file being in the middle of restore process (DB Restore with norecovery mode),                     then the DB files shows RESTORING state.

Suspect: File will go on suspect state if it is failed during the recovery process.

• If the file in primary file group is failed during the recovery process, then the entire database will go on suspect state.
• If the file is not part of primary file group, then only that file will go on suspect state.
• If the db/file is in suspect state, then do DB/File restore or CHECKDB with allow data loss option.

Recovery pending: File went into this state, if the recovery process postponed due to piecemeal restore process.

         A database can also shows recovery pending state, if any of files (t-log) are missed out while

         recovery process or lack of system resources (disk space, cpu, memory...).. these system

         resources may not allow the DB to come to online.

         Solutions:

         i. Try to take db offline and bring it back to online, in case your database went into recovery

             pending due to lack of resources.

         ii. Set the database to emergency mode, then Detach the DB, attach the DB without any log                     files (use the script and remove the log file details in the script), it will add a new log file and               DB will come back to online. Use this method in case of missing/corrupted t-log file.

             We can't detach the database if it is in recovery pending mode. Put that db into emergency

             mode before detach.

             OR else

            Set the database to offline, move the data and t-log files to new paths, and alter the database                in order to add those new paths to database (don't add the t-log file(s), just alter only data                    files path), try to set the database to online. In this case, the DB will come to online by

            creating a new log file in the default location. 

            OR else

            Simply put the database to offline and set it back to online.. When we are setting it back to

            online, the missing t-log file will be automatically added to default path.

          iii. In case, the t-log/data file got corrupted and unable recover the database. set the db to

           emergency, run the CHECKDB with allow data loss, set the db into online.