SQL Server 2014 :: Add A Step To Add To Encrypt Backup

Jul 7, 2015

I have inherited a job that is calling a maintenance plan. The maintenace plan makes a back-up of various databases.I have to add a step to add to encrypt the backup. I have the exe already. Its called "EncryptBackup.exe".My difficulty is I do not know how to add the step to the job exactly. The command syntax eludes me a bit. The syntax for the encrypter exe is:

C:exesEncryptBackup.exe C:DBBackupFolderA Encypt filename.bak

The output is "filename_encrypt.bak". It should be noted that there exists several sub-folder off: C:DBBackup like FolderA, FolderB, etc...each one holds a database.

View 5 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Encrypt And Check A Login Password

Jul 6, 2014

I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.

Why the following test does not work and what the correct code should be?

Insert Into [user]
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO

Why does the following produce no rows?

SELECT *
From [user]
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')

View 8 Replies View Related

SQL Server Admin 2014 :: Restore DB With Full Backup And Transactional Log Backup

Aug 3, 2015

Need to restore database,here's the scenario:

Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.

View 8 Replies View Related

SQL Security :: How To Determine What Certificate Is Used To Encrypt A Database In 2014

May 28, 2015

I use a number of certificates on my database server.  I'm trying to verify the correct certificate is being used on the correct database.

View 3 Replies View Related

SQL Server Agent Job Step Fails When Creating A SQL Server Integration Serveices Type Of Step

Mar 12, 2008

Hi

I posted this on the sql server security forums too. Here is the error that i get when i change the type of the step to ssis

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
Additional information:
An exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.(Microsoft SQL Server, Error: 3930)

This error pops up right after i change the type of the step to "SQL Server Intergration Services Package"

I have made the following configurations:

The user group (windows group) that the user belongs has the following roles in msdb :

db_dtsadmin
db_dtsltuser
db_dtsoperator
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

i have made a proxy to sql server agent which has the following subsystems :

"SQL Server Integration Services Provider" the proxy is tied to the same login which has those SQLagent and dts roles in msdb database.

Im using windows authentication and the user that logs into the sql server is in the same group that i have set all of the rights.

Ps. Clearly im missing some role or right somewhere because as soon as i give the group sysadmin role then all the users in that group can create SSIS steps in the agent.

Ps. Ps. I have been living under the impression that i dont have to give sysadmin rights to people that create ssis packages and schedule then with the agent.

View 3 Replies View Related

Sample Files From The Book SQL Server 2005 Reporting Services Step By Step

Jan 18, 2008



Hi All,

Please let me know where can i find the sample files and rs2005sbsDW database in msdn library as i dont have CD provided along with the book.

Please give me the link of the sample files so that i can download it for testing the sample application.

Regards,
Prabhanjana

View 1 Replies View Related

How About Backup With Init + Zip In One Step?

Jul 3, 2003

For a daily backup job,

Is there a way to "Backup Datbase dbXYZ to backup_dbXYZ with init", go, then next statment of master..xp_cmdshell to zip the backup_dbXYZ.bak to backup_dbXYZ.zip file?

Also, this may need to be done via remote server..

thanks
David

View 8 Replies View Related

Backup Log Job Step Failed

Mar 20, 2015

I have a biztalk db server where I have few job steps. The log bkp job steps which the below error but when I go to the location of backup/checking the msdb backupset table I can see the backup file.

[SQLSTATE 01000] (Error 4035) BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.146 MB/sec). [SQLSTATE 01000] (Error 3014)

View 0 Replies View Related

SQL Server Admin 2014 :: Unable To Access Physical Server To Backup Transaction Log

Dec 5, 2014

I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.

Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."

I don't understand how backing up the transaction log could break the "transaction chain"?

View 9 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table?

Aug 9, 2014

how to take backup of indexes for a particular table.

View 1 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table

Aug 9, 2014

How to take backup of indexes for a particular table.

View 5 Replies View Related

SQL Server Admin 2014 :: How To Backup Without Interrupting Users

Sep 24, 2014

Is there a way to backing up SQL DB without having to stop users from connecting to it.

View 5 Replies View Related

SQL Server Admin 2014 :: Append Differential Backup

Feb 10, 2015

Look at this scenario

1- I have created a backup device
2- I have created a maintenance plan full backup and run it - with overwrite option on the backup device
3- I have created a maintenance plan differential backup and run it with append option on the same file of backup device
4- I have created a maintenance plan log backup with append option on the same backup device

When I made restore database from the backup device i found three files the full and differential and log backup

5- I ran again the differential backup maintenance plan ( suppose to be ran everyday night )

when i made restore database I found only two backups the full and the last differential !!!

what I want to do is to take a full backup every week , append differential backup everyday and append log backup every hour

when I ran the last differential backup it erased the first differential and log backups, why is this happening and how to apply this scenario and keep the all differential backups on the same backup device .

View 4 Replies View Related

SQL Server Admin 2014 :: Backup Files Not Created

May 10, 2015

I run the following:

EXECUTE dbo.DatabaseBackup
@Databases = 'F1SB',
@Directory = 'F:SqlBackup2014',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',

[code]...

I cannot see the file created in the directory. The account under which sql server the agen job run have full privileges on it and is sysadmin.Then i run the Command in ssms

BACKUP DATABASE [F1SB] TO DISK = N'F:SqlBackup2014<server>F1SBFULLIGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])

and I get this error message:

Msg 3013, Level 16, State 1, Line 13
BACKUP DATABASE is terminating abnormally.

View 9 Replies View Related

SQL Server Admin 2014 :: Backup Script To Take All The Databases?

Sep 25, 2015

I need backup script to take all the database backups and we have the maintenance plan but our database character size is 98 and when we are taking the backups through maintenance plan while storing the backup history information it is adding the date and timezone information and exceeding the length to 128 so it is not writing the information on MSDB.

So we want to take the backup using the script and it has to create sub folder for each database. Also if any of the database fails it should continue with others.

View 6 Replies View Related

SQL Server Admin 2014 :: Get Last Full Backup Times

Oct 27, 2015

I've got the below and have several variation and still cant seem to find a perfect way to query the server to bring back that last full backup per db. I'm shopwing mutilple records in the backup set db w/ type = 'D'. I look online and type D = Database. Which i assumed it meant full database backup. Apparently not. Try running the below on one of your full databases. Then check to see if the date is actually the last backup date.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,

[Code] ....

View 9 Replies View Related

SQL Server Admin 2014 :: Restore Encrypted Server Backup Via GUI

Feb 10, 2015

I have an issue to restore an encrypted backup via GUI.I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014. The message error is: No backupset selected to be restored.I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. I have the following versions:

On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

View 9 Replies View Related

SQL Server 2014 :: Call For DB Backup For A Database On Another (linked) Server

Jul 8, 2015

I have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I just can't figure out the syntax for a database backup where the database is on another server.

Trying something like [ServerName].[DatabaseName] does not work...

View 9 Replies View Related

SQL Server Admin 2014 :: Can Backup All Stored Procedures In A Database

Oct 31, 2014

is there a way to backup all stored procedures in a database?

View 4 Replies View Related

SQL Server Admin 2014 :: AlwaysON Secondary Full Backup?

Nov 12, 2014

Recently I have faced one DBA interview, below is the question they asked me.

" Does AlwaysON secondary replica support Full backup, if it is not why"?

I know AlwaysON secondary replicas support only copy_only and tlog backups, why they wont support full backup?

View 9 Replies View Related

SQL Server Admin 2014 :: Full Backup While Data Is Modifying

Nov 13, 2014

If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?

View 2 Replies View Related

SQL Server Admin 2014 :: Restoring A Single Table From Backup

Dec 12, 2014

With all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).

View 6 Replies View Related

SQL Server Admin 2014 :: Transactional Log Backup With Frequency Of 1 Month

Jan 6, 2015

I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .

And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .

So should I need to take any other precautions or concerns with this approach.?

View 9 Replies View Related

SQL Server Admin 2014 :: Backup Jobs On Availability Groups?

Feb 11, 2015

we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.

we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .

View 3 Replies View Related

SQL Server Admin 2014 :: What Happens If Shrink Database With Truncate Only And Take A Backup

Apr 30, 2015

I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?

View 9 Replies View Related

Schedule Database Backup And Delete For Server Express 2014

Jul 30, 2015

I would like to auto daily back up for all databases on a SQL Server Express 2014, and keep back up of last 30 days.

View 3 Replies View Related

Configuring SQL Server Express In SqL Server 2005 Pro To Support MSVisual C# 2005 Step By Step

Feb 4, 2007

Visual C# 2005 Step by Step John Sharp

Intel D975XBX 930 2GB ram HD 300GB - MatroxVD
Windows XP Sp2 + updates
Visual Studio 2005 Professional edition
SQL Sever 2005 Standard Edition

20070203
Install Practice Files at D:Program FilesMicrosoft PressVisual C Sharp Step by Step.

Configuring SQL Sever Express Edition
Hostname - xxxxxxxxx..

At sqlcmd s xxxxx...SQLExpress E got - Pipes error.

Open Microsoft SQL Sever 2005 - Configuration Tools SQL Configuration Manager select Protcols for SQLEXPRESS select Named Pipes enable close.

At sqlcmd s xxxxx..SQLExpress E get Sqlcmd: : Unknown Option. Enter -? for help.

Chdir C:Documents and SettingsAll UsersStart MenuProgramsMicrosoft Server 2005
Sqlcmd s xxxxxx..SqlExpress E
Hresult 0x2, Lvel 16, State 1
Named Pipes Provider: Couldnot open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

How do I fix this?

Need to be able to to use the Northwind Traders data base

Thanks

View 1 Replies View Related

SQL Server Admin 2014 :: Restore Database Without Backup History In MSDB

Jul 23, 2014

What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?

I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.

What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?

View 6 Replies View Related

SQL Server Admin 2014 :: Comvault Backups Breaking Backup Chain?

Jan 10, 2015

I'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.

View 2 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 Replies View Related

SQL Server 2014 :: Select Statement Returning Too Many Rows For Backup Start Date

Jun 3, 2015

Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.

select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'

[Code] .....

View 8 Replies View Related

SQL Server Admin 2014 :: Cannot Decrypt Encrypted Columns From Database Backup On Local Machine

Jun 29, 2015

I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.

USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]

I tried doing above still no luck.

View 3 Replies View Related

Step By Step For Upgrading SQL Server 2000 Cluster To SQL Server 2005 Cluster

May 15, 2008



Friends -

Could any one of you provide steps for upgrading SQL Server 2000 cluster to SQL server 2005 cluster.

My environment is Windows 2003 server.

Appreciate your support.

Cheers
VSH

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved