Sql-server – What all files are required for restoration process of SQL Server in Full + Diff + Transactional backup policy

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.

You will need to set up a separate backup device for the FULL and the DIFF. When you create the Backup Device it is a single file. You can't save a FULL and a DIFF in the same Backup Device.

More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore. More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx

REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally overwriting a database with a different database. If the database specified in a RESTORE statement already exists on the current server and the specified database family GUID differs from the database family GUID recorded in the backup set, the database is not restored. This is an important safeguard.

The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:

Restoring over an existing database with a backup taken of another database.

With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.

Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.

With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.

Overwriting existing files.

For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.