How to resotre MS SQL backup and fixed user incorect associate to login

When you restore a DB to a new create Database or Users has changed after backup the database,  you may notice that user name in the restored database not correctly associate to user in logins.
the SQL Command as below will restore database name call myDb and fix the User user1 associate to user name in Logins.


-- Full backup database , WITH FORMAT says overwrite the bak file if it is already existed.
BACKUP DATABASE myDb
    TO DISK = 'd:\backup\myDb.bak'
    WITH FORMAT;
GO

-- Restore Sample 1
USE master
RESTORE DATABASE myDb
FROM DISK = 'd:\myDb.bak'
WITH REPLACE
GO

-- Restore Sample 2

-- 1. List File Set position in backup file
RESTORE HEADERONLY
FROM DISK = 'd:\myDb.bak'
GO
-- 2. List Logical Name from particular file set position in backup file
RESTORE FILELISTONLY
FROM DISK = 'd:\myDb.bak'
WITH FILE = 2
GO

-- 3. Restore data from particular file set position and move backup data to specify location

-- 'MyDB' and 'MyDB_log' is Logical name from step 2.
--  MyDB.mdf is data store, MyDB_log.ldf is DB log file
RESTORE DATABASE myDb
FROM DISK = 'd:\myDb.bak'
WITH MOVE 'MyDB' TO 'd:\DB\MyDB.mdf'
,MOVE 'MyDB_log' TO 'D:\DB\MyDB_log.ldf'
,FILE = 2, REPLACE


--
USE myDb

-- the following statement say if login 'test_user' not exist just create it with password.
IF NOT EXISTS(SELECT name FROM master.dbo.syslogins WHERE name = 'test_user')
BEGIN
    CREATE LOGIN test_user WITH PASSWORD = 'Password123', CHECK_POLICY = OFF
END
GO

-- Create new user for login
-- for sql server 2005/2008
USE myDb
CREATE USER user1 FOR LOGIN test_user WITH DEFAULT_SCHEMA = dbo

-- for SQL Server 2000
EXEC sp_adduser 'login1', 'user1'

-----------------------------------------------------------------
-- the Syntax of sp_adduser
sp_adduser [ @loginame = ] 'login' 
    [ , [ @name_in_db = ] 'user' ] 
    [ , [ @grpname = ] 'group' ]

-- Set default schema to user
USE myDb
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo;

-- Add db_owner role to the user
EXEC sp_addrolemember 'db_owner', 'user1'

-- the 'update_one' is the parameter value of @action in sp_change_users_login

-- the 'test_user' is user name in myDb

-- the 'user1' is login name

-- the third parameter password no require if you specific the first param @action='update_one'

EXEC sp_change_users_login 'update_one', 'test_user', 'user1';

------------------------------------------------------------------- the Syntax of sp_change_users_login
sp_change_users_login [ @Action= ] 'action' 

    [ , [ @UserNamePattern= ] 'user' ] 
    [ , [ @LoginName= ] 'login' ] 
    [ , [ @Password= ] 'password' ]
[;]

-- If you got a error while restore db: Exclusive access could not be obtained because the database is in use.
-- find out which user connecting the db and kill the spid
-- Show all the users connection include spid.
EXEC sp_who
-- Kill the users who is connecting the db, 52 is spid.
KILL 52



-- Change DB Logic Name
-- display current using DB logic name
select * from sysfiles

-- change logic name

ALTER DATABASE MyDB

MODIFY FILE (NAME =  'MyDB_log' , NEWNAME = 'MyDB2_log');

See MSDN