Data encryption and database restores.
1. When using data encryption on a 2 node active/passive cluster does it make any difference which server the sqlserver is running on when encrypting or dcrypting data?
2. When restring a database to a different server with encrypted data is the data decrytable or is it lost?
I took some database backups from a client site to do some development.
There were 20G, 16G and 200MB
i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.
went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:kupMODTRNMaster.bak'
with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf',
move 'MM' to 'f:mssqldataM1_MLSH_1.mdf',
move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf',
move 'DM' to 'f:mssqldataM1_MLSH_3.mdf',
move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf',
move 'ED' to 'f:mssqldataM1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies View RelatedI did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have a database 30 GB in size. I need to copy this entire database to
laptop. I want to use backup and restore database on this laptop.
I need to know how I can accomplish this wihtout copying the backup file
to the destination server. Is there a way you can restore from a file over the
network from a different server?
I tried everything,with the security setup but was not able to do this.
Any recomendations will be valuable.
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
                    WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
Hi all,
A few weeks back I had a problem with setting up Maintenance Plans on my SQL Server, the only fix in the end was re-install the SQL along with SP1. Since then backing up or restoring our developers database takes around 15-20 minutes whereas before it would take 1-2 minutes.
The server doesn't seem to be the problem as I have restored it to a 2nd server and get exactly the same problem there. Other databases on the same server of around the same size backup as expected.
The database is approximately 150MB's with the log file being 800MB's.
The server in question is a Dual Core Opteron 2.2Ghz with 4GB's of RAM, running Windows 2003 x64, fully patched and up to date. The SQL version is 2005 Standard Edition x64 also including SP1.
Plenty of disk space and no CPU activity even at the time of backups taking place.
Any help is much appreciated.
Recently, we had some tables inadvertantly dropped from a 20+ GB data warehouse in the middle of one of our load cycles. In order to avoid restarting the load, we had to make restore a copy of the database from a full database backup and then manually move in the dropped tables using DTS.
This ended up being a painful process, and I am looking to avoid it by restructuring the database into filegroups so I can mimic the table restore function from 6.5.
I am looking for pros, cons, gotchas, and best practices regarding splitting a MSSQL 7 database into multiple filegroups. We would be including 1 or more tables and all of thier indexes in each filegroup. Specifically, I have the following questions:
1. All the literature I read mentions the need for doing transaction log backups and restores if I utilize filegroup backups. Is this really necessary? We have turned off transaction logging for performance reasons, and since we completely control the addition of new data into the db.
2. Are there implications for disk read performance involved with filegroups? Our data resides on an EMC disk array. I feel that we'll most likely have between 20 and 30 filegroups.
3. If we decide to move a table (and its indexes) between filegroups, is the process as simple a dropping the clustered index and recreating it on the new filegroup (then dropping and recreating the n-c indexes)?
Any information y'all can post would be greatly appreciated.
With Migrating from SQL 2000 to SQL 2005 I realise that there are a few options available.
A SQL 2000 backup is able to be restored into 2005 and is workable however there are some issues with internals that have changed. I have identified some DDL which would need to be modified to work correctly.
If the Backup is restored and left in a recoverable state does 2005 allow for 2000 TLog backups to be restored as well ? I am assuming that as you can restore a full db backup that this is allowed.
I am asking the question as i have a very short and fixed deadline of moving from 2000 to 2005. A new datacentre is being moved to and the old DC is being turned off. all new hardware is being placed in the new DC so i need to move from the old to the new with very little downtime to the DB or app.
My approach is as follows
1. Full DB Backup (2000) copied across to 2005 server and loaded being left in a recoverable state.
2. Tlog backups copied across on scheduled basis and loaded to a point in time.
3. Test new 2005 DB with app/web frontend
4. Bring both in sync again and then perform 1 final Tlog backup, copy and load bringing app online again with very little downtime.
Has anybody tried this approach ? does anybody see any issues with my approach ? all comments welcome on this.
Thx
Hi,
i am trying to encrypt my database using dbEncrypt software.The encryption provides role based access ot the database.In that case developer can not call any stored procedure of that database. These two are conflicting things. Can anyone suggest me a way so that i can access the stored procedure through my code by checking the roles from the front end. is it possible??
Please reply soon.
Hi,
i want to encrypt my database(sql server2005) fully. Can anyone suggest me any third party tool or any other method.
Please its urgent.
Thanx in advance:(
Hi,
I am trying to encrypt my database using some softwares, For that i have to install the software and encrypt database accordingly, i have a querry that will my database remain encrypted if i uninstall the encryption software??
The software i am using are dbEncrypt,Netlib,XP_Crypt,Rsa Bsafe.
Thanx
Divya
is there a way to encrypt a propriatary database so that it may not betampered with except for those who hold the key?
View 1 Replies View RelatedHello I just want make sure: There is no encryption of the whole database.I assume this, because I have found only the themes "encryption for connections" and "encrypted columns"Why this question:
A lot of customer ask for this (maybe without further knowledge)
We've got a merge replication with offline laptops.
So, if one of this laptops get lost, it would calm people if the data were encrypted.I'd be very thankful for a fast reply, as my boss is in a meeting with such customers.Kind regardsAline
i have to encrypt my data in sql2005 database using assymetric key encryption which i have done properly.But i have a doubt that while writing stored procedure i have to provide key information in it , that will be visible to everyone then whats the use of taht encryption. Can anyone tell me how can i write stored procedure without providing key information in it.
Divya
I am 99% sure SQL Server 7.0 cannot encrypt data in an individual column. Can anyone tell me whether I am right or wrong?
View 1 Replies View RelatedDoes anybody know how can I implement data encryption on the sql7.0 database?
I need to do this with some of the fields, like credit card number. My sql server acts as backend database server for IIS servers.
Your help is greatly appreciated!
Donald Ye
Hi,
I am looking for a way to encrypt a column in my SQL Server Table. I have been looking in the books and have not found anything. Any ideas of how to do it?
I need assistance on how to encrypt the data in a column.
View 2 Replies View Relatedhi,
I am a new user to SQL convert my business application from VFP. I am bulding my new application depending on stored procedures. My questions as follow:-
- Is there a way to stored procedures encryption?
- How to products my data structure
regards
Hi,
We need to set up a data export process from a SQL DB.
The output (be it XML, Text Files or whatever) needs to be encrypted before it is FTPd somewhere.
Is there support for encrption in SSIS? How / where in the package designer would you achive this?
Thanks in advance.
Martin
Hello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
We are planning to store the pin number in a sqlserver table. This column has to be encrypted and stored in the database. As new records are added this column needs to encrypted automatically. Please advise me of any tools.
View 1 Replies View RelatedHi!
Could you help me how could I encrypt an SQL Express database with a password?
The lines I currently use:
ssconn.ConnectionString = "Data Source=.SQLEXPRESS;Integrated Security=True;"
CreateDatabase = "CREATE DATABASE i ON (NAME = i, FILENAME = '" & ssceconfilename & "')"
Dim myCommand As New SqlCommand(CreateDatabase, ssconn)
myCommand.ExecuteNonQuery()
I intend to encrypt the whole database structure so that nobody could see in.
I managed to do the encryption with SQL CE 2.0, but how to do it with express?
Thank you!
Hi!
Could you help me how could I encrypt an SQL Express database with a password?
The lines I currently use:
ssconn.ConnectionString = "Data Source=.SQLEXPRESS;Integrated Security=True;"
CreateDatabase = "CREATE DATABASE i ON (NAME = i, FILENAME = '" & ssceconfilename & "')"
Dim myCommand As New SqlCommand(CreateDatabase, ssconn)
myCommand.ExecuteNonQuery()
I managed to do the encryption with SQL CE 2.0, but how to do it with express?
Thank you!
I'm a newbie to SQL and I inherited a 2008 R2 Sql server with about 20 different databases on it. Â I've noticed that more than half of the databases are encrypted and I was looking to encrypt the rest but for some reason I am unable to do so. Â I'm assuming I already have a master key created but when I go to Options of the DB and change Encryption Enabled from False to True I get and error, "Cannot change database encryption state because no database encryption key is set." Â How I could use my existing key to encrypt the databases or would I have to create a separate key for each db?
View 6 Replies View RelatedI'm currently learning how to implement Encryption into my SQL database, and I've run into something I don't know how to explain or understand. I'm working on creating a set encryption for a set of columns in a database, and I don't know how to figure out what size the VARBINARY column needs to be. After some trial and error, I've noticed that a clear text of 9 characters needs 68 varbinary characters, 100 cleartext=148 varbinary, and 254 cleartext= 308 varbinary characters. How is the minimum number of varbinary characters calculated?Â
View 2 Replies View RelatedHi,
I want to backup my database at regular intervals and encrypt it. Also I want to verify the back up. How do I do it?
I want the back ups to run every fortnight.
Thanks.
Hi
Can anyone help?
I have a database using encryption with symmetric keys created using asymmetric keys.
I backed up this database and restored it on another machine along with the service master key.
I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.
If I regenerate all the encrypted stored procs from script they work fine again.
Is there a way to restore the database without having to regenerate all the encrypted procs??
The error in the subject line "The property 'Use Encryption for Data' is not supported" is encountered when running a DTS package via a job step in SQL Server 7.0. The problem is not encountered when executing the DTS package interactively via Enterprise Manager from my PC. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on my PC, I notice a Use Encryption for Data Property. When I view the Advanced Properties of the OLE DB driver from Enterprise Manager on the Server, the Use Encryption for Data Property does not appear. Service Pack 3 for SQL 7.0 is installed on both my PC and on the SQL Server. I suspect this Property difference for OLE DB could be related to the fact that I installed Enterprise Manager for SQL 2000 on my PC at one time and then uninstalled. Any ideas how to correct this situation?
View 1 Replies View RelatedIs there any way to encrypt the sql server mdf and ldf data files in SQL Server 2005 without using EFS or creating an additional system user? Only one application which has the password embedded (i know this is a security risk) should be able to connect to the database and access the mdf and ldf files.
Greetings
Roland Mayr
My current experience is with access and sql2000. In my current application, I compress/encrypt my data prior to storing in my database. Does the new verion of sql support compression/encryption and if so can you please point me to any links which discuss.
thanks,
Fred Herring