Restore Error

  • Hi,

    My Full Backup Restore code

    RESTORE DATABASE Webdeccan

    FROM disk = 'G:\Backup\Webdeccan\xxxxxx.BAK'

    WITH NoreCOVERY,

    MOVE 'webdeccan_data' TO 'e:\SQLServer\MSSQL\Data\webdeccan_data.mdf',

    MOVE 'webdeccan_log' TO 'e:\SQLServer\MSSQL\Data\webdeccan_log.ldf'

    code for Restore differential backup

    RESTORE DATABASE Webdeccan

    FROM disk = 'G:\Backup\Webdeccan\Diff.BAK'

    WITH NoreCOVERY

  • First, it would help if you told us what the error is instead of making us guess.

    Next, guessing I would say that your database is still in LOADING mode. And it's obvious why that would be. At no time have you recovered the database, you always leave it in NORECOVERY.

    If you don't have any log files to restore, just run this:

    RESTORE DATABASE Webdeccan WITH RECOVERY

    Your last RESTORE should always have WITH RECOVERY.

    -SQLBill

  • Hi all,

    Can anyone tell me the difference & feature between Overwrite & Append mode in SQL Server Backup for Differential & appropriate usage.

    Thanks in advance,

    Karna

  • Hi all,

    My Full Backup Restore code

    RESTORE DATABASE Webdeccan

    FROM disk = 'G:\Backup\Webdeccan\xxxxxx.BAK'

    WITH NoreCOVERY,

    MOVE 'webdeccan_data' TO 'e:\SQLServer\MSSQL\Data\webdeccan_data.mdf',

    MOVE 'webdeccan_log' TO 'e:\SQLServer\MSSQL\Data\webdeccan_log.ldf'

    This is scheduled daily @ 9:10pm called as New Backup

    code for Restore differential backup

    RESTORE DATABASE Webdeccan

    FROM disk = 'G:\Backup\Webdeccan\Diff.BAK'

    WITH NoreCOVERY

    Scheduled every hour from 12:25:00AM - 11:59:00PM

    I am getting error while restoring differential backup

    Server: Msg 3136, Level 16, State 1, Line 1

    Cannot apply the backup on device 'G:\Backup\Webdeccan\Diff.BAK' to database 'Webdeccan'.

    Server: Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    When I check earlier jobs, I found one full backup job has been scheduled @ every 1 hour starting at 00:00 hrs called as Old Backup.

    So I took old Full backup file earlier to Differential Backup. When I restore differential backup, I used to get same error.

    Please provide suggestions to solve this problem.

    Thanks in advance

    Karna

  • There were many discussions on this topic in this forum. Most likely, the order of your differential backups is not correct. Here are two links. May they helpful.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=143564

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=308260

  • Karna,

    The Append and Overwrite options available when backing up a database using Enterprise Manager equate to the NOINIT (default) and INIT options in T-SQL. NOINIT stores multiple backups in the same file as separate backup sets. INIT overwrites any previous backups, so that the backup file contains only the most recent backup. Note that if you backup a database with any of the exipration date options, and you want to use INIT, use the SKIP option as well.

  • Scheduled every hour from 12:25:00AM - 11:59:00PM

    Is that true, are you really trying to restore a differential backup EVERY hour? Can't do it. You can only restore one Full backup and one Differential. You can restore as many Transaction Log backups as you need.

    For example: do a Full backup at midnight, a differential at noon and log backups every hour during the day. Then depending on when you need to restore to, either restore the the Full Backup, the Differential backup and all log backups made since the Diff backup. Or restore the Full backup and any log backups made upto the differential backup.

    Maybe if you explained what you are trying to do, we could give you better help.

    -SQLBill

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply