:::: MENU ::::

Posts Categorized / Encryption

  • Jun 09 / 2010
  • 0
Backup and Restore, dbDigger, Encryption, Security and Permissions, T-SQL Scripts

The backup set holds a backup of a database other than the existing ‘*’ database.

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
  • Apr 28 / 2010
  • 0
Changing passwords for logins used with SQL Server maintenance plans
Backup and Restore, dbDigger, Encryption, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Configure SQL server password protected backups creation and cleanup

Securing the backup and data files is more important than to establish optimal rights and privileges for database users. SQL Server backup file contains data and log files and it can easily be restored once in hand. It is critical to device a mechanism where backup file should be safe at disk and also another layer of protection is required in backup file itself.
Several tools can be found for backup file encryption, but i have discussed an SQL Server facility to create password protected backup files. It may be used with SQL Server 2000/2005/2008. You can read full article on this topic here.
There are two considerations in this regard

  • The applied password encryption on backup file would not be as reliable as in strongly encrypted files
  • This feature may be removed in future versions of SQL Server, so use it till SQL Server 2008 only
  • Sep 01 / 2009
  • 0
dbDigger, Encryption, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission

My article related to Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission. It compares both methods (using WITH ENCRYPTION clause and VIEW DEFINITION permission)to hide SQL Server objects code.

Consult us to explore the Databases. Contact us