Copy over database to a new server

Question

Copy over database to a new server

vkancherlapalli

Grasshopper

Points: 16
More actions
April 8, 2011 at 8:33 am

#145072

Hello,
We want to copy over our existing database to a new and more robust server. What is the best way to 1)copy over all teh objects, logins, mail profiles, tables, view stc. 2) copy over the data 3) Or can we do both, copy over the objects and data?
Thank you.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Backup, restore.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vkancherlapalli Grasshopper Points: 16 More actions · Answer 2

Are there scripts to do the same? Maybe free utilities? The issue is our database is really huge, for calculations it is going to take 2 days to perform a de-attach, and attach process.

Does the backup-restore take a long time?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Huh?

2 days to detach and attach? They're both near instant operations. Do you mean 2 days to copy?

That must be one really large DB. A 1.2 TB DB that I used to work with only took 7 hours to copy, and that was with really, really bad IO subsystem.

If your DB is that big, any way of getting the data across will be more than 2 days. There's no magic tricks to get data to a new server

You should have existing backups, so just copy one across and restore it. The existing backup history will show how long that takes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

If it's only the user database that you're going to backup and restore, then you will need to script out logins, jobs, mail profiles and so on, and create them on the new server.

John

Perry Whittle SSC Guru Points: 234065 More actions · Answer 5

Take source database offline

copy disk files to target server

re attach at target server

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉