Monthly Archives: March 2010

Sql Server: The backup set holds a backup of a database other than the existing (Restore failed)

There are few common tasks in SQL server that should work out of the box, but give errors that confuse less experiences developers. Like when you try to restore a backup from another server, you get:

Restore failed for Server ‘.\SQLEXPRESS’.  (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘SomeDb’ database. (Microsoft.SqlServer.Smo)

The solution could not be simpler: you should use WITH REPLACE option of RESTORE command, or in SQL Server Management studio select “Options” page and check “Overwrite the existing database (WITH REPLACE)”

SQL Server 2008 ldf file size

SQL Server is smart in selecting great default options for a new database. If you can afford to lose the changes between backups to keep maintenance simple (and in most of non-critical databases it is a reasonable compromise)  and you backup your database regularly using a tool like SQLBackupAndFTP, then the “Simple” recovery model (selected by default) is appropriate for you.  However if you ever had massive data operations, you may notice that the size of your transaction log (LDF) file is huge. The reason for it is that SQL server does not automatically shrinks the size of transaction log.

To keep log file under control, it may be tempting to enable Auto Shrink option. That would be a mistake – it is an evil option that should always be off. Shrink is a heavy procedure that should only be used rarely.

Instead just run this command to decrease the size of the data and log files to leave 10 percent free space (read more):
DBCC SHRINKDATABASE (YourDatabaseName, 10)

For more precise operation use DBCC SHRINKFILE or you can just do it in Management Studio

It is not over yet! Most often shrink will increase fragmentation of your indexes. To defragment indexes, you’ll need to use ALTER DATABASE – see this link