I found myself doing several database migrations this weekend and because these databases were generally small (Less than 10 GB), I choose the route to detach the database, copy the files to the new location and re-attach.
To remove the databases, I detach them from the current instance. I am not worried about backup history with this as the server will be decommissioned.
[sourcecode language=”sql”]EXEC master.dbo.sp_detach_db @dbname = N’MyDatabase’
If you refresh your database list, you will no longer see the database listed; however, the files are still on the server. You can now copy those to the new location and attach them. You need only pass in the name of the database and the location of the files and SQL Server will add the database and make it available to you.
[sourcecode language=”sql”]EXEC sp_attach_db @dbname = N’MyDatabase’,
@filename1 = N’E:MSSQLMyDatabase.mdf’,
@filename2 = N’F:MSSQLMyDatabase_log.ldf’; [/sourcecode]
Don’t forget to add your security and any jobs on the old server you need to copy over for this database.