Since the new Denali database really rocks my socks.dll, it's time to move the old databases from SQL 2008 R2 to Denali. It's time for a quick and dirty database migration!
If you want to skip all the effort from backing up a database, copy it to another disc and restore it as a new database, then try this!
There are two ways to achieve a succesfull detach and attach, with management studio and with stored procedures, I will give both examples:
First make sure the database you are moving doesn’t have any related services running from the application or has active sessions to the database. ( sp_who Active)
from script:
We will be moving a database called kvdn, we need execute two relative simple queries. First one containing database name and whether we update the statistics. Update statistics will update and index columns and can increase the speed of your queries.
sp_detach_db [ @dbname = ] 'dbname'
[ , [ @skipchecks = ] 'skipchecks' ]
If 'skipchecks' is true, UPDATE STATISTICS will be skipped, to update the existing optimization statistics, change skipchecks to 'false'.
EXEC sp_detach_db 'kvdn', 'true'
now we are going to attach in the new database environment
we need to provide database name and also filelocation for the mdf en ldf files
sp_attach_db [ @dbname = ] 'dbname',
[ @filename1 = ] 'filename_n' [ ,...16 ]
Open up the denali server and run this query to attach the kvdn database
EXEC sp_attach_db @dbname = 'kvdn',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\kvdn.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\kvdn_1.ldf'
We just succesfully moved the database.
and now for the gui way:
right click the database you want to detach and choose detach...
By default, the detach operation keeps your current optimization statistics, to update the existing optimization statistics, click the Update Statistics check box.
Now find the location if your database files, by default this will be "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA"
move the files over to the new file location and open up the management studio where you want to have the database running.
right click database and select Attach...
Now insert the location of the database files
and that's it, your database is up and running.
retrieving the database users:
I have seen examples where the users aren't automatically moved along or stoped working with the attached database, the easiest way is to just remove them, in case they did move and didn't work and recreate them.
or re link them with the sp_change_users_login stored procedure:
EXEC sp_change_users_login 'Update_One', 'User', 'User'