No. The recover process always starts from a full database backup and then it applies the log backups.
If your database has no backups then it is not in full recovery mode. Untill you actually take a backup it will always run in simple, no matter the intented recovery mode (aka 'pseudo simple', see New script: is that database REALLY in the FULL recovery mode?)
If your database really is in FULL recovery mode, then a backup was tacken and the recovery can start with that.
There are third party tools that can analyze the log and recover stuff, but they work only if the log was not recycled, so is back to the discussion whether the recovery model is really full or simple.
You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:
CREATE CERTIFICATE ERecruitStatsGatheringCert
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY (
FILE = 'd:\DavesCert.pvk',
DECRYPTION BY PASSWORD = 'S3creT!');
But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:
- Create a cert in the user DB
- Add the signature to the store procedure(s)
- Drop the private key of the certificate
- Backup/restore that cert to
master (public key only!)
- Create login in
master, assigned that permission, etc.
Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.
As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.
I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.
Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.
Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].
Best Answer
As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping.
I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window.
Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance overhead.