Restore the DB 1

[manmaria]

Whenever I am trying to restore the DB from a backup into a DB, I am not getting full control of the database and getting the erros like 'DB is in use'. How do I take it away from all the users until I restored the DB?

Thanks,

 

[Catadmin]

Here's a kill process cursor I frequently use for such a purpose:

Use Master
GO
Declare @spid as varchar(10),
	@CMD as varchar(1000);

declare cur CURSOR for select spid from master.dbo.sysprocesses where dbid = 
        (select dbid from sysdatabases where name = 'MyDB'
)
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
    set @CMD = 'kill ' + @spid
    exec (@CMD)
    fetch next from cur into @spid
END
close cur
deallocate cur

Make sure to change MyDB to the name of your database before running. The start your restore right afterwards to make sure no one has time to reconnect.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Thanks. Do I need to do any thing after I restore?
I do not understand "The start your restore right afterwards to make sure no one has time to reconnect".

 

[Catadmin]

After the restore, you'll want to go through and fix all the user logins so none of them are orphaned. I've got a cursor for that too.

Use MyDB
GO
DECLARE @username varchar(25)
	DECLARE fixusers CURSOR
	FOR 
	SELECT UserName = users.name FROM sysusers users
	JOIN master.dbo.syslogins logins
 	ON users.name=logins.name
	WHERE users.uid > 2 AND users.issqluser = 1
	ORDER BY users.name

	OPEN fixusers
	FETCH NEXT FROM fixusers
	INTO @username
	WHILE @@FETCH_STATUS = 0

	BEGIN
		EXEC sp_change_users_login 'update_one', @username, @username
		FETCH NEXT FROM fixusers
		INTO @username
	END

	CLOSE fixusers
	DEALLOCATE fixusers

And when I say the thing about the reconnect, I'm talking about my own personal experience. We have several DBAs and Developers here who connect through SSMS or QA. If they're trying to run something and get cut off because I kicked them off the DB, they'll instantly try to reconnect to the server and the database. If they get reconnected before the restore takes over, then the restore fails and I have to kick them off all over again.

@=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Thanks again.
What is this doing here

SELECT UserName = users.name FROM sysusers users
JOIN master.dbo.syslogins logins
ON users.name=logins.name
WHERE users.uid > 2 AND users.issqluser = 1
ORDER BY users.name

I mean what is the purpose? If I get some rows return back means, some users are orphaned and no rows return back means, none of them orphaned?

Thanks,

 

[Catadmin]

Oddly enough, even when I get no rows returned, it sometimes does some "fixing".

That part of the query is looking for SQL Logins to merge with SQL Logins of the same name which are already on the Server. It doesn't fix NT logins because they usually don't need fixed.

Truthfully, the Fix Users code is more helpful when moving a backup to a new instance and restoring there because that is more likely to break user accounts than restoring to the same instance the backup was made from.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"