none
Restore backup SQL Server 2005 - Msg 3154 RRS feed

 > 

  • Pergunta

  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Boa tarde pessoal,

    Estou fazendo backup full e de log de uma base e quero restaurar em outra, quando vou restaurar aparece a mensagem de erro 3154. Estou utilizando por linha de comando.

    ** BACKUP FULL
    use Karine
    GO
    BACKUP DATABASE Karine
    TO TAPE = '\\.\tape0'
    WITH FORMAT,
    NOREWIND,
    NoUnload,
     NAME = 'Backup Full Karine';
    GO


    ** Resultado
    Processed 176 pages for database 'Karine', file 'Karine' on file 1.
    Processed 2 pages for database 'Karine', file 'Karine_log' on file 1.
    BACKUP DATABASE successfully processed 178 pages in 3.988 seconds (0.363 MB/sec).

    OK

    ** BACKUP LOG
    use Karine
    GO
    BACKUP LOG Karine
    TO TAPE = '\\.\tape0'
    WITH NOFORMAT,
    NOREWIND,
    NoUnload,
     NAME = 'Backup Log KARINE - 09:00HS';
    GO

    ** Resultado
    Processed 2 pages for database 'Karine', file 'Karine_log' on file 2.
    BACKUP LOG successfully processed 2 pages in 3.363 seconds (0.003 MB/sec).

    OK

    ** BACKUP LOG ultimo
    use Karine
    GO
    BACKUP LOG Karine
    TO TAPE = '\\.\tape0'
    WITH NOFORMAT,
     NAME = 'Backup LOG Karine 17:50hs';
    GO


    ** Resultado
    Processed 0 pages for database 'Karine', file 'Karine_log' on file 3.
    BACKUP LOG successfully processed 0 pages in 6.493 seconds (0.000 MB/sec).

    OK


    Para restaurar em outra base:

    Full:
    RESTORE DATABASE TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH FILE=1,
    NORECOVERY;
    GO

    ** Resultado
    Msg 3154, Level 16, State 4, Line 1
    The backup set holds a backup of a database other than the existing 'TesteKarine' database.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Karine Porton Analista de Infraestrutura Unimed Tubarão

    quarta-feira, 2 de abril de 2014 18:04
    |

Respostas

  • Question
    Entrar para Votar
    1
    Entrar para Votar

    Utilize o parâmetro WITH REPLACE.

    http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    quarta-feira, 2 de abril de 2014 19:23
    |
  • Question
    Entrar para Votar
    1
    Entrar para Votar

    Deu certo, além de usar o REPLACE, usamos também o MOVE. O Restore ficou assim:

    RESTORE DATABASE TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH REPLACE,
    FILE=1,
    NORECOVERY,
    MOVE 'Karine' TO 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\TesteKarine.mdf',
    MOVE 'Karine_log' TO 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\TesteKarine_log.ldf';
    GO

    RESTORE LOG TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH NORECOVERY,
    FILE = 2;
    GO

    RESTORE LOG TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH RECOVERY,
    FILE = 3;
    GO

    Karine Porton Analista de Infraestrutura Unimed Tubarão

    • Marcado como Resposta Karine Porton quarta-feira, 2 de abril de 2014 20:23
    quarta-feira, 2 de abril de 2014 20:23
    |

Todas as Respostas

  • Question
    Entrar para Votar
    1
    Entrar para Votar

    Utilize o parâmetro WITH REPLACE.

    http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

    Vithor da Silva e Silva | MCTS - SQL Server 2008, Implementation and Maintenance Site: http://www.vssti.com.br Blog: http://vssti.blogspot.com

    quarta-feira, 2 de abril de 2014 19:23
    |
  • Question
    Entrar para Votar
    0
    Entrar para Votar

    Ola Vithor,

    Já tinha tentando, mas olha o erro que aparece:

    RESTORE DATABASE TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH REPLACE,
    FILE=1,
    NORECOVERY;
    GO

    **RESULTADO

    Msg 1834, Level 16, State 1, Line 1
    The file 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Karine.mdf' cannot be overwritten.  It is being used by database 'Karine'.
    Msg 3156, Level 16, State 4, Line 1
    File 'Karine' cannot be restored to 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Karine.mdf'. Use WITH MOVE to identify a valid location for the file.
    Msg 1834, Level 16, State 1, Line 1
    The file 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Karine_log.ldf' cannot be overwritten.  It is being used by database 'Karine'.
    Msg 3156, Level 16, State 4, Line 1
    File 'Karine_log' cannot be restored to 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\Karine_log.ldf'. Use WITH MOVE to identify a valid location for the file.
    Msg 3119, Level 16, State 1, Line 1
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Karine Porton Analista de Infraestrutura Unimed Tubarão

    quarta-feira, 2 de abril de 2014 19:31
    |
  • Question
    Entrar para Votar
    1
    Entrar para Votar

    Deu certo, além de usar o REPLACE, usamos também o MOVE. O Restore ficou assim:

    RESTORE DATABASE TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH REPLACE,
    FILE=1,
    NORECOVERY,
    MOVE 'Karine' TO 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\TesteKarine.mdf',
    MOVE 'Karine_log' TO 'i:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\TesteKarine_log.ldf';
    GO

    RESTORE LOG TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH NORECOVERY,
    FILE = 2;
    GO

    RESTORE LOG TesteKarine
    FROM TAPE = '\\.\tape0'
    WITH RECOVERY,
    FILE = 3;
    GO

    Karine Porton Analista de Infraestrutura Unimed Tubarão

    • Marcado como Resposta Karine Porton quarta-feira, 2 de abril de 2014 20:23
    quarta-feira, 2 de abril de 2014 20:23
    |