July 1, 2003 at 8:06 am
Hello Folks,
I attempted to restore a user db today as an awry update caused some bad data. I tried a kill_all_users script so I could put the db into sa only mode before doing the restore. After disabling the users and running the script I was still getting a 'Cannot alter database while in use' type error in EM.
Whats the best way to kick out the users and restrict access before a emergency restore.
Mick
July 1, 2003 at 8:49 am
Make sure you are not in that database in the QA
Shas3
July 1, 2003 at 9:23 am
Try running the following command immediately before the restore:-
alter database dbname set single_user with rollback immediate
July 1, 2003 at 5:12 pm
Put a sp_renamedb statement in your kill script to prevent app or user log right back in when you perform the kill action in master db.
July 2, 2003 at 4:41 pm
Here's a script that we have used successfully for over 3 years to keill users and drop the database. After this step runs in the job we run a restore step then a user creation and permissions step. We found that if the DB wasn't dropped after killing the users we were getting the same error.
"Declare @DatabaseName varchar(50)
Set @DatabaseName = 'My_DB'
--Cursor for all the spids running against this database
DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE dbid =
(SELECT dbid FROM master.dbo.sysdatabases
WHERE name = @DatabaseName)
DECLARE @SysProcId smallint
OPEN SysProc --kill all the processes running against the database
FETCH NEXT FROM SysProc INTO @SysProcId
DECLARE @KillStatement char(30)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
EXEC (@KillStatement)
FETCH NEXT FROM SysProc INTO @SysProcId
END
WAITFOR DELAY '000:00:01'
if (select count(*) from master.dbo.sysprocesses where dbid =
(select dbid from master.dbo.sysdatabases where name=@DatabaseName)) <> 0
BEGIN
EXEC SetMessages
RAISERROR(60009,16,1)
RETURN
END
SET @DatabaseName = QUOTENAME(@DatabaseName)
EXEC ('DROP DATABASE ' + @DatabaseName )
"
July 2, 2003 at 11:55 pm
>dogramone
Good, but I use more faster and optimization script
Declare
@db_name sysname,
@sql varchar(8000),
@spid varchar(8000)
while 1=1 do
begin
set rowcount 1
select @spid = min(spid) from master..sysprocesses where dbid = db_id(@db_name) where spid > 50 and spid > @spid
if (@spid is null) or (@@rowcount = 0)
break
set rowcount 0
select @sql = 'kill ' + @spid
exec(@sql)
end
set rowcount 0
July 3, 2003 at 2:29 am
i am sorry but i don't hnderstand the order of the steps ...
so can anyone give the steps i must take (1,2,3, ... ) to restore the DB to a production server, and prevent any user to login in ..
why I must drop database or rename it ??? i know that the db must be loaded so i cane restore data over it ...
thanks for anyone who reply
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
July 3, 2003 at 8:34 am
Try this. By the way, I used Query Analyzer in this example because it is easier to describe than all the clicking that needs to be done in Enterprise Manager.
Jon
---------------------
-- Determine the name of the database to be restored
-- Open Query Analyzer with the sa (or equivalent) account
-- Make sure that your default database is not the one you are trying
-- to restore; for example, I am using the pubs database for this
-- example
use master
go
-- Put the database into 'single user mode' to drop existing
-- connections and prevent new connections
alter database pubs set single_user with rollback immediate
go
-- Make sure that you are not connected to the database yourself (for
-- example, close Enterprise Manager, which can harbor unexpected
-- connections)
-- Try to avoid dropping the existing database if possible because SQL
-- Server can take a long time recreating the supporting NTFS files if
-- the database is large
-- Display the backup header to determine the file number to be used
-- for the restore; note the "DatabaseName" column and the "Position"
-- column, which contains the file number you will need to do the
-- restore; the following command assumes that your backup device is
-- named "tapedrive"
restore headeronly from tapedrive with unload
go
-- Restore the database
restore database pubs from tapedrive with file = 1, unload, stats
go
-- If the database is still in 'single user mode', reverse the setting:
alter database pubs set multi_user
go
Edited by - shew01 on 07/03/2003 10:59:35 AM
July 3, 2003 at 8:43 am
here's a script i've used for quite a while...
------------------------
USE master
go
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE yourdatabase
FROM DISK = 'c:\path\filename'
with replace
ALTER DATABASE yourdatabase SET MULTI_USER
go
------------------------
I generally run it from a batch file with something like:
osql -E -d master -i db-restore.sql -o db-restore.log -n
July 3, 2003 at 10:46 am
One other thing to consider: Depending on your backup strategy, You could do a full restore and apply all applicable T-logs (to specific point in time) to a new database name, truncate the production table that was damaged and to a DTS wizard import(or whatever you're comfortable with).
This process really depends on how hot the table is of course. There are additional steps you would want to take, but you get the idea....
July 3, 2003 at 11:38 am
Actually, in this case, I would just fire up the Lumigent Log Explorer, locate the 'awry update' and simply undo it..
This assumes you have full transaction logs and lumigent, although I think they have a functional eval..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply