locked
Restore the backups of all the databases at a time RRS feed

  • Question

  • Hi all,

    I have a task where i need to configure database mirroring for 400 databases at a time. I need a single TSQL Script to restore all the databases full backups with no recovery mode at a time.

    QUick answer will be really appreciated. 

    Saturday, January 18, 2014 2:12 PM

Answers

All replies

  • Hi all,

    I have a task where i need to configure database mirroring for 400 databases at a time. I need a single TSQL Script to restore all the databases full backups with no recovery mode at a time.

    QUick answer will be really appreciated. 

    Are all these databases on single SQL instance ? If so there would be heavy load.There is surely script to create DB mirroring through script but please take my advise please go one at a time.Below links will be helpdul

    http://ifcsong.wordpress.com/2011/10/11/setup-database-mirroring-using-windows-authentication-t-sql/

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, January 18, 2014 2:44 PM
  • Hi shanky,

    yes, all the DB's are in a single instance.

    the link was the TSQL to script to create db mirroring but i required the script to restore all the full backups at a time with no recovery.

    ex: i took the backups of 400 and databases into a folder. i want to restore those backups at a time with no recovery.


    Sunday, January 19, 2014 3:38 PM

  • ex: i took the backups of 400 and databases into a folder. i want to restore those backups at a time with no recovery.


    I dont think its good idea to run 400 restore for a database in one go.I will surely cause you a load .I again suggest you to go step by step.Its really hard to find script for 400 I will give script for one you can create  for 400.Also make sure you have sufficient space to restore 400 DB

    restore database db_name from disk='backup location'
    with norecovery
    go
    restore log db_name from disk='location'
    with norecovery

    Use his script and replace your location .Same script can be used for other databases  just by replacing name


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, January 19, 2014 4:09 PM
  • I will take care of load and space. but this script again becomes a manual work.. 


    Hi all,

    Waiting for quick resolution.


    Monday, January 20, 2014 4:26 AM
  • Have a look into the

    pro sql server 2008 database mirroring book

    author

    Robert L. Davis and Ken Simmons

    automating the mirroring is explained with script


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, January 20, 2014 5:46 AM
  • I should be able to help you in writing a script that will generate restore script for all your 400 databases but need to know if your target server has same directory structure what your source server has?

    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Replication Alert Customization: Send Replication Alert along with query to find exact command

    Monday, January 20, 2014 5:57 AM
  • 400 databases are on the single server (principal)?  I hope you read this article

    http://technet.microsoft.com/en-us/library/cc917681.aspx

    Now , in terms of the script I would generate the script 

    SELECT 'RESTORE DATABASE '+ name +' FROM DISK =''C:\backup\'+name+'.bak'' WITH NORECOVERY'  FROM sys.databases

    Copy past the result of the needed database to the mirror and then run it, but there is a big BUT

    Do you have one mirroring server or 400? If one you can just copy paste the output in the new query window and press F5 but before please the article I posted above.


    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

    • Proposed as answer by Ramesh Babu Vavilla Monday, January 20, 2014 6:50 AM
    • Marked as answer by tracycai Monday, January 27, 2014 1:45 AM
    Monday, January 20, 2014 6:13 AM
  • Thanks Uri dimant,

    you are a life saver.

    Yes i have only one mirroring server and these db's are small databases.

    kindly also provide a script so that i can configure mirroring for all the databases on Principal using TSQL script rather than going manually using GUI.

    Monday, February 3, 2014 3:21 PM