Recently i applied password protected backups for my SQL Server databases as explained in my article Using passwords with SQL Server database backup files. Backups and file removal task worked successfully in test environment. Before moving the encrypted backups setup to production systems i just performed a hands on activity to be smooth with restore process of password protected backups.
I created password protected full backup of AdventureWorks with following script to work with restore scenarios.
After creating backup successfully in ‘D:Testing’ directory, i planned it to restore over an existing database AdvTest.
It is notable that password protected backups may not be restored through SSMS, so we have to use T-SQL scripts for this purpose.
I got password used for backup by following select statement
select replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips' GO
Using following script, i tried to restore AdventureWorks backup on existing AdvTest database
Use master GO RESTORE DATABASE AdvTest FROM DISK='D:TestingAdventureWorks-2010-05-31 15-04-53.bak' WITH MEDIAPASSWORD='2010-05-31-MSSQLTips' GO
Restoration of password protected backup failed on existing database failed with following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdvTest’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
After some research on issue i came to know a work around. It is required to use WITH REPLACE option for restore of backup on another database with different name. So i modified my script as
Use master GO RESTORE DATABASE AdvTest FROM DISK='<span style="color: #3333ff; font-family: courier new; font-size: small;">D:TestingAdventureWorks-2010-05-31 15-04-53.bak</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">' WITH REPLACE, MEDIAPASSWORD='</span><span style="color: #3333ff; font-family: courier new; font-size: small;">2010-05-31-MSSQLTips</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">', MOVE 'AdventureWorks_data' TO 'D:DB FilesAdvTest.mdf', MOVE 'AdventureWorks_log' TO 'D:DB FilesAdvTest_log.ldf'
Here i have specified the data and log files of source and target. It is important to note that if database is being restored on same database to which it belongs then move option is not required to be specified.
For example if we are required to restore our AdventureWorks backup on AdventureWorks database then following script would work
Use master GO RESTORE DATABASE AdventureWorks FROM DISK=’D:TestingAdventureWorks-2010-05-31 15-04-53.bak‘ WITH REPLACE, MEDIAPASSWORD=’2010-05-31-MSSQLTips‘, GO
To get logical and physical file names of any database for use in above scripts, use following command
Use AdvTest GO sp_helpfile GO