Solved

recovery 400 SQL database in a short time

Posted on 2014-04-21
6
178 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now