Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

recovery 400 SQL database in a short time

Posted on 2014-04-21
6
Medium Priority
?
209 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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 …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview

916 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