February 19, 2012 at 11:38 pm
Hello.
Disclaimer: sorry for my English.
We use couple of sql 2008 server. Backup logic - simple recovery model, full backup of user databases every night.
Task Scheduler launch launch script with connection to sqlcmd:
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S %SERVER_NAME%\%SQL_NAME% -E -i \\<server name>\backup$\_backupScripts\sql\dobackup.sql -o \\<server name>\backup$\_backupScripts\sql\logs\output_%SERVER_NAME%_%SQL_NAME%.log
dosqlbackup.sql:
//here some functions to get @arcname, nothing interesting
Backup Database @dbname To Disk = @arcname;
But sometimes something going wrong.
In time near backup I see only "Recovery completed ... This is information message only..."
I couldn't find message from log file, because it's rewrite with successful backup next night.
I have questions:
where find another log information why backup was unsuccessful?
could sqlcmd append information to log file, not rewrite, or better in script make different file name for backup log?
i doesn't found suitable BPA (I know, I'm noob) for backup, and I don't know it's good idea or not - move database to single user mode?
February 20, 2012 at 2:57 am
You can't use a parameter for the database name in a BACKUP statement. You need to use dynamic SQL instead, or find some other way of building your SQL command.
John
February 20, 2012 at 2:59 am
sumitagarwal781 (2/20/2012)
You can find backup failure errors in SQL server error log as well.
There is no errors in SQL log 🙁
February 20, 2012 at 3:05 am
John Mitchell-245523 (2/20/2012)
You can't use a parameter for the database name in a BACKUP statement. You need to use dynamic SQL instead, or find some other way of building your SQL command.John
But backup only sometimes doesn't work, almost all time it work's 🙂
And of course i didn't post all script.
Complete script:
Declare dbnames_cursor Cursor For
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
Open dbnames_cursor;
DECLARE @dbname varchar(150);
DECLARE @arcname varchar(1024);
Fetch next From dbnames_cursor into @dbname;
While @@Fetch_Status=0 Begin
SELECT @arcname = 'E:\BackupDB\Newbackup\'+
CAST (DATEPART (yyyy, GETDATE()) AS VARCHAR(4))+
right ('0'+CAST (DATEPART (mm, GETDATE()) AS VARCHAR(2)), 2)+
right ('0'+CAST(DATEPART (dd, GETDATE()) AS VARCHAR(2)), 2)+'_'+
right ('0'+CAST(DATEPART (hh, GETDATE()) AS VARCHAR(2)), 2)+
right ('0'+CAST(DATEPART (mi, GETDATE()) AS VARCHAR(2)), 2)+
'_'+@dbname+'.bak';
Backup Database @dbname To Disk = @arcname;
Fetch next From dbnames_cursor into @dbname;
End
Close dbnames_cursor;
Deallocate dbnames_cursor;
GO
February 20, 2012 at 3:12 am
I've just tried your script, and it looks like I was wrong about not being able to use a variable in your BACKUP statement, so ignore that comment. I don't know why you get the message you do - that looks more like something you'd get when you do a restore. How many backups fail when this happens - all of them, or just one? Is it the same one every time?
John
February 20, 2012 at 3:28 am
John Mitchell-245523 (2/20/2012)
I've just tried your script, and it looks like I was wrong about not being able to use a variable in your BACKUP statement, so ignore that comment. I don't know why you get the message you do - that looks more like something you'd get when you do a restore. How many backups fail when this happens - all of them, or just one? Is it the same one every time?John
This happens on one server, which host one database. On other servers - no errors.
It happens time to time. Today is OK, yesterday fail, but about week before - no errors.
And no messages in log.
February 20, 2012 at 3:53 am
It's worth having a look in the default trace next time it fails to see whether the activity is recorded in there. Another thing that's worth doing is piping the results of sqlcmd to a different text file each time so that you can examine the (error) messages.
John
February 20, 2012 at 5:31 am
matrenin (2/20/2012)
John Mitchell-245523 (2/20/2012)
I've just tried your script, and it looks like I was wrong about not being able to use a variable in your BACKUP statement, so ignore that comment. I don't know why you get the message you do - that looks more like something you'd get when you do a restore. How many backups fail when this happens - all of them, or just one? Is it the same one every time?John
This happens on one server, which host one database. On other servers - no errors.
It happens time to time. Today is OK, yesterday fail, but about week before - no errors.
And no messages in log.
Add a TRY...CATCH block in your code to send the error message back to you (through the mail or you may want to simply print it).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply