Moving SQL Database

We have a new server and I want to move our SQL Database to the new server and retire the older server.
can anyone offer any solid advice on moving this SQL 2005 DB. Any tools that will work or anything. I've never done this so any help will be great. I can research online, but I find this site to be reliable.
LVL 24
timgreen7077Exchange EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can do a server to server database copy by using the COPY/MOVE database wizard within the server manager, but I have had issues using this myself so I often tend to do a manula backup and the restore each database, but of course the copy wizard is a lot easier if it works!


Anthony PerkinsCommented:
Start here:
Moving Database Files

This MSDN article will take you to the following articles:
Moving System Databases

Moving User Databases

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Like Wayne said, you can restore a manual backup.

Also, to add - you need to script out jobs and logins if you need them on the new server. Obviously you need to know which jobs, logins are required. This is for moving the database to the new server and making it available, allowing apps, people to connect. To script jobs, you can right click and see the option to script

to script logins, use the microsoft provided sp below

You would need to setup database mail, allowing notifications to be sent (if this is prod server)

Depending on what you have like replication, log shipping, mirroring, linked server connections, you would need to reestablish the same.

(If you have a new IP address and servername, you would need to help your application team with this info for establishing connections, request for backups, monitoring for new server.)

These are few starting points, but, there are other factors like do you have other apps on the server, use the server for ssis/ssrs, oracle, etc. You would need to assess what is on the server, and determine if all of them need to be moved over. Of course you need more research from your side depending on your requirements.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

you would also need to setup maintenance plans for backups, optimizations..
SThayaTechnical MAnagerCommented:

 you can accomplish your task with SSIS . use the
 "Transfer SQL Server Objects task " to perfrom this .

Note : Make sure that your old server user Login accounts are in active status , otherwise it will through Error. First Delete the De-Active Login Account then proceed .

This is the easiset way .
you should use backup/restore database method
this method have many benefit:
simple,old server keep running,and you can test in new server
below are suggest method:
1:backup msdb,master system database and restore to your new server,restore master database need sql server  in single user mode;this will export your logins,jobs to your new server
2.backup your user database (if your have backup jobs ,use copy_only option) and restore to your new server with norecovery option
3.stop applications from connecting to your old server , kill active session,backup log
4.restore log to your new server with recovery option
5.configure you application to connect to your new server
Scott PletcherSenior DBACommented:
Is the version of SQL you are moving from and to the same?
Scott PletcherSenior DBACommented:
If the versions are the same, you can just copy the existing physical files to the new server and start up SQL as normal!
David ToddSenior Database AdministratorCommented:

What I've done in the last couple of server upgrades, is start an overnight restore to the new box from the current server. Then in the morning you can test the results and when the results approach perfect, you can think about going live.

If your backups are named something like myDatabase_yyyymmddhhmmss.bak then it maybe useful to use a console utility to copy the latest backup from current server to new server and rename the file to myDatabase_backup.bak or something like that. If the utility uses the filesystem object it can easily identify the latest file that looks like myDatabase*.bak, rather than parsing a directory listing using SQL. Which if we change the language and region from the default US to our preferred New Zealand ...

Anthony PerkinsCommented:
I suspect the author is MIA...
David ToddSenior Database AdministratorCommented:
Hi AC,

I agree.

Suggest delete with points refund. While there are some good ideas here, we're not sure - since the asker is mia - if they really match the actual situation.

timgreen7077Exchange EngineerAuthor Commented:
Sorry for the delay. I have alot of projects and it have been really busy. Thanks for all the replies.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.