Error when trying to backup data from SQL Server

Question

I'm getting a disturbing and continuous error when I'm trying to do a backup in my database. I have done the research on this subject but I could not get it fixed. Please anyone with the knowledge, suggest the best way of solving it. Here is the error message below: I tried changing .dll files, no chance!!!

Which client are you trying to take a backup from? I guess its not Mangement studio? Try this link: https://stackoverflow.com/questions/17681432/how-can-i-enable-assembly-binding-logging — SqlKindaGuy, Nov 21 '17 at 13:19
If I may Explain in Details, : " The system was build in MS access then later on was upgrade to SQL 2008. from 2008 to 2014 the Database backup was still working till it's crashed. We used 2016 to restore it and it was working till recently. just few days ago, we can not make backup as usual. it's just starting giving us errors" — McElie, Nov 21 '17 at 13:27
This is a tooling error; the server is most likely fine. You can always do a `BACKUP DATABASE` with `sqlcmd`, if the command line doesn't scare you. Otherwise, try repairing the installation of the SQL Server tools, or try another machine to issue the command from. (If you're logging into SQL Server itself to do the backups, stop doing that. Unless the server is a local test server, of course.) — Jeroen Mostert, Nov 21 '17 at 13:30
@JeroenMostert, Thanks for coming, How would I do the backup by using SQL Command? — McElie, Nov 21 '17 at 13:32
`BACKUP DATABASE testdb1 TO DISK = 'c:\testdb1.bak' WITH FORMAT, MEDIANAME = 'MyBackups', NAME = 'Full backup of my database' GO` — SqlKindaGuy, Nov 21 '17 at 13:46
@plaidDK, I have tried it, but I'm getting an error: "Incorrect syntax near 'GO'." — McElie, Nov 21 '17 at 13:55
@plaidDK, Please provide me with an answer and no a comment of How can I Fixed the above error if I have to use your method for a daily basis? Should you not have another approach to solve the above problem? — McElie, Nov 21 '17 at 14:01
@McElie I am not familiary with your problem above. But if you use tsql command to do a backup you can just schedule it in a SQL Job agent to run daily. — SqlKindaGuy, Nov 21 '17 at 14:04

score 1 · Accepted Answer · answered Nov 21 '17 at 14:14

You can do an automatic script like this:

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 2. Setting the backup path

SET @path = 'D:\Backup\'  

 -- 3. Getting the time values

SELECT @time = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- system databases are excluded

--5. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

-- 6. Defining the filename format

       SET @fileName = @path + @name + '_' + @year + @month + @day + @hour + @minute + @second + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  


       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor

Then put this in a SQL Job step and schedule it daily like this:

Create new job under SQL Job Agent and add new step

Add an schedule

Job Created with an schedule

Result

Thank a lot, I have consider your ideas as the second approach. the First will be as we successfully backup the database, this means that the application is the main problem and need to be revised. On the other hand, I will use your script to create the daily backup job to a specific time. — McElie, Nov 21 '17 at 14:24
@McElie Hope you work it out - Remember to upvote or mark answer as correct if it can benefit you, so other can use same solution. Thanks. — SqlKindaGuy, Nov 21 '17 at 14:26
@McElie hmm - Its working fine here. Sure you got everything copy pasted? — SqlKindaGuy, Nov 21 '17 at 14:36

Error when trying to backup data from SQL Server

1 Answers1