change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:

> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

change data files name and transaction log name after restore

Hi All,
I have SQL Server database. Server name is DBServer. I have database A
in this server. If i click properties, the data files name show
"A_Data" and the transaction log name show "A_Log". I backup database A
to C:\ABackup.
After that, i create database B. I want to restore database A to
database B. I choose "from device" and select the location to
C:\ABackup. But when i tried to restore, in the Original File Name
(tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
data files name and transaction log name will change into A_Data and
A_Log.
What i want is that in my database B the name of data files and
transaction log not change after i do restore from database A.
Does anybody know how to do that?
Thanks before.On the Restore Database window select your file using 'From device'.
Go to Options. On 'Restore As' update the directory path and file name. You
can not change the logical name here, only the physical name.
Finish the Restore
If you are using SQL Server 2005
Select Database Properties, select Files tab
You can change the Logical Name here.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"afang" wrote:
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Hi
You can restore a database with a different name ,so there is no need to
create a new database prior to restore operation
"afang" <khokimfang@.gmail.com> wrote in message
news:1147053596.925983.99420@.j33g2000cwa.googlegroups.com...
> Hi All,
>
> I have SQL Server database. Server name is DBServer. I have database A
> in this server. If i click properties, the data files name show
> "A_Data" and the transaction log name show "A_Log". I backup database A
> to C:\ABackup.
> After that, i create database B. I want to restore database A to
> database B. I choose "from device" and select the location to
> C:\ABackup. But when i tried to restore, in the Original File Name
> (tab Option) show "A_Data" and "A_Log". If i click OK to restore, the
> data files name and transaction log name will change into A_Data and
> A_Log.
> What i want is that in my database B the name of data files and
> transaction log not change after i do restore from database A.
> Does anybody know how to do that?
> Thanks before.
>|||Thanks Ben, for your information

Certificate Restore Not working.

I have been trying to use Encryption to encrypt a few key fields. So my first goal was to create all the items I would need to effectivly use encryption, to encrypt a few key fields.

First I created a sample sql script to create a certificate, create a table and insert some test data encrypted. And then made sure I could decrypt the data encrypted with the certificate.

Second step was to test the backing up of the certificate, droping the certificate to similate a restore of the database. And then a restore of the certificate, and then see if the it was still possible to decrypt the existing encrypted data with the restored certificate.

I receive no errors when restoreing the certificate, but it does not properly decrypt the exisiting data.

Can anyone help and point out my mistake?

--Start Sql--

--Create Sample Cert CREATE CERTIFICATE SampleCert1 ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' WITH SUBJECT = 'Sample Cert for Demo', EXPIRY_DATE = '04/18/2017'; --create Sample table with encrypted version CREATE TABLE Table_1 (Id int NOT NULL IDENTITY (1, 1), SSN_Encrypted varbinary(300) NOT NULL) ON [PRIMARY] GO --insert row with encrypted version. Insert into Table_1 (SSN_Encrypted) values (EncryptByCert(Cert_ID('SampleCert1'),'000-00-0000')) --returns '000-00-0000' for 'SampleCert1' since it was able to decrypt select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 GO --backup Certificate BACKUP CERTIFICATE SampleCert1 TO FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY ( FILE = 'D:\Backups\SampleCert.pvk' , DECRYPTION BY PASSWORD = N'728AC41753642403251BF8E7233EC0C' , ENCRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh' ); GO DROP CERTIFICATE SampleCert1; GO --Restore from backup. to simulate a restore from tape CREATE CERTIFICATE SampleCert1 FROM FILE = 'D:\Backups\SampleCert.cer' WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk', DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh'); --This should return the same ssn as was encrypted, if returns null the restore failed. select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted,N'728AC41753642403251BF8E7233EC0C') as varchar(12)) as 'SampleCert1' from Table_1 --cleanup Removes Certificate from the system DROP CERTIFICATE SampleCert1; --removes the temporary table. Drop table Table_1

I think I found out why your statement is not working as you expected.

In the following query, the certificate is re-created from the backup, but only a decryption password (used to open the PVK file) is specified, but no encryption password is present. This will create the CERTIFICATE in SQL Server with the private key protected by the DB master key.

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh');

Instead of

CREATE CERTIFICATE SampleCert1

FROM FILE = 'D:\Backups\SampleCert.cer'

WITH PRIVATE KEY (FILE = 'D:\Backups\SampleCert.pvk',

DECRYPTION BY PASSWORD = N'997jkhUbhk$w4ez0876hKHJH5gh',

ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C');

You can verify if my assumption is correct by running the following statement:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates

If I am right, you can just change the protection mechanism for the existing CERTIFICATE:

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '728AC41753642403251BF8E7233EC0C' );

Let us know if this information helped.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Well today is not my lucky day I am afraid. I can't verify yet.

Last night we rolled out a policy to enforce passwords complexity, and length requirements.

So I am geting an error just trying to run the same script, even though I am using NT Authentication, and have updated my password to conform to the new policy.

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

Does Create Certificate use a different security creditial then the loged in Users?

I even tried using SA with the following set. with the same results.

Code Snippet

Alter Login [sa] with CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

Any more idea's where I need to look to figure this out?

|||

Disabling the password policy checks is not available for cryptographic objects (certificates, keys, etc.), the only part of the policy enforced in such objects is the password complexity for newly created or modified objects/passphrases.

ALTER CERTIFICATE SampleCert1

WITH PRIVATE KEY ( ENCRYPTION BY PASSWORD = '<<new password that complies with password policy>>' );

--returns '000-00-0000' for 'SampleCert1' since it was able to decrypt

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted, <<new password that complies with password policy>>') as varchar(12)) as 'SampleCert1'

from Table_1

Or if you prefer to use the DBMK to protect your certificate, you can change your query to the following:

select SSN_Encrypted, cast(DecryptByCert(Cert_ID('SampleCert1'),SSN_Encrypted) as varchar(12)) as 'SampleCert1'

from Table_1

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you for all your help.

Knowing what password I had to fix made it short work geting it working.

Owen.

catching of output for RESTORE FILELISTONLY.

Hi Folks,
How can I catch the output(columns) of the following SQL
command in a table or each column in variables?
RESTORE FILELISTONLY
FROM DISK='c:\temp\FlexKIDS.bak'
I have tried the following construction, which did NOT
work:
CREATE TABLE #LIST_FILE
(LogicalName varchar(120),
PhysicalName varchar(500),
L_Type char(1),
L_FileGROUP varchar(60),
L_size int,
Max_size int)
INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
DISK='c:\temp\FlexKIDS.bak'INSERT #LIST_FILE EXEC ('RESTORE FILELISTONLY FROM
DISK=''c:\temp\FlexKIDS.bak''')
--
Jacco Schalkwijk
SQL Server MVP
"jack" <jbonapart@.dicon.nl> wrote in message
news:090801c3a834$312770e0$a401280a@.phx.gbl...
> Hi Folks,
> How can I catch the output(columns) of the following SQL
> command in a table or each column in variables?
> RESTORE FILELISTONLY
> FROM DISK='c:\temp\FlexKIDS.bak'
> I have tried the following construction, which did NOT
> work:
> CREATE TABLE #LIST_FILE
> (LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
> INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
> DISK='c:\temp\FlexKIDS.bak'|||Insert into #LIST_FILE
exec('RESTORE FILELISTONLY
FROM DISK = ''c:\temp\FlexKIDS.bak''')
This will help to capture the result set into a temp. table
Try this out .!
Regards,
Raghu
>--Original Message--
>Hi Folks,
>How can I catch the output(columns) of the following SQL
>command in a table or each column in variables?
>RESTORE FILELISTONLY
>FROM DISK='c:\temp\FlexKIDS.bak'
>I have tried the following construction, which did NOT
>work:
>CREATE TABLE #LIST_FILE
>(LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
>INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
>DISK='c:\temp\FlexKIDS.bak'
>.
>