none
AI tentar recovery banco acuso em uso memso sem single_user e fechando conex~oes RRS feed

  • Pergunta

  • Pessoal preciso voltar um bkp de uma banco, algi bem simples porém mesmo ao deixar o banco em single_user e matar todas as conexões dp mesmo, ainda acuso o erro de banco em uso.

    Msg 3102, Level 16, State 1, Line 1
    RESTORE cannot process database 'SCC_TI' because it is in use by this session. It is recommended that the master database be used when performing this operation.

    Estou usando esta sequencia:

    USE SCC_TI
    
    
    --Colocar a base de dados em modo single_user
    
    alter database SCC_TI set single_user with rollback immediate
    
    
    
    
    -- Agora vamos realizar a tarefa de matar as conexões
    
    declare @execSql varchar(1000), @databaseName varchar(100) 
    -- Set the database name for which to kill the connections 
    set @databaseName = 'SCC_TI' 
    
    set @execSql = '' 
    select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ' 
    from master.dbo.sysprocesses 
    where db_name(dbid) = @databaseName 
    and 
    DBID <> 0 
    and 
    spid <> @@spid 
    exec(@execSql) 
    
    --- iniciando o restore
    
    RESTORE DATABASE [SCC_TI] 
    FROM  DISK = N'F:\OneDrive - IPAEAS-UCB\SERVERSQLAPS\Backup\SQL\Diario\SCC_TI\SCC_TI_backup_2019_04_01_220225_4077571.bak' 
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
    GO
    
    
    --Agora vamos repor a base de dados para modo multi_user:
    
    alter database SCC_TI set multi_user with rollback immediate
    
    Alguma sugestão?


    Moizés Cerqueira | MCP / MCTS SQL SERVER

    quinta-feira, 4 de abril de 2019 19:17

Respostas

  • Pessoal consegui resolver usando desta forma:

    USE [master]
    ALTER DATABASE SCC_TI SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    
    RESTORE DATABASE SCC_TI
    FROM  DISK = N'F:\OneDrive - IPAEAS-UCB\SERVERSQLAPS\Backup\SQL\Diario\SCC_TI\SCC_TI_backup_2019_04_01_220225_4077571.bak' 
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
    
    ALTER DATABASE SCC_TI SET MULTI_USER
    
    GO

    usando pelo banco master

    e resultado foi este:

    Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
    12 percent processed.
    24 percent processed.
    37 percent processed.
    49 percent processed.
    61 percent processed.
    74 percent processed.
    86 percent processed.
    99 percent processed.
    100 percent processed.
    Processed 1032 pages for database 'SCC_TI', file 'SCC_Data' on file 1.
    Processed 1 pages for database 'SCC_TI', file 'SCC_Log' on file 1.
    RESTORE DATABASE successfully processed 1033 pages in 4.356 seconds (1.852 MB/sec).

    Valeu!


    Moizés Cerqueira | MCP / MCTS SQL SERVER

    quinta-feira, 4 de abril de 2019 19:37