February 24, 2012 at 1:34 pm
I need to copy (clone) all databases, as well as their users and permissions, from a sql server 2000sp4 to anotehr server with SQL server 2005sp3.
There are more than 30 databases in the source sql server 2000sp4.
After copy (clone) all the source database to the target sql server, the source databases will still running for months.
If it is only for one database copy, we can use SQl server EM to backup database from source server and restore it on the target server using SQL Server EM.
For big number of databases, such as my case over 30 DBs, I believe it must have a script to handle DB backup process in the source server and a script to handle restoration in the target server.
Is this correct?
If yes, can some experts please help me on how to get these scritps and detail info on how to use the scriots ?
If not, can you please let me know if there is any easy way to handle multiple database clones to another server without using a manual process?
Thanks for your inputs!
February 25, 2012 at 4:03 pm
are the disk\file paths different between the 2 servers?
are you cloning for testing purposes, if so have you thought of cloning the whole server to a virtual machine?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 25, 2012 at 4:56 pm
Cloning to a virtual machine is an interesting idea. You'd have to change the server names, but that's not a big deal.
Personally, for 30 databases, if this is a one time thing, I'd just backup and restore on the other server. It's a little time consuming, but you could spend that much time testing a script to get it right.
February 25, 2012 at 5:01 pm
Steve Jones - SSC Editor (2/25/2012)
You'd have to change the server names, but that's not a big deal.
Not if they're in an environment that is segregated from the public network, generally it's best to though
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2012 at 8:07 am
If this is not a one-time process, I would recommend talking to your SAN team and seeing what options are available there.
Almost all SAN's now have snapshot/snapclone capabilities - as well as some type of mirroring that could be used.
I use snapclones to build a copy of the backup drive, present the snapclone to my report server and restore from the backups every night. Because of the SAN being used and how it is configured - I am able to restore 1.5TB+ of databases and transaction logs in about 1 hour (actually takes less time to restore than to backup the databases).
The scripts I use are proprietary - by I can put together an example if needed. What it takes is querying the source system's backup history tables to build the restore commands as a SQL script and then executing that SQL script on the destination system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2012 at 8:01 am
Here is the detail info
We are in a project to migrate currently source sql server 200sp4 of A server for QA and B server for PRD in location L1 to the distination location L2 with sql server 2005sp3 of C server for QA adn D server for PRD.
QA migration includs all DBs (more than 30) in server A 2000sp4 located in L1 will be copied (cloned) to server C 2005sp3 in Location L2.
PRD migration includs all DBs (more than 30) in server B 2000sp4 located in L1 will be copied (cloned) to server D 2005sp3 in Location L2.
The data file path in source server is diff than in destination server. All data files in any servers are in the same loactions.
I am very intested in Jeffrey's scripts. It seems what I need. Is it possible you can share the scripts to me?
Thank you very much for your help!
February 27, 2012 at 8:13 am
Your question is one of my favorite interview questions. How they answer tells me a lot about their skills, and whether they are comfortable with scripting to automate mundane tasks.
You can easily script both BACKUP and RESTORE commands.
The Backup script is easy:
SELECT 'BACKUP DATABASE [' + Name + '] TO DISK = ''D:\Backup\' + name + '.bak'''
FROM sys.databases
ORDER BY name
The RESTORE script is easy if the database file locations are the same on the source and destination servers. But if they are not the same, it becomes a bit more complex.
But, it is still doable. The RESTORE DATABASE syntax will have to include WITH MOVE which requires that you know the logical name of each file.
The logical name can be retrieved with this command. Note it will also indicate whether the file is a database or log file.
RESTORE FILELISTONLY
FROM Disk = 'E:\Backups\CCS.bak'
I describe this in more detail at this link, which includes all scripts.
Paul
- Paul
http://paulpaivasql.blogspot.com/
February 27, 2012 at 9:22 am
Hi Paul,
THANK you very much for your reply and your link. It is exactly what I need. I really appreciate your great help!
I will create the scripts for my case based on your info.
Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply