How to Locate and Restore from Dump File

Question

Hello,

I have SQL Server 2008 Developer Edition.  I recently encountered an array of problems while attempting to set up db mirroring.  Bottom line is I was unable to restore my database on either the primary or mirror instance.  I have since dropped the database and created another one with the same name.  My question is how do I locate the dump file and restore from it?  I tried looking for files with .dmp extension but haven't found any.

Thanks!

---

Scott Olander

Answer 1

Try running a query like the following from within the database in question, this should show you all of the backups taken and where the backup file is located...

Select
	s.database_name As [DatabaseName],
	m.physical_device_name As [BackupFileName],
	Cast(Cast(s.backup_size / 1000000 As Int) As Varchar(14)) AS [SizeMB],
	s.backup_start_date As [BackupStartTime],
	Case 
		When s.[type] = 'D' Then 'Full'
		When s.[type] = 'I' Then 'Differential'
		When s.[type] = 'L' Then 'Transaction Log'
	End As BackupType
From 
	msdb.dbo.backupset s
	Inner Join 
		msdb.dbo.backupmediafamily m 
	On 
		s.media_set_id = m.media_set_id
Where 
	s.database_name = Db_Name()
Order By 
	backup_start_date desc

Thanks

Answer 2

hi scott,

You have to make sure few things before enable mirroring.

1) when you restore the database from backup to secondary make sure your mdf and ldf files are pointing to that local instance DATA folder.

2) as you know it must me restored with no recovery mode.

3) make sure SQl agents are running on both instancess .

4)setup mirroring and makesure your endpoints are configured correctly. (look in  sys.endpoints)

5) take a snapshot at secondary and check mirroring is working. it is a simple process.

if you encounter any error let me know i can help you resolving those.

thanks

kumar

 

below link have helpful information how to get the system information regarding the backup history.

http://www.sqlbackuprestore.com/backuprestorehistorytables.htm

Answer 3

Thanks!  I was able to locate the backup file.  I think all my problems are encryption related b/c now I am encountering same error message as I first did when I began the process:

RESTORE DATABASE SQL2008SBS

FROM DISK = 'SQL2008SBS_1.bak'

WITH REPLACE

GO

Processed 296 pages for database 'SQL2008SBS', file 'SQL2008SBS' on file 1.

Processed 216 pages for database 'SQL2008SBS', file 'SQL2008SBSFG1_Dat1' on file 1.

Processed 144 pages for database 'SQL2008SBS', file 'SQL2008SBSFG1_Dat2' on file 1.

Processed 176 pages for database 'SQL2008SBS', file 'SQL2008SBSFG1_Dat3' on file 1.

Processed 8 pages for database 'SQL2008SBS', file 'FG2_dat' on file 1.

Processed 16 pages for database 'SQL2008SBS', file 'FG3_dat' on file 1.

Processed 8 pages for database 'SQL2008SBS', file 'CDC' on file 1.

Processed 2 pages for database 'SQL2008SBS', file 'SQL2008SBS_Log' on file 1.

Msg 3283, Level 16, State 1, Line 1

The file "SQL2008SBSFG1_Dat1" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "SQL2008SBSFG1_Dat2" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "SQL2008SBSFG1_Dat3" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "FG2_dat" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "FG3_dat" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "CDC" failed to initialize correctly. Examine the error logs for more details.

Msg 3283, Level 16, State 1, Line 1

The file "SQL2008SBS_Log" failed to initialize correctly. Examine the error logs for more details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally

Do you guys have any idea on this one?  I had turned encryption off and deleted some keys and certificates prior to taking the backup.  Now I see that this was not the correct way to go...

Thanks again, Scott

---

Scott Olander

Answer 4

scott,

is that a backup file or backup device.

if it is device, do you know exactly what is the file position. you can try some thing like this

RESTORE DATABASE SQL2008SBS

FROM DISK = 'SQL2008SBS_1.bak'

WITH file = 1 --get this from header only command

GO

restore header only from disk = 'give the full path of the backup location'

i assume you will be using full path in your restore command.

my strong feeling from the list of very first error is data file is trying to create mdf in the primary server as it do not have previlages.

have you changed the .mdf & .ldf file to the data folder on the secondary server.

thanks

kumar

Answer 5

Do you have TDE enabled for the database from which you took the backup?

Answer 6

Can you please check,it is saying that failed to initialize.. did you checked what each file size physically and where you are restoring does it have space on the Drive..

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 7

Yes.  I think TDE is the root of all my problems.  When I first tried to restore to the mirror instance I encountered "missing thumbprint for certificate" errors so I went back to the db on the principle instance and dropped some certificates and keys and disabled encryption instead of backing up the keys like I should have.  And now after dozens of attempts and error messages later I cannot restore to either the principal or the mirror.

---

Scott Olander

Answer 8

If you do not want to use TDE then completely remove the TDE and then take the backup of the database and try to restore.

USE MASTER
GO
ALTER DATABASE DBNAME
SET ENCRYPTION OFF
GO
USE DBNAME
GO
DROP DATABASE ENCRYPTION KEY
GO

Answer 9

If you do not want to use TDE then completely remove the TDE and then take the backup of the database and try to restore.

USE MASTER
GO
ALTER DATABASE DBNAME
SET ENCRYPTION OFF
GO
USE DBNAME
GO
DROP DATABASE ENCRYPTION KEY
GO

Just to add:

You might also need to wait till de-cryption completes before initiating the Backup, you can use the below mentioned query:

Use <DB_Name>
GO
/* The value 3 represents an encrypted state 
   on the database and transaction logs. */
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

To undertstand the output refer: http://msdn.microsoft.com/en-us/library/bb677274.aspx

---

Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 10

I did have TDE enabled on the db from which I took the backup.  The problem is I have since dropped that db and created a new one with the same name and am trying to restore to it from the backup taken from the original encrypted db.

Thanks,

Scott

---

Scott Olander

Answer 11

I did have TDE enabled on the db from which I took the backup.  The problem is I have since dropped that db and created a new one with the same name and am trying to restore to it from the backup taken from the original encrypted db.

Thanks,

Scott

---

Scott Olander

in order to complete this restore you need to first re-create\restore the certificate used to encrypt the original DB.

---

Sarabpreet Singh Anand

Blog , Personal website

This posting is provided , "AS IS" with no warranties, and confers no rights.

Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.