Moving a database to a new server

My database server is being decommissioned at work. We have a new database server to take its place. Both servers have the same operating system and SQL Server 2008. I need to move all my databases and data over to the new server.

What is the best way to do this to ensure as few problems as possible?

My data is not just tables, views and stored procedures. There are also several SQL Server Agent jobs and several database maintenance plans and scheduled backups.

The system is not being used at night, so I have a late night opportunity to take the database offline, if need be.

Can I just copy the MSSQL directory over to the new server? Or should I just backup each database individually? If I do backup each database, should I back up the system databases (like master, model and msdb) too?

Doing a backup and restore will be a good way to copy over all the user databases.

• Backup DBs on the source server.

• Copy the backup file to the destination server

• Then restore the DBs onto the destination server.

The the system DBs are a bit different. Follow this link for one possible way. Then again, it depends what you need from the system tables. The master contains your SQL logins, which you can script out. Here is a link for scripting out the logins.

StackExchange Database Administrators Q#47508, answer score: 5