simple & fast sql server database backup and restore

Question

Hi, I'm looking for a simple (simplest if possible) way of backing up and restoring a database.

I want to do a backup in one state and then after doing some operations get back to the backed up state.

Tried Database->Tasks->Back Up... and then Database->Tasks->Restore but I always get an error with:

Restore failed for...DBName

The tail of the log for the database "database name" has not been backed up...

So, I want to back up and restore with one simple operation each, can one advise me to a solution, be it GUI or not GUI based?

Answer 1

It looks like you have Differential Backups set up. It is slightly more complicated than restoring a Full backup.

http://msdn.microsoft.com/en-us/library/ms175510.aspx

By doing a Full backup, you are going to break the chain of backups that the DBA set up, so it would be a good idea to check first with the DBA before you break his backup set.

Edited for very helpful comments:

You should use the COPY_ONLY option when you do a backup so that you do not break the chain of backups.

http://msdn.microsoft.com/en-us/library/ms186865.aspx

Answer 2

When restoring you need to check the option to Overwrite existing database, if you are, otherwise it will fail.

MSSQL 2005 and up features snapshots, sounds like it suits your requirements nicely.

Answer 3

Use the WITH REPLACE option of the RESTORE command:

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.

The topic is also covered at nauseam in MSDN see Restoring Without Using a Tail-Log Backup, which contains the links for 'How to' articles that cover Management Studio scenarios (ie. the tool you're using).