January 20, 2016 at 5:56 am
Hi all
I have database that is set with log shipping to another secondary database. Also have a test database that is basically a copy of live ans its updated now and then.is It possible to make a full backup of a specific past date of the live database? For example if i want restore my test database with data of 2days ago.
Thanks
January 20, 2016 at 6:44 am
If I understand your question correctly you cannot BACKUP a database to a time in the past, but you can RESTORE it to apoint in time in the past.
You need to start from the last FULL backup previous to when you want to restore it to, restore that with norecovery mode and then roll forward through any intermediate backups after that full, i.e. differentials or log backups
---------------------------------------------------------------------
January 20, 2016 at 7:41 am
In addition to George's reply:
1. You can only use this technique to restore to a point in the past if you were already making both full backups and log backups, and if the database is in the "FULL" recovery model; and
2. If you do need a backup of the situation two days ago (e.g. to send the backup to someone), you can use the point-in-time restore method George describes to restore to a new database (so you leave the current database intact but restore to a new database that will look like your real database two days ago), then backup that database.
January 20, 2016 at 7:46 am
Thanks George and Hugo. The problem is that i can't find any full backups of the database, only the differential ones. I will start making full backups also.
January 20, 2016 at 7:55 am
Ouch, that sounds painful.
If you have differential backups, than at least one full backup must have been made - otherwise SQL Server will not allow you to make a differential backup.
However, if you lost the file that carries that full backup, you are in a bad situation. Every restore always has to start with a full backup, all the rest (both differential and log backups) are based off a chain that starts with a full backup.
Can you run the query below? Hopefully (depending on how aggressive your msdb backup history is cleansed) it will return the date and time of the last full backup, which might help you find the file location of the last full backup.
SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE database_name = 'perFact'
AND type = 'D';
January 20, 2016 at 8:11 am
Thanks for the script. The last full backup was almost 3 months ago... i think is better to create a daily full backup.
Thanks for your help.
January 20, 2016 at 9:19 am
For production systems, most companies like to do daily full backups, plus transaction log backups every 15 minutes or so. If the activity is fairly low, you could change that to every 30 or even every 60 minutes.
Differential backups are often not used, but some companies like to have one about halfway in business hours. (Lunch break can be a good time)
For databases that have fairly low usage, you can consider doing a weekly full backup and a differential in the other six nights (plus transaction log backups every one or two hours or so).
All the above applies to "normal size" databases only. VLDB is a ballpark of its own.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply