May 9, 2007 at 11:35 pm
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
May 10, 2007 at 8:26 am
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
May 10, 2007 at 10:33 pm
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
May 10, 2007 at 11:03 pm
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
May 11, 2007 at 7:45 am
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://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=143564
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=308260
May 11, 2007 at 7:46 am
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.
May 11, 2007 at 9:29 am
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