locked
How to restore database in simple recovery mode by default. RRS feed

  • Question

  • Hi Experts,

    my current requirement is that what ever database i want to restore , i want to restore it simple recovery mode though the original database which i taken backup is in full recovery mode.

    generally we can change database recovery model explicitly after restoration got completed .but while doing restoration ,how can i achieve this.

    i have checked the model database settings but those settings will be inherited if we create new database in that instance and it is not applicable for restored backups.

    any suggestions,please suggest.

    Thanks,

    Ramesh Reddy Mallid.

    Monday, June 24, 2019 9:34 AM

Answers

  • i have checked the model database settings but those settings will be inherited if we create new database in that instance and it is not applicable for restored backups.


    Correct, the recovery model of the restored database is determined by the source database. The model database is used only for new databases. Although the RESTORE command does not have an option to change it during the restore, is easily changed to the desired recovery model afterwards (e.g. ALTER DATABASE...SET RECOVERY SIMPLE).

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 24, 2019 10:26 AM
  • Hi Experts,

    my current requirement is that what ever database i want to restore , i want to restore it simple recovery mode though the original database which i taken backup is in full recovery mode.

    You do not have option to change the recovery model while restoring as database is not accessible at all. Your only option is to add a recovery change tsql in restore query.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 24, 2019 10:02 AM

All replies

  • I have doubts  you can change the recovery during the restore process 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 24, 2019 9:51 AM
    Answerer
  • Thanks Uri for your reply.

    can you please provide reference for this.

    Thanks,

    Ramesh Reddy Mallidi.

    Monday, June 24, 2019 9:57 AM
  • Hi Experts,

    my current requirement is that what ever database i want to restore , i want to restore it simple recovery mode though the original database which i taken backup is in full recovery mode.

    You do not have option to change the recovery model while restoring as database is not accessible at all. Your only option is to add a recovery change tsql in restore query.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 24, 2019 10:02 AM
  • i have checked the model database settings but those settings will be inherited if we create new database in that instance and it is not applicable for restored backups.


    Correct, the recovery model of the restored database is determined by the source database. The model database is used only for new databases. Although the RESTORE command does not have an option to change it during the restore, is easily changed to the desired recovery model afterwards (e.g. ALTER DATABASE...SET RECOVERY SIMPLE).

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, June 24, 2019 10:26 AM
  • There is not reference, just think about it  , how can you  interrupt the restore process? It is so easy to change the recovery mode just after restore is completed 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 24, 2019 10:29 AM
    Answerer