Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ---Step 1: Retrive the Logical file name of the database from backup.
- RESTORE FILELISTONLY
- FROM DISK = 'D:BackUpYourBaackUpFile.bak'
- GO
- --Step 2: Use the values in the LogicalName Column in following Step.
- ----Make Database to single user Mode
- ALTER DATABASE [DBNAME]
- SET SINGLE_USER WITH
- ROLLBACK IMMEDIATE
- ----Restore Database
- RESTORE DATABASE [DBNAME]
- FROM DISK = 'D:BackUpYourBaackUpFile.bak'
- WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
- MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
- ,REPLACE --Overwrite exinting data
- /*If there is no error in statement before database will be in multiuser
- mode.
- If error occurs please execute following command it will convert
- database in multi user.*/
- ALTER DATABASE YourDB SET MULTI_USER
- GO
- --this recreate user/login association
- USE [DBNAME]
- GO
- DROP USER [DATABASEUSER]
- GO
- IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'DATABASEUSER')
- BEGIN
- create login DATABASEUSER with password = N'PASSWORD', default_database = DBNAME
- END
- USE [DBNAME]
- GO
- CREATE USER [DATABASEUSER] FOR LOGIN [DATABASEUSER]
- GO
- USE [DBNAME]
- GO
- EXEC sp_addrolemember N'db_owner', N'DATABASEUSER'
- GO
Advertisement
Add Comment
Please, Sign In to add comment