I have created a certificate and now have taken a backup of this file by the following code:
backup certificate EncrProdCode to file = 'C:BackupCertEncrBackup.cer'
with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk',
encryption by password = 'EncrPrivKeyBackuppwd')
Now i want to create a certificate IN DIFFERENT DATABASE from this file. i m using the following database:
create certificate EncrFromFile from file = 'C:BackupCertEncrBackup.cer'
with private key(file = 'C:BackupCertEncrPrivKeyBackup.pvk',
decryption by password = 'EncrPrivKeyBackuppwd')
But is showing error:
Msg 15232, Level 16, State 1, Line 1
A certificate with name 'EncrFromFile' already exists or this certificate already has been added to the database.
No certificate with this name exists. why i m getting this error. and how can use the same certificate in other database. Is there any other way to create a certificate from an existing certificate file.
NOONE ASKED FOR THIS!!! why the heck is BACKUP CERTIFICATE altering the file's ACLs - it's just a PUBLIC CERT I'm exporting!!
in additions MS doesn't seem able to add the ability to re-inherit file-rights from parent objects via the command-shell.
I export the public-key for a reason! I want to import it on another machine (automatically) to establish a trust. But this freaking "feature" is costing me HOURS of research on how to get rid of these ACLs on a public certificate!!!
argh!!!
sorry, I really had to get this out!
why not at least give us a switch we can set in BACKUP CERTIFICATE command to stop this from happening?
is there a work-around? now I need to activate EXEC sp_configure 'show advanced options', 1 and EXEC sp_configure 'xp_cmdshell', 1 just to be able to issue a (not working) DOS command cacls... to try to fix the "feature".
there's got to be a better way!!!
also - why can't there be a switch to force overwriting the target file, if it already exists? It's a BIG PAIN having to do lots of other stuff, just to be able to "backup" the certificate!
You gave us "xp_FileExist", but no xp_FileDelete with similar parameters!! again, I need to issue DOS COMMANDS just to do stuff I would not have to if someone at the levers was actually activating the back-brain!
PLEASE give me some hints on how MS recommends to solve these features!
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
Hi, I am new to SqlServer (I am using 7.0) and I have just recieved a database.bak file from one of our clients and I want to create a new D/B from this back up file ,I have already installed the MS SqlServer. Can you tell me how to do this.
The error occurs on the CREATE CERTIFICATE statement:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.
I am logged on as a local admin when running the script. The SQL Server service account is also a local admin, and the account I am logged on as is sysadmin in SQL Server. This script works fine in my Dev environment and a separate Pre-Prod environment.
I try to implement mirroring using certificate authentication. When I do this query :
create certificate Principal_cert with subject = 'Principal certificate', start_date = '2007/11/01', expiry_date = '2020/11/01'; GO
Create endpoint endpoint_Princ state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication=certificate Principal_cert, encryption = disabled, role = all);
GO
I have this error :
Msg 1088, Level 15, State 1, Line 1
Cannot find the object "Principal_cert" because it does not exist or you do not have permissions.
I have also noticed that the error does not occured when I try this in the master database.
So, I have two questions:
1. Why SQL server does not find a certificate that just been created ? 2. Should the certificate be in the master database or in the database being mirrored ?
hi all, i want to create a service broker application between two different instances. can i achieve the dialog security and transport security with out using certificate? like we can achieve the transport security using windows authentication (both the instances are in same domain). so how to achieve the dialog security with out using certificates.
I've problems with backing up the certificate (on the Witness Server). On principal and mirror, it works fine (Win 2003 Server, US English), but on my local PC (XP Prof, German) what i use as witness Server following backup command raises an error:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'something$Strong123';
CREATE CERTIFICATE HOST_W_cert WITH SUBJECT = 'HOST_W certificate for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '12/31/2050';
BACKUP CERTIFICATE HOST_W_cert TO FILE = 'C:HOST_W_cert.cer';
The error message is:
Msg 15240, Level 16, State 1, Line 1
Cannot write into file 'C:HOST_W_cert.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
I connect to the database with a domain user what is a local admin on the witness server (my local user), so this couldn't be a permission reason. The file doesn't already exists, so that is not the problem.
I am sure I'm being dumb here but I am trying to deploy an assembly with external_access.
I have signed the assembly using the <new> option in the project properties.
When I then try and create the Key I get the above error using the code below.
CREATE ASYMMETRIC KEY SQLExtensionUDTKey
FROM EXECUTABLE FILE = 'C:Documents and SettingsSimon SabinMy DocumentsVisual Studio 2005ProjectsSQLBitsCoreSQLExtensionsSQLExtensions.UDTinDebugSQLExtensions.UDT.dll'
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write restore filelistonly from disk = 'path and filenam.bak' restore database. zy from disk = 'path and filename.bak' with replace, move..... move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
Let me start by saying that I am more acquainted with a LAMP set up than using Windows/ASP/MSSQL.
I have recently taken over managing a friends website after he had bad experiences with his last web dev and have also had to transfer to a different host as well. The previous dev sent all the site files and database but the database was in MS Access file format [.mdb].
The new hosts required it as an MSSQL backup file [.bak] and I spent several hours of my weekend downloading and installing SQLExpress, reading up on migrations and backups before actually performing one. I uploaded it to the site, as instructed by the new hosts, who would then install it on the SQL server for me.
I received this reply back from them and need some assistance deciphering it. I tried searching for a bit yesterday but found nothing.
Anyone know what this means?
"Apologies for replying late.We have tried to restored backup file dmt.bak , but we could not restored it due to incompatibility of disk structure version,While we tried to restored it we have received following error'
"The backed-up database has on-disk structure version 611.The server support version 539 and can't restored or upgrade this database "
Please provide correct ,bak file with correct version , so we go ahead and restored it for you.".
Hi, I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this? Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything. Thanks for any help.
Hi genius minds, Suppose I have database backup file. Can I create a new database(different than the database whose backup it is) and restore the backup into newly created database. I'm having a backup file of some database, I want to create database from it. Suggest any way to do it? Thx
I have a client that is using the free MSDE database engine. There is no graphical interface that I know of to manage the day-to-day tasks. I need to do a daily backup to a disk file. I want to start the backup at 7:40pm. My client then will copy that disk file to tape duing his system-wide backup at midnight. I setup a batch file and executed it in osql but the job is not working. No disk file has been created in the last 3 weeks. I don't have a clue what I am missing. Can someone please look at this and tell me how to fix it??
Thanks in Advance!
Debbie Erickson USE master EXEC msdb..sp_delete_jobserver @job_name='PetDB Backup', @server_name='Server06' GO USE master EXEC msdb..sp_delete_jobschedule @job_name='PetDB Backup', @name = 'ScheduledBackup' GO USE master EXEC msdb..sp_delete_jobstep @job_name='PetDB Backup', @step_id=0 GO USE master EXEC msdb..sp_delete_job @job_name='PetDB Backup' GO USE master EXEC msdb..sp_add_job @job_name='PetDB Backup', @enabled=1, @description='Backup Petdb' GO USE master EXEC msdb..sp_add_jobstep @job_name='PetDB Backup', @step_name='Nightly petdb maint', @subsystem='TSQL', @command='BACKUP DATABASE Petdb TO DISK = ''E:PetlicData BackupPetdb.bak''', @database_name='petdb' GO USE master EXEC msdb..sp_add_jobschedule @job_name='PetDB Backup', @name = 'ScheduledBackup', @freq_type=4, @freq_interval=1, @active_start_time='194000' GO USE master EXEC msdb..sp_add_jobserver @job_name='PetDB Backup', @server_name='Server06' GO
i am trying to create a SP to create a job to take a differential backup. but i m hving a problem . can i any one help me. what i m doing , is to create job with the same name as the database name ...
I'm building an ASP.NET application that uses SQL DMO to automate database dumps. This will be used by our DBAs to simplify the transfer of databases between server environments. As part of the process, I need to create a backup of an online database and then restore with a different name to run cleanup scripts against it to remove unneeded audit data, etc. I've tried setting objSqlBackup.BackupSetName to accomplish this, but it appears the backup files retain the original database name internally. When attempting to restore the backup, this results in an error saying that the database already exists. Does anyone have a code sample or link that explains how an alternate name can be specified? In SQL Server Enterprise Manager, there is a field for specifying an alternate name when creating a backup. Given that SQL DMO is supposed to offer the same functionality, there must be a way... FYI, The full process is as follows: - Create backup of selected database - Restore database with a different name (this is where I need help) - Run cleanup scripts to clear out unnecessary audit data, etc. - Create backup of "cleaned" database - Create zip file of database backup Thanks in advance for any help you can provide!
Forgive me if I'm asking a simple question, but I'm new to database administration I'm trying to migrate a database from a server running Windows 2000 Server and SQL Server 2000 to a new machine running Windows Server 2003 and SQL Server 2005. I moved a copy of a recent database backup file to the new server, and created a new database with Restore From Backup. The tables seem to have restored fine, but my views are non-existent. Has anyone seen this problem before, or does anyone know of something I may have skipped?
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:<path>FULL.BAK' WITH REPLACE, RECOVERY, MOVE 'PROD' TO 'E:<path>TEST.MDF';
The errors are all cannot execute due to PROD is in use.
Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I checked quickly one network and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Can anybody run me through the process to create a CSV file from an SQLexpress database using BCP.exe ? Or is there a simpler way to create a CSV for that matter? Really important and would be much appreciated.
Hi, If anyone knows ,please tell how to store all fields with comma seperation by select statement into a text file, without using Extended Stored Procedure.
