May 19, 2010 at 8:16 am
Hi all
I'll need to migrate a 160Gb -3rd Party- database.
I'm shifting from a server running MS SQL 2000 to a new server with SQL 2005.
I'll have to migrate this database to the new server.
The business will be down for one day, so that nobody will access to any of these servers.
First of all, I though to do a backup of the database and restore it in the new server.
Because of it's an huge database, I don't know if I'll find any problem doing the backup (time-cosuming, transaction log, etc).
I'm considering to Detach/Attach instead of using Backup/Restore. As the database will be stopped, I don't need to worry about the transactions that are currently being doing in the database in that moment.
Am I missing something?
Thanks a lot
May 19, 2010 at 8:26 am
I would take a FULL backup as a precaution, then use detach/attach. It's probably faster since you bypass the backup & restore steps and only need to do the file copy. If you use a 3rd party tool for backups, it might not make as much difference since your compressed backup file to copy will be much smaller.
May 19, 2010 at 8:29 am
Ditto. Backup the db, shouldn't be too long at that size. 160GB isn't that large.
Detach, copy, attach.
You'll have orphaned users, so I'd use sp_help_revlogin (from MS) to script logins, sp_change_users_login to fix them.
May 19, 2010 at 8:41 am
As already mentioned, take a FULL backup as a precautionary measure. Once you attach the files to SQL Server 2005, those files cannot be detached and attached back in SQL Server 2000.
SInce you indicated am I missing anything, I would like to suggest you have a check list of things that are there currently on the SQL Server 2000 which might be needed on the SQL Server 2005 server, such as Login, (Steve already indicated), any linked servers used in queryies of the current database, Maintenance plans / Jobs related to this database etc..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 19, 2010 at 10:06 am
Thanks guys for your answers!
When you say 'backup'.. do you mean an SQL Backup? or files backup?
We are doing backup of the drive which contains the database every day, and we'll do one before starting the migration.
Just a 'silly' question: If you recommend to do a SQL Backup, why don't you recommend to restore that backup in the new server instead of detach/attach the database? I thought the longest process was the backup generation.
No problem for users/linked servers/.. since it's a database of a 3rd party application and I've got the documentation of the objects we need to create during the database (re)installation.
Thank you all very much
May 19, 2010 at 10:29 am
I was referring to SQL Server Backup, am sure others also meant SQL backups.
Detach / attach is quicker than backup / restore since it avoids the backup step and restore step.
Backup / restore needs additional space for the backup file on the SQL Server 2005 ( in your case).
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 19, 2010 at 10:38 am
I agree with Bru and Steve.
Perform a full backup. Then you can detach, copy, attach the database.
Also, do a SQL backup. I would not rely on a file system backup for the database backups.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2010 at 1:13 pm
Restore v attach, 6 of one. Either way works. I don't have a big preference.
As Jason mentioned, no file system backups on a regular basis. Always do a SQL backup to a drive physically separate from data/logs. That way you are protected.
May 19, 2010 at 1:27 pm
muten79 (5/19/2010)
We are doing backup of the drive which contains the database every day, and we'll do one before starting the migration.
That's not the way you back up a SQL database.
Firstly the database files are open and in use while the SQL service is active, hence, unless the backup tool can backup open files, it won't be able to back the database files up.
If it can back open files up, there's a chance that it will back the data and log files at slightly different times while there are transactions occurring, and attempting to attach those backed up files will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2010 at 2:26 pm
muten79 (5/19/2010)
We are doing backup of the drive which contains the database every day, and we'll do one before starting the migration.
Then you may already be set up for failure. Are you aware of how to do SQL backups and if so then why aren't you doing them?
I tend to use BACKUP and RESTORE rather than attach/detach for migrations because using a sequence of full and log backups allows you to minimise downtime to just a few minutes if necessary. Doing testing and then a dry run before the actual migration is obviously a good idea but if you rely on detaching then that means downtime just for testing purposes, which is a costly disruption you could do without. Finally, there's no harm in making absolutely sure you have a good backup, ie: by restoring it on the new server. After all, you were going to take a backup anyway before you detached weren't you?
May 20, 2010 at 3:06 am
Hi all
Thanks for your help.
I didn't explain it very well. We'll make a backup of the system state.
The old server is dedicated to that database and, if something goes wrong, we can restore the system without problem.
After reading all your useful posts, I think I'll go for backup/restore instead of attach/detach. Since you say the size of the database is not too much large (I thought it was!), I'll go for that option. I've done the process previously with small databases and feel more confident with backup/restore.
Thank you all!
May 20, 2010 at 7:48 am
Steve Jones - Editor (5/19/2010)
Ditto. Backup the db, shouldn't be too long at that size. 160GB isn't that large.Detach, copy, attach.
You'll have orphaned users, so I'd use sp_help_revlogin (from MS) to script logins, sp_change_users_login to fix them.
By the way, how long could it take to do the backup?
May 20, 2010 at 8:07 am
muten79 (5/20/2010)
By the way, how long could it take to do the backup?
It is hard to predict to without knowing what system specs you have.
I just found out on one of our server, performing a compressed backup of a 150 GB database took 40 minutes.
I would recommend you to look at the information in your MSDB backupset table and find out yourself, based on which you will have a better idea.
Remember the backup time would be different based on what location / medium you are performing, backup to local disk is quicker than backup to tape drive ..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 20, 2010 at 9:21 am
I agree with Bru, hard to predict. It depends on system load (since that will change the amount of stuff in the backup, potentially, with log records) and then also the CPU load/pressure and the IO. If you backup to the same disks that store data (bad idea), you are competing with the buffers, the bus, the disk heads. If separate, then it should be faster.
40 Minutes compressed, 150GB doesn't sound horrible. A little slow, but not that bad.
May 20, 2010 at 3:43 pm
muten79 (5/20/2010)
By the way, how long could it take to do the backup?
Does this question mean that you are not taking regular Full SQL backups already ? If not, you should take a FULL backup NOW, and make sure you also take regular transaction log backups. (every hour or more)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply