Moving SQL server database from one server to another

[jtodd]

Hey guys. I am trying to move a SQL database from one SQL server to another. One is SQL server 2003 and the destination is running SQL server 2008. Can anyone provide some guidance with this. I have never done this before so any pointers with be greatly appreciated! Thanks!

[JorgeM]

It sounds like the versions you are referring are not the SQL version, but the server operating system versions. For example, there is no SQL 2003. So I am assuming Server 2003 --> Server 2008.

If you have the same version of SQL installed on both server, all you need to do is copy the database related files (DB and Log) from one server to the other. Open SQL management studio on the target server and use the existing DB and Log file(s) that you copied over instead of creating a new DB.

[pritaeas]

Microsoft has a Migration Assistent available to help you port your SQL Server 2003 to 2008 (R2).

[jtodd]

After some clarification, what I intended to ask is how do I migrate a database(logins and all) from SQL server 2005 to SQL server 2008(not r2). Any pointers or pitfalls I should be aware of? Thanks!

[thewebhostingdi]

For this first you need to Detach the database from MSSQL 2003 then Attach the database in MSSQL 2008

Or you can also use export/import for this.

[relalimarmo]

You can also do a full DB backup from the source server then copy/restore the backup file on the new server. This leaves your original source DB intact. You may need to use the MOVE option if you want the data/log files in different drives/paths.

[tsmorris]

You can list the users in the database like this:

SELECT d.name, 
       s.name,
       d.type, 
       d.type_desc
  FROM sys.database_principals d
  LEFT OUTER JOIN sys.server_principals s ON s.sid = d.sid
 WHERE d.type IN('G','S','U')
   AND d.name NOT IN('guest','INFORMATION_SCHEMA','sys') 

With domain logins you can add them pretty easy as long as the two servers are in the same domain. If you have SQL logins, you can add them with the same sid and password using this code:

/*
SELECT name, sid, password_hash
  FROM sys.sql_logins
 WHERE name IN('george','Paul02','Sam_Wilson01','training03')
 ORDER BY NAME

SELECT name, sid, -- SQL 2000
       password
  FROM master.dbo.sysxlogins WHERE name = 'C02N03'

*/
CREATE LOGIN george WITH PASSWORD= 0x01005848A307A55D1A1C93E2D3ADF54BC2CEE8561A5CC4B63988 HASHED, DEFAULT_DATABASE=[TempDB], 
                          CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, SID= 0xC2629FE601A8A948860D6B46AC3848B3

Adding them with the same sid will connect the users in the database with the logins you add automatically. Since the windows logins get their sid from the domain, you don't have to worry about that with them.