giammin

Restore .bak and restore user login association

Jun 16th, 2014
481
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.18 KB | None | 0 0
  1. ---Step 1: Retrive the Logical file name of the database from backup.
  2. RESTORE FILELISTONLY
  3. FROM DISK = 'D:BackUpYourBaackUpFile.bak'
  4. GO
  5.  
  6. --Step 2: Use the values in the LogicalName Column in following Step.
  7.  
  8. ----Make Database to single user Mode
  9. ALTER DATABASE [DBNAME]
  10. SET SINGLE_USER WITH
  11. ROLLBACK IMMEDIATE
  12.  
  13. ----Restore Database
  14. RESTORE DATABASE [DBNAME]
  15. FROM DISK = 'D:BackUpYourBaackUpFile.bak'
  16. WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
  17. MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
  18. ,REPLACE --Overwrite exinting data
  19.  
  20. /*If there is no error in statement before database will be in multiuser
  21. mode.
  22. If error occurs please execute following command it will convert
  23. database in multi user.*/
  24. ALTER DATABASE YourDB SET MULTI_USER
  25. GO
  26.  
  27. --this recreate user/login association
  28. USE [DBNAME]
  29. GO
  30. DROP USER [DATABASEUSER]
  31. GO
  32. IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins  WHERE name = 'DATABASEUSER')
  33. BEGIN
  34.     create login DATABASEUSER with password = N'PASSWORD', default_database = DBNAME
  35. END
  36. USE [DBNAME]
  37. GO
  38. CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER]
  39. GO
  40. USE [DBNAME]
  41. GO
  42. EXEC sp_addrolemember N'db_owner', N'DATABASEUSER'
  43. GO
Advertisement
Add Comment
Please, Sign In to add comment