Avoid Database Restore Issues (Errors 3219 or 3514) by a Simple Script Edit - the DB Restore Work-around

, 2012-03-18

Starting with the 1000 foot view: During the process of applying changes to a production database - let's call it Change Management (pick your ITIL or COBIT method accordingly), we should always go through a thorough testing process on an exact copy and environment too, if possible, of the production database itself. Developers especially are familiar with this process, since they are often handed off a database backup and told to simply 'deal with it', however they often find themselves in this frustrating situation where Error 3154 or 3219 comes up, and this post is to explain the simple work around, instead of reading a big thread on MSDN.
Straight to the point, there is a quick method to avoid Error 3154: The backup set holds a backup of a database other than the existing database message and 3219 which are related errors to restoring. In other words, the non-matching backup set restore failure that I have been stumped on occasionly, until I remember to edit the script as below.

Isabella Island, Galapagos, Ecuador

Here's a typical Restore script run in SQL Server 2008/5 - which will fail because the media set is not matching:

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' F
ROM
DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10 
-- replace is because you are restoring over

First create an empty database with the desired Test nomenclature for your respective environment.  Notice, that if we take out both the File=  headers for the restore (struck out below), we will be able to successfully restore the backup file even if the database is from an entirely different server.

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' F
ROM
DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10

Now, in another scenario, if we are doing this on databases being used in production, and require a quick restore, there will possibly be a log file (tail of the log file error) problem that can be mitigated by first doing a log backup prior to full restore, switching the database to single user mode, then restoring over top of the existing database (no replace needed below since it's on the same server, same DB) with the specific restore point, finally followed by a switch back to Multi User mode.

use master
GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'MyDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDatabase')

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDatabase'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDatabase] FROM
DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10

GO

ALTER DATABASE [MyDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you are simply starting up the restore on a new server for the very first time or overwriting the same database again, then you will only need a typical restore.

use master
go


RESTORE DATABASE [RestoreDirectlyDBfromBackup] FROM 
DISK = N'DriveName:\SQLBackups\BackupFile.BAK'
-- N'\\NetworkServerExample\SQLBackups\DBNAME_db_2009date.BAK'
WITH  FILE = 1,  MOVE N'DatabaseName_data' TO N'DriveName:\SQLData\DBName.mdf',  MOVE N'DatabaseName_log'
-- NB. if you have multiple data files you will need to do a MOVE X To FileLocation for each one of them
TO N'DriveName:\SQLLogs\DBName.ldf',  NOUNLOAD,  STATS = 10

 

Happy Restoring 🙂 

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads