July 22, 2011 at 5:05 am
Dear All,
In our organization, there is need of moving more than 100 databases from one db server to another.
Can you advise how to move in a short way to save the time.
Thanks and Regards,
Ravi.
July 22, 2011 at 9:48 am
It depends on your environment/application/db size.
If you can schedule down time then detach/copy/attach will work (copy time must be taken into consideration)
If you have 99.999 policy detach/attach is impossible so you'll have to use Transactional Replication or:
1. Backup DB
3. Restore with_norecovery to new server
4. Set old db to read_only
5. Backup T-log from old db
6. Restore T-log to new db with_recovery
7. Change db settings in application code or config file or ...etc.
July 22, 2011 at 9:51 am
don't forget to script your logins from the old server to the new one with sp_help_revlogin
Lowell
July 24, 2011 at 5:15 am
if I may join in this discussion, I am in a similar position of migrating a sql 2005 DB to another sererver. My DB detils are as follows:
- simple recovery model
- around 700Mb in size
- runs imports from another db daily using SSIS packages saved as files and scheduled in windows scheduler
I have no problems bringing the DB offline and migrate using detach/attach method, in this case please let me know what the exact procedure do I have to follow? Is there anything that I have to be aware regarding SSIS packages, I suppose I can just copy those files to the new location as they are saved as files.
I have a few varcharmax data types in my tables, I read somewhere when they exceed certain size they will be saved separately - in this case do they have to be migrated separately?
If I use the backup method illustrated above, can I just ignore the part about Transaction logs. Also please let me know, what needs to be done for post migration.
How exactly can I script migrate the logins in sql 2005?
Thanks in advance.
July 25, 2011 at 12:38 am
(edited as I almost forgot the original poster)
general instructions here: http://support.microsoft.com/kb/314546
As for migrating 100 databases, this should get you on the right track. this is using the "backup/restore" option from the kb. You'll need to copy the login's separately.
USE AT YOUR OWN RISK, If you don't understand, don't run it!
-----------------------------------------------------------------------
--quick and dirty from memory, so just consider this a starting point please
--
--backup\restore databases from SERVER1 to SERVER2
-----------------------------------------------------------------------
use master
go
declare @backupDir varchar(260) -- location to copy backup files to, \\SERVER2\restores
create table #databases_to_move (dbname sysname)
--populate table with appropriate db names.
insert into #databases_to_move (dbname) select name from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb', 'distribution')
create table #database_files (dbname sysname, seq int, type int, fname sysname, fpath varchar(260) )
insert into #database_files
select db.name, row_number() over (partition by db.name, mf.type order by file_id), type, mf.name, mf.physical_name from sys.databases db inner join sys.master_files mf on db.database_id = mf.database_id where db.name in (select dbname from #databases_to_move)
--query to create the db backups and place the
/*
--Template We're going for:
--stop activity on the db.
ALTER DATABASE <db> SET RESTRICTED_USER WITH ROLLBACK AFTER 10;
--create backup
BACKUP <db> TO DISK = '<@backupDir>\<db>.bak' WITH COPY_ONLY;
--Set DB Offline. If something goes wrong, we can just bring it back online
ALTER DATABASE <db> SET OFFLINE WITH ROLLBACK IMMEDIATE;
*/
--run this script, take the results and copy/paste a new window. and double check.
select 'ALTER DATABASE ['+dbname +'] SET RESTRICTED_USER WITH ROLLBACK AFTER 10; ' +
'BACKUP ['+ dbname +'] TO DISK ='''+@backupDir+'\'+dbname +'.bak'' WITH COPY_ONLY;'+
'ALTER DATABASE ['+dbname+'] SET OFFLINE WITH ROLLBACK IMMEDIATE;' from #databases_to_move
--script to do restores.. run this on server2. And triple check it before you run it.
--It assumes 1 log file per db, and a max of 3 data files per db, though easy to tweak.
select 'RESTORE DATABASE '+dbname+' FROM '''+@backupDir+'\'+dbname+'.bak'' WITH '+
--Log file
' MOVE ''' + (select max(fname) from #database_files df where df.dbname = db.dbname and type = 1 ) + ''' TO ''' +
(select max(fpath) from #database_files df where df.dbname = db.dbname and type = 1 ) + ''', ' +
--Data File 1
' , ''' + (select max(fname) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 1 ) + ''' TO ''' +
(select max(fpath) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 1) + ''' ' +
--Data File 2 if it exists
case when exists (select * from #database_files df where df.dbname = db.dbname and type = 0 and seq = 2) then
' , ''' + (select max(fname) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 2 ) + ''' TO ''' +
(select max(fpath) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 2) + ''' ' else '' end +
--Data File 3 if it exists
case when exists (select * from #database_files df where df.dbname = db.dbname and type = 0 and seq = 3) then
' , ''' + (select max(fname) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 3 ) + ''' TO ''' +
(select max(fpath) from #database_files df where df.dbname = db.dbname and type = 0 and seq = 3) + ''' ' else '' end + ';' -- END OF RESTORE
+ 'ALTER DATABASE ['+dbname+'] SET MULTI_USER; ' -- can't remember if this is needed...
from #databases_to_move db
July 25, 2011 at 2:15 am
My script is also based on the assumption you can have downtime on each database long enough to perform a backup and restore. if not, the method Alex mentioned above is your best bet.
(though, I personally prefer setting the DB to OFFLINE instead of detaching.)
good luck!
-J
July 25, 2011 at 2:23 am
Since you asked the question I assume your not a regular DBA and just wants a solution which would get the job done. If your the performing the activity I would suggest a simple back up and restore. Mainly because fewer things can go wrong. Make sure the logins are scripted seperately, As a test try doing it for a couple of databases and see if your able to restore properly then go ahead with the rest of the databases.
Usually there are third party tools that take good backups and allow to restore them as well , see if your company uses one.
Else more advanced methods include using an SSIS package , using dettach attach , A simple file system backup.
the copy database wizard in SSMS etc.
To be safe make sure you have access to a good DBA when you start the process.
July 25, 2011 at 3:37 am
Thanks for the link provided, as you assumed I am not a regular DBA - please let me know what happen to varcharmax data when it exceeds 8000bytes.
July 25, 2011 at 3:44 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply