Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unhappy Unanswered: Serious Restore Problems

    Im unable to restore from a backup file.

    I turned the file into a restore device and asked the server to restore it.

    first time nothing happens I can wait forever the progress bar remains unchanged at 0.

    I stop the restore and then try again

    This time the progress bar starts moving up to a certain point, there its stuck again.

    the bottom line, I cant restore from these backup files (full backup recovery model by the way)

    the database remains in (Loading) mode and I cant access it, I found some other way to recover the data so its not lost but Im very concerned about not being able to recover from the backups.

    did anyone encounter this problem before? whats causing it?

    thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I'm sorry to say that I can't really tell what's going on with your database, but this is a restore-script I struggeled for AGES to find when my database needed a restore:
    Code:
    USE Master 
    GO
    
    CREATE PROCEDURE sp_KillEmAll @dbname varchar(128) = NULL AS
    
    IF @dbname IS NULL BEGIN PRINT '' PRINT 'WARNING!!!!' PRINT ' This procedure will
    kill all active connections to the database that is passed to this procedure!' PRINT
    '' RETURN (2) END
    
    SELECT spid, CONVERT(varchar(15),db_name(dbid)) AS DB,
    CONVERT(varchar(15),convert(sysname, rtrim(loginame))) as loginname INTO #tokill FROM
    master.dbo.sysprocesses (nolock) WHERE db_name(dbid) = @dbname
    
    IF @@Rowcount = 0 BEGIN PRINT 'no database connections exist!' RETURN (0) END
    
    declare @spid int, @sqlKillCmd varchar(50) DECLARE CR_ConnectionsToKill CURSOR FOR
    SELECT spid FROM #tokill OPEN CR_ConnectionsToKill FETCH NEXT FROM
    CR_ConnectionsToKill INTO @SPID WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status
    <> -2) BEGIN select @sqlKillCmd = 'Kill ' + convert(varchar,@spid) execute
    (@sqlKillCmd) select @sqlKillCmd END FETCH NEXT FROM CR_ConnectionsToKill INTO @SPID
    END CLOSE CR_ConnectionsToKill DEALLOCATE CR_ConnectionsToKill
    
    drop table #tokill 
    RETURN (0) 
    GO
    
    
    exec sp_KillEmAll @dbname = 'dbname'
    go
    
    RESTORE DATABASE dbname FROM 
    DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\dbname_db_200311120300.BAK' 
    WITH NORECOVERY
    
    RESTORE LOG dbname FROM 
    DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\dbname_tlog_200311120300.TRN' 
    WITH NORECOVERY
    
    RESTORE LOG dbname FROM 
    DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\dbname_tlog_200311120600.TRN' 
    WITH NORECOVERY
    
    RESTORE LOG dbname FROM 
    DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\dbname_tlog_200311130000.TRN' 
    WITH RECOVERY,
    STOPAT='2003-11-12 22:50:00'
    
    DROP PROCEDURE sp_KillEmAll 
    GO
    The procedure in the beginning of the script kills absolutely all connections to the database and it get's dropped at the beginning of the script so it doesn't get run by accident. *Hopefully* it will be helpful in some way...be aware that this is a point in time restore from a db with full recovery model. Also be aware that the last backup/translogfile you restore must have the keywords WITH RECOVERY, this is VERY IMPORTANT!

    Good luck man...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •