Solved

recovery 400 SQL database in a short time

Posted on 2014-04-21
6
186 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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