May 10, 2015 at 3:57 pm
Hi All,
I run the following:
EXECUTE dbo.DatabaseBackup
@databases = 'F1SB',
@Directory = 'F:\SqlBackup2014',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'serverCertificate'
and the output is next:
Date and time: 2015-05-10 21:44:55
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'F:\SqlBackup2014\<server>\F1SB\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2015-05-10 21:44:55
Date and time: 2015-05-10 21:44:55
Command: BACKUP DATABASE [F1SB] TO DISK = N'F:\SqlBackup2014\<server>\F1SB\FULL\IGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])
Outcome: Succeeded
Duration: 00:00:10
Date and time: 2015-05-10 21:45:05
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>\F1SB\FULL\IGS-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.
Any help on this...
Igor Micev,My blog: www.igormicev.com
May 11, 2015 at 2:04 am
Durations suggest that the other backup is failing same as the native backup. It sure looks like a permissions issue. Running it as yourself is going to be different than running it as Agent. I would double check the permissions. That's the most common problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2015 at 2:39 am
Grant Fritchey (5/11/2015)
Durations suggest that the other backup is failing same as the native backup. It sure looks like a permissions issue. Running it as yourself is going to be different than running it as Agent. I would double check the permissions. That's the most common problem.
Grant, it's not a permission issue. It's a windows account with sysadmin privileges. The account which is same for sql server and the agent, has full privileges over all folders (data, log and backup). I've tried to backup directly from ssms. The first backup with option WITH FORMAT passed, and on the second attempt it fails.
Event views error:
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">3041</EventID>
<Level>2</Level>
<Task>6</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2015-05-11T06:16:12.000000000Z" />
<EventRecordID>714840</EventRecordID>
<Channel>Application</Channel>
<Computer>server</Computer>
<Security UserID="S-1-5-21-1519272988-1155903890-2896701036-500" />
</System>
- <EventData>
<Data>BACKUP DATABASE F1SB WITH DIFFERENTIAL</Data>
<Binary>E10B000010000000090000004900470053002D004400420030003100000000000000</Binary>
</EventData>
</Event>
Igor Micev,My blog: www.igormicev.com
May 11, 2015 at 3:30 am
Since the only error you have found is "Terminated Abnormally" it's really hard to say. As I said, the most common error there is permissions, but I believe you. Second most common is space. After that it's syntax, which looks OK. After that... not sure without details.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2015 at 5:02 am
Igor Micev (5/11/2015)
Grant, it's not a permission issue. It's a windows account with sysadmin privileges.
It's still possible to revoke permission to a windows admin, the advantage that being an admin offers is that you can take ownership and subsequently grant yourself explicit ACLs.
Check the permissions on the security tab for the folder and also check the security tab for individual files too.
You must also supply a valid medianame when performing encrypted backups in sql server 2014. Please see my article at this link[/url].
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2015 at 5:51 am
Perry Whittle (5/11/2015)
Igor Micev (5/11/2015)
Grant, it's not a permission issue. It's a windows account with sysadmin privileges.It's still possible to revoke permission to a windows admin, the advantage that being an admin offers is that you can take ownership and subsequently grant yourself explicit ACLs.
Check the permissions on the security tab for the folder and also check the security tab for individual files too.
You must also supply a valid medianame when performing encrypted backups in sql server 2014. Please see my article at this link[/url].
I've seen your article before. It's ok.
Permissions are double checked and they are ok. I found the following post http://dba.stackexchange.com/questions/77322/backups-failing-for-all-databases in which the poster says after some hotfixes installed backups started to work. Probably I'll have to see that with the sys-admins.
Igor Micev,My blog: www.igormicev.com
May 11, 2015 at 10:36 am
This is the read-in dump file with WinDbg, but I cannot get some useful info as I'm not good enough for system core. Maybe someone could say something?
Igor Micev,My blog: www.igormicev.com
May 11, 2015 at 10:43 am
have you re run the backup command specifying a medianame?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2015 at 10:51 am
Perry Whittle (5/11/2015)
have you re run the backup command specifying a medianame?
Yes, actually all the time I'm specifying it. I even change the drives where I'm trying to backup on. Not possible so far.
Igor Micev,My blog: www.igormicev.com
May 12, 2015 at 1:17 am
There is no medianame specified in the query below!
Igor Micev (5/10/2015)
Then i run the Command in ssmsBACKUP DATABASE [F1SB] TO DISK = N'F:\SqlBackup2014\<server>\F1SB\FULL\IGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate]).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 12, 2015 at 1:25 am
Perry Whittle (5/12/2015)
There is no medianame specified in the query below!Igor Micev (5/10/2015)
Then i run the Command in ssmsBACKUP DATABASE [F1SB] TO DISK = N'F:\SqlBackup2014\<server>\F1SB\FULL\IGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate]).
It is taken from the log text. I double checked it right now, and it's the same with medianame specified.
Actually, specifying ENCRYPTION is not possible if previously medianame wasn't.
Igor Micev,My blog: www.igormicev.com
May 12, 2015 at 1:46 am
Digging ... it's possible the matter that I have added some new files across the databases without restarting the instance. Reading one KB says that it had been a bug in sql 2008R2, so now I'm wondering if that could be the reason.
Igor Micev,My blog: www.igormicev.com
May 14, 2015 at 12:20 pm
Hi All,
Just to share with the sql folks that the problem was resolved by upgrading the instance with CU1 and CU2. Now there is no issue with the backup process.
Thanks you for your replies.
Igor Micev,My blog: www.igormicev.com
May 16, 2015 at 9:59 pm
Igor Micev (5/14/2015)
Hi All,Just to share with the sql folks that the problem was resolved by upgrading the instance with CU1 and CU2. Now there is no issue with the backup process.
Thanks you for your replies.
Yowch! I hate that. Not the first time I've seen it, won't be the last, and I always hate it when it happens.
Thanks for the feedback, Igor.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2015 at 12:46 am
Jeff Moden (5/16/2015)
Igor Micev (5/14/2015)
Hi All,Just to share with the sql folks that the problem was resolved by upgrading the instance with CU1 and CU2. Now there is no issue with the backup process.
Thanks you for your replies.
Yowch! I hate that. Not the first time I've seen it, won't be the last, and I always hate it when it happens.
Thanks for the feedback, Igor.
Additionally, if there are certificates on the instance and you're using those backups on other instances, then you'll have to re-restore the certificates on the others instances.
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply