• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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.
0
timgreen7077
Asked:
timgreen7077
  • 2
  • 2
  • 2
  • +5
2 Solutions
 
WayneATaylorCommented:
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!

Wayne

0
 
Anthony PerkinsCommented:
Start here:
Moving Database Files
http://msdn.microsoft.com/en-us/library/ms189133(v=SQL.90).aspx

This MSDN article will take you to the following articles:
Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx

Moving User Databases
http://msdn.microsoft.com/en-us/library/ms345483(v=SQL.90).aspx
0
 
sqlserverdbaCommented:
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
http://msdn.microsoft.com/en-us/library/ms191450.aspx

to script logins, use the microsoft provided sp below
http://support.microsoft.com/kb/918992

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.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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

 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 .


 http://msdn.microsoft.com/en-us/library/ms142159.aspx
0
 
mark_gaoCommented:
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
0
 
Scott PletcherSenior DBACommented:
Is the version of SQL you are moving from and to the same?
0
 
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!
0
 
David ToddSenior DBACommented:
Hi,

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 ...

HTH
  David
0
 
Anthony PerkinsCommented:
I suspect the author is MIA...
0
 
David ToddSenior DBACommented:
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.

Regards
  David
0
 
timgreen7077Exchange EngineerAuthor Commented:
Sorry for the delay. I have alot of projects and it have been really busy. Thanks for all the replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now