Typically, backups to file are appended with a timestamp (e.g. MyDatabase_FULL_201202060900.bak) so you have the option of using a script to generate the restore sequence given a directory of files. Google will give you dozens of examples, as will the SSC script library. I have this script in my library currently.
If the server you've taken the backups from is available, you can generate the restore script from the msdb
tables dbo.backupset
and dbo.backupmediafamily
, example here. Note SSMS will generate the correct script for you and you can use Profiler to capture the queries it uses to do so.
If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The frequency depends on your required SLA (RTO & RPO), more on it here.
Second, you should definitely be automating your backup tasks - both full and log backups (if you are taking log backups). There are hundreds of reasons that can justify automating the tasks, my favorite one is I don't have to run/repeat the task again in a well planned automation.
In order to automate your backups using SQL Server Agent jobs, you can either write your won scripts, test it and implement to suit your environment, or pick up one of the freely available scripts and modify to suit your need. My favorite is Ola Hallengren's free sets of scripts, which he publishes and updates on his website. These scripts have been around for a while, and are well tested in various SQL Server environments.
Restore
Assuming your database is in full recovery mode, and you have taken your full backups and log backups, following are a simplified steps for database recovery.
Perform the tail-of-the-log backup to backup with NORECOVERY
option. This is the log generated since the last log backup was
executed.
Restore the most recent full backup with NORECOVERY
option.
Restore all the log backups since last full backup beginning the
first log backup done after the full backup to the most recent log
backup with NORECOVERY
option.
Restore the tail-of-the-log backup with NORECOVERY
option.
Recover the database with RECOVERY
option.
Note: If differential backup is part of your backup plan, you may have to restore the most recent differential backup before you restore the log backups, if the most recent differential backup was taken after the most recent full backup.
The database recovery could be more complex depending on the physical layout of your database files, and your recovery objectives etc. You can learn more about backup and recovery in SQL Server book online. Aslo, Paul Randal has a series of blog post on backup and recovery on his Accidental DBA series.
Best Answer
There is no need to take a FULL backup before and after running
DBCC CHECKDB
. It is recommended that your FULL backup go before theDBCC CHECKDB
so that you know exactly which FULL backups don't contain database corruption ifDBCC CHECKDB
ever fails due to corruption. The FULL backup before the last successfulDBCC CHECKDB
is one without corruption.To recover from corruption, you can use the FULL+DIFF+LOG chain or FULL+LOG chain. Pick the FULL backup from before the last successful
DBCC CHECKDB
. If there was a DIFF before the last successfulDBCC CHECKDB
, then you can use that too. But a DIFF after the last successfulDBCC CHECKDB
could have database corruption, depends when the DIFF occurred and when the corruption occurred.LOG backups do not contain data pages, so they do not have the corrupted data pages in them. Thus, you can recover with no data loss if you have the entire LOG chain and the tail of the log after the non-corrupted FULL or non-corrupted FULL+non-corrupted DIFF.