Friday, February 11, 2011 5:02 PM
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.
Friday, February 11, 2011 7:03 PM
Backup DB ---> move the backup copy to the destination server and restore please ....
- Marked As Answer by Alex Feng (SQL)Moderator Monday, February 21, 2011 11:44 AM
Saturday, February 12, 2011 2:02 PM
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
STATS = 10
Hope that Helps!
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!