Best Method for Moving SQL Server 2005 Express DB to 2008 R2 in New Domain?

Question

We need to move a database currently running on SQL 2005 Express to a new SQL 2008 R2 Server located in a different domain.  Currently, there are no trusts setup between the two domains.  I've read a couple of different methods online, but don't know what method is recommended.  Some have said I can just backup and restore the database to 2008, while others talk about detaching it and using scripts.  Any help is appreciated.

Regards,

Matthew

Answer 1

Backup DB ---> move the backup copy to the destination server  and restore please ....

-----------------
Thanks,Suhas V

Answer 2

Hello Matthew,

I would recommand you to take a Full backup of user database in SQL Server 2005 Express and restore the same in SQL Server 2008 R2 Instance. Make sure, you create all the logins used by your apps in Production Server.

To sync SQL Logins read the following article on MSDN.

 

Don't follow Detach and Attach methods as once you attach the database to SQL Server 2008 R2 then you wont be able to attach the database or restore the database back to SQL Server 2005 Express for any work. To know more read the following article Why an SQL Server Database from a higher version cannot be restored onto a lower version?

 

Database Backup Script

 

BACKUP DATABASE [AdventureWorks]

TO  DISK = N'C:\UserDatabaseName.BAK'

WITH STATS = 10

 

Database Restore Script

RESTORE DATABASE [AdventureWorks]

FROM  DISK = N'C:\UserDatabaseName.BAK'

WITH  FILE = 1, 

MOVE N'AdventureWorks_Data' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf',  -- Location of Data File

MOVE N'AdventureWorks_Log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Log.ldf',  -- Location of Log File

RECOVERY  ,

STATS = 10

Hope that Helps!

Cheers,
Ashish Kumar Mehta

Please click the Mark as Answer button if a post solves your problem and Vote as Helpful if a Post is Helpful!