Primero debemos eliminar los procesos que estén conectados a la base de datos:
USE Master
GO
--Eliminar todos los procesos pendientes
DECLARE @spid INT,
@dbName VARCHAR(50),
@cmd VARCHAR(50)
DECLARE #Cursor CURSOR LOCAL FOR
SELECT spid ,dbname = CASE WHEN dbid = 0 THEN null WHEN dbid <> 0 THEN db_name(dbid) END
FROM master.dbo.sysprocesses
WHERE spid >= 0
AND spid <= 32767
AND LOWER(db_name(dbid)) = 'TuDB'
OPEN #Cursor
FETCH NEXT FROM #Cursor INTO @spid,@dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'KILL ' + CONVERT(varchar(5),@spid) + ';'
EXECUTE (@cmd)
FETCH NEXT FROM #Cursor INTO @spid,@dbName
END
CLOSE #Cursor
DEALLOCATE #Cursor
GO
Ahora restauramos la base de datos:
--Restaurar la base de datos
RESTORE DATABASE TuDB
FROM DISK = 'C:\TuBackUP'
WITH REPLACE
GO
USE TuDB
GO
Si así lo requieren, podemos relacionar los Logins de SQL con los usuarios en la base de datos:
--Relacionar login con user
DECLARE @name AS NVarchar(100)
DECLARE #Cursor CURSOR LOCAL FOR
SELECT name
FROM sysUsers
WHERE hasdbAccess = 1
AND isLogin = 1
AND issqlUser = 1
AND name NOT IN ('dbo','guest')
AND name IN (SELECT name COLLATE database_default FROM master..syslogins)
OPEN #Cursor
FETCH NEXT FROM #Cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC TuDB.dbo.sp_change_users_login 'UPDATE_ONE',@name,@name
FETCH NEXT FROM #Cursor INTO @name
END
CLOSE #Cursor
DEALLOCATE #Cursor
Salu2.
No hay comentarios:
Publicar un comentario