Solved

recovery 400 SQL database in a short time

Posted on 2014-04-21
6
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 118
Need return values from a stored procedure 8 45
shrink table after huge delete 2 28
Substring works but need to tweak it 14 31
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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