June 26, 2009 at 5:53 pm
Hi,
We have sql server 2000 databases and have full backups. Unfortunately some updates on the databases went wrong and we need to restore all the databases to last night full backup.
Now Restoring the databases:
1. Should I delete the existing databases first and restore the Backups?
What is the best method?
June 26, 2009 at 8:20 pm
No need to delete or remove the existing databases. The restore operation will overwrite the existing files.
I would recommend taking the databases offline first - then restoring. That way, you can tell which databases have been restored and be assured that nobody is adding data that is going to be wiped out after your restore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 27, 2009 at 12:32 am
rambilla4 (6/26/2009)
Hi,We have sql server 2000 databases and have full backups. Unfortunately some updates on the databases went wrong and we need to restore all the databases to last night full backup.
Now Restoring the databases:
1. Should I delete the existing databases first and restore the Backups?
What is the best method?
To add to what Jeffery said, If you know the time when that update happened and if you have transaction log backups happening, you can do a point-in-time restore, i.e., recover the database to a time just before the update happened, this way u'll retain all other genuine modifications that happened to the database post your full backup till that update.
check out http://msdn.microsoft.com/en-us/library/ms190982(SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms190982(SQL.90).aspx
June 27, 2009 at 11:20 am
Hi,
We are backing up the full, diff and log backups to the same backup device. In order to recover the database to the last transaction log, I followed below steps:
1.Restore headeronly from [db_name]. I'm gettinng below error
Result: No entry in sysdevices for backup device '%.*ls'. Update sysdevices and rerun statement.
Could you please tell me how to resolve this error?
June 27, 2009 at 11:22 am
It should be
RESTORE HEADERONLY FROM FILE = < Backup file location >
You're not restoring from a database, you're restoring from a file.
p.s. Backing up multiple different backup types to the same file is not a particularly good idea. Imagine if there's a disk problem and that one file is unusable. You'll have lost everything.
Most people prefer to make one backup to a file only. Also that makes it easier to back those files up to disk and to easily see what they are
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 12:26 pm
thank you,
We are backing up the full, diff and log backups to the same backup device. In order to recover the database to the last transaction log, I followed below steps:
1.Its worked by using the below command:
RESTORE HEADERONLY FROM DISK = 'Z:\BACKUPS\PROD_06-25-2009.bak'
Sample Results(I did not included all the colmns):
BackupNameBackupTypeExpirationDateCompressedPositionDeviceTypeDatabaseName
PROD--FULL Database Backup1NULL012PROD
PROD--LOG Database Backup2NULL022PROD
PROD--LOG Database Backup2NULL032PROD
PROD--LOG Database Backup2NULL042PROD
PROD--LOG Database Backup2NULL052PROD
PROD--LOG Database Backup2NULL062PROD
PROD--DIFF Database Backup5NULL072PROD
PROD--LOG Database Backup2NULL082PROD
2. Restore the full backup:
restore database PROD
from disk='Z:\BACKUPS\PROD_06-25-2009.bak' with norecovery,stats=10
3.Restore Diffbackup:
restore database PROD
from disk='Z:\BACKUPS\PROD_06-25-2009.bak'
with file=7, norecovery,stats=10
4.Restore the last log backup:
restore log PROD from disk='Z:\BACKUPS\PROD_06-25-2009.bak' with file=8,
recovery
,stats=10
Questions:
1.Backing up the Full, Diff and Log backups to the same backup device(PROD_06-25-2009.bak) is Preffered? or
2.Backing up the Full to Z:\Backups\Full\prod.bak
Backing up the Diff to Z:\Backups\Diff\prod.bak and
Backing up the Log to Z:\Backus\Log\prod.trn
is preffered???
3.To restore the database from the backup method1:
a)IS every time we need to execute RESTORE HEADERONLY FROM DISK = 'Z:\BACKUPS\backup_device and note down the numbers in the Position(bolded column) and start the Restore process accordingly?
b) Is there any automated script to restore frm backup device to know the position column no and apply the subsquent diff backups and log backups?
4.Right now we have all the backups of database going to only one Backup device. So I'm convincing my Boss to use the Backup method2(step 2 from above), so that we can have full backups in separate folder, diff and log also n a separate folder and its easy to restore when we want to restore
Please advice me which one is good and why
Thank You
Ram
June 27, 2009 at 12:49 pm
rambilla4 (6/27/2009)
\Questions:1.Backing up the Full, Diff and Log backups to the same backup device(PROD_06-25-2009.bak) is Preffered? or
2.Backing up the Full to Z:\Backups\Full\prod.bak
Backing up the Diff to Z:\Backups\Diff\prod.bak and
Backing up the Log to Z:\Backus\Log\prod.trn
is preffered???
Neither. My personal preference is that each backup goes to an individual file that has the date and time as part of its name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 2:05 pm
Neither. My personal preference is that each backup goes to an individual file that has the date and time as part of its name
2.Backing up the Full to Z:\Backups\Full\prod.bak
Backing up the Diff to Z:\Backups\Diff\prod.bak and
Backing up the Log to Z:\Backus\Log\prod.trn
is preffered???
Actually in method2, I'm doing the following:
1.Full backup every night to the path Z:\Backups\Full\prod_06-23-2009.bak(with mm/dd/yyyy)
2.Diff backup every 6hrs to the path Z:\Backups\Diff\prod_20090626060000.bak(every 6hrs it creates a new backup file with date, month, time and year)
3.Log backup every hr to the path Z:\Backups\Log\prod_20090626060000.trn(Here I did not mentioned exact timings, just giving example) every 1hr it creates a new backup file with date, month, time and year)
What about the above backup stategy Gail?
June 27, 2009 at 2:31 pm
Looks good.
I know there are scripts around (google should find) that can build a full restore script from the backup records in msdb when all the backups are to separate files. Even if you don't use such a script, having all the backup files visible with the times makes it pretty easy to write restore scripts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 8:49 pm
Hi Gail,
I have one question regarding the restore of a database from a Backup device, which consists full, diff and log backups in it.
Scenario:
1. We have the backup device called PROD_06-25-2009.bak in the path 'Z:\BACKUPS\PROD_06-25-2009.bak'.It has full backup taken at 2:00 AM , differential backup taken at 8:00 AM and log backups taken at 3:00 AM, 4:00 AM, 5:00 AM, 6:00 AM, 7:00 AM, 8:00 AM, 9:00 AM, 10:00AM in it. Now something bad happen to the database at 9:15 AM and I need to restore the database to the last transaction log backup taken at 9:00 AM.
I have restored the PROD database as below:
restore database PROD
from disk='Z:\BACKUPS\PROD_06-25-2009.bak' with orecovery,stats=10
Question:
Now, does the database PROD restored to the last Full backup taken at 2:00 AM only ?OR
does it restored to the last transactional backup taken at 10:00 AM(including Full backup taken at 2:00 AM, diff backup taken at 8:00 AM and 9:00 AM )?
please clarify me?
June 28, 2009 at 2:50 am
You're only restoring the full backup there hence only the full backup will be restored. To get to 9AM, you have to explicitly restore the 8AM diff and the 9am log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply