Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

recovery 400 SQL database in a short time

Posted on 2014-04-21
6
Medium Priority
?
212 Views
Last Modified: 2014-05-31
Hi
        we have 1 SQL server 2008 R2 in city A, there is 400 database in this SQL server, but each database is very small, about 5~10 MB, all 400 database full backup is 2.61GB. now in city B, we have new SQL server 2008R2, i would like to move this 400 database to our new SQL server in one night, as my understand, i can backup all database then copy this 2.61G file to city B, but i do not want to recovery this 400 database one by one maually, so how can i writh the T-SQL script to do this?
        or is ther any other plan to recovery this 400 database to new SQL server?
0
Comment
Question by:beardog1113
6 Comments
 
LVL 8

Expert Comment

by:Narender Gakka
ID: 40014060
this script would do your job, place all your backup files in a single directory and then run this script with enough access permissions.

http://community.spiceworks.com/scripts/show/1512-restore-all-sql-databases-in-a-directory-to-instance-using-a-script
0
 

Author Comment

by:beardog1113
ID: 40014103
seems it is complicated, how can i know which folder's .bak file will be recovery? is there any simple script?
0
 
LVL 19

Expert Comment

by:Miguel Angel Perez Muñoz
ID: 40014205
You can virtualize your server and move all virtual machine, this would be fastest way.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:beardog1113
ID: 40014225
yes, but server A in BJ and server B in other city, the network is very slow between this 2 server, i can only export server A to ovf file and delivery the disk to BJ, then import to server B, and when i delivery disk, there have new data in server A database, i need do the sync after server B power on, that is why i want to recovery 400 database. other way, can i detach the db and copy .mdf and .ldf file to server B,then attach them? because the DB file is very small but the database number is 400....., the attach is faster than recovery
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 40094821
Yes, you can detach and reattach all the files, but that's going to involve downtime on your main server since the databases will be offline while you copy them. Are you looking to script the backups too? If so, you can just build the statements you want using something like this:

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Recovery is similar - Narender's link is actually ideal for doing what you're looking for, so I suggest you take another look there. It will require some configuring but it's about as close as you're going to get.
0
 

Author Closing Comment

by:beardog1113
ID: 40103304
thanks
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question