August 6, 2015 at 9:28 am
Hi,
Can you help me please? I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.
I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
Thanks for any approaches,
J.
August 7, 2015 at 4:22 am
This is really straight forward. You can use the RESTORE command and provide a new name. You just have to use the WITH MOVE options of the command in order to move and rename the files of the database. That's it. The documentation even has examples of how to do exactly what you're looking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2015 at 7:06 am
Thank you Grant - I found what I'm looking for. I notice that there is a verify only option to restore.
RESTORE VERIFYONLY FROM DISK=blah
Worth considering from my pov.
Thanks,
J.
August 7, 2015 at 7:25 am
VERIFYONLY is a good test of a backup. However, it's an incomplete test. For it to be more complete, you must use CHECKSUM when you take the backup. Then the verification process will validate the checksum too. But, even then, it's still incomplete. The only way to completely validate a backup is to do a restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2015 at 9:42 am
Thanks grant. Food for thought all right.
Cheers,
J.
August 7, 2015 at 10:04 am
One further note, if you're going to do this programmatically, and more than once, you'll get the same issue with filenames. Perhaps if you restore you want to have the user pick the db name and adjust file names for the WITH MOVE? Or use some token like a date/time marker in the names.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply