<div>
I assume you're scheduling the command with Task Scheduler. 
Have you already checks the SQL ERRORLOG file? &nbsp;I'm assuming nothing is showing up there. 
 
If it were me, I'd add a couple lines to add troubleshooting lines to the output. &nbsp;Write out the output of the cursor to confirm the cursor isn't ending up empty for some reason. &nbsp;Use a <a href="https://msdn.microsoft.com/en-us/library/ms175976.aspx" rel="ugc">Try</a> around the open cursor and the first fetch? &nbsp;And/Or output something if the initial @@Fetch_Status isn't zero...
</div>

<div>
Have you tried the Windows Event logs? eventmgr.msc 
 
Event logs provide historical information that can help you track down problems with SQL Server. SQL Server writes events to the SQL Server event logs, the SQL Server Agent event logs, and the Windows application log. You can use all three logs to track messages related to SQL Server. 
. 
You can view the SQL Server event logs in SQL Server Management Studio or through a text editor. In SQL Server Management Studio. 
 
Just because you have Express doesn't mean you cannot install the MGMT Studio. 
 
Or 
 
Start a text editor, such as Notepad, and then use its Open dialog box to access the SQL Server Log folder, normally located in MSSQLSERVER\MSSQL\LOG or MSSQL.InstanceName\MSSQL\LOG
</div>

<div>
Hi, 
 
do you prefer using script? 
Otherwise i suggest you to use <a href="http://www.sqlbackupmaster.com/" rel="ugc">http://www.sqlbackupmaster.com/</a>&nbsp;- it is great freeware sql backup utility. You can set up scheduled job and you will receive email notification with detailed log on successful or failed backup job.
</div>

<div>
I have the backups scheduled daily for 20:45 and the only thing that I can see in the SQL logs is that I can see is at 20:46 that the SQL server was awakened after being idle. 
 

<pre><code class="code-1 nolinks" id="code-20-41494328-1">03/04/2016 20:46:22,Server,Unknown,Server resumed execution after being idle 23440 seconds: user activity awakened the server. This is an informational message only. No user action is required.
</code></pre>
 
When I run the script manually, it always works. What can I do? Can I add a line to wake the SQL server?
</div>

<div>
&gt; What can I do? 
I'd still add the lines to the script to output a little more to your output file to help troubleshoot the unlikely event that it does have problems... or the Try/Catch to trap a problem and if it is that the SQL Server isn't responding in a timely manner, to perhaps attempt the first query a second time. 
 
&gt;&nbsp;Can I add a line to wake the SQL server? 
Certainly, you can add a query to request anything (&quot;Select @@version&quot; for example), and <a href="https://msdn.microsoft.com/en-us/library/ms187331.aspx" rel="ugc">WAITFOR </a>a couple seconds before starting the rest of your script.
</div>

<div>
What lines should I add, and how can I use waitfor in my situation?
</div>

<div>
&gt; how can I use waitfor in my situation? 
 
If you believed the problem was that the server was asleep when you started backups, you could add the following lines before line 1: 
 

<pre><code class="code-1 nolinks" id="code-20-41494418-1">select @@version
go
waitfor delay '00:00:05'
go
</code></pre>
 
Which would give your server a few seconds to 'wake up' before the rest of your script ran. &nbsp;That all assumes that the problem is that the server isn't 'waking up' fast enough. &nbsp;Or if you want to give it a full minute, change '00:00:05' to '00:01:00'. 
 
&gt;&nbsp;What lines should I add[?] 
If I were trying to figure out what's going on... there are a few areas which I believe would result in the script starting, and providing no output. 
1. The server is asleep and doesn't respond in a timely manner before the script times out. &nbsp;I tend to give this a relatively low probability, but adding the delay above, SHOULD allow the server to wake up. 
2. For whatever reason, the script which is populating the cursor is blank. &nbsp;Can't think of any reason for this to be true a couple days a week, but it would be nice to eliminate that possibility. 
3. The cursor fails to open. &nbsp;I think you'd get error output in your output file, so I don't _think_ this is happening. 
4. The very first fetch statement from the cursor has a return status other than 0. &nbsp;That'd be consistant with what your are describing. 
 
So again, if it were me, I'd repeat lines 13-15 right before line 12, so the hopefully the output of that query ends up in the file output. &nbsp;When everything is working... you don't care about this output. &nbsp;If this ever returns no lines, you do care. 
 
Again, if it were me, I'd replace lines 17-18 with something like: 

<pre><code class="code-10 nolinks" id="code-20-41494418-2">BEGIN TRY
 OPEN db_cursor 
END TRY
BEGIN CATCH
 SELECT 
 ERROR_NUMBER() AS ErrorNumber
 ,ERROR_SEVERITY() AS ErrorSeverity
 ,ERROR_STATE() AS ErrorState
 ,ERROR_PROCEDURE() AS ErrorProcedure
 ,ERROR_LINE() AS ErrorLine
 ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
BEGIN TRY
 FETCH NEXT FROM db_cursor INTO @name 
END TRY
BEGIN CATCH
 SELECT 
 ERROR_NUMBER() AS ErrorNumber
 ,ERROR_SEVERITY() AS ErrorSeverity
 ,ERROR_STATE() AS ErrorState
 ,ERROR_PROCEDURE() AS ErrorProcedure
 ,ERROR_LINE() AS ErrorLine
 ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

IF @@FETCH_STATUS &lt;&gt; 0
 SELECT 'Initial @@FETCH_STATUS unexpected',@@FETCH_STATUS
</code></pre>
 
Should run just as fast... but will hopefully trap the only parts I can imagine might possibly be giving you trouble.
</div>

<div>
The good news is that the error routine caught the errors and reported on them. 
The bad news is there isn't a cursor created anymore. &nbsp;From your current script... 
1. Uncomment line 18. 
2. Copy lines 48-50 to just after line 18. 
3. Move those same lines 48-50 to before line 18. 
4. Remove what is currently in 52 and 53. &nbsp; (It duplicates 21 and 33.) 
(Again, line numbers are based on what I see in that existing script... the line numbers will change as you make changes.)
</div>

<div>
Now, my script is 

<pre><code class="code-10 nolinks" id="code-20-41501756-1">--Wakeup server
select @@version
go
waitfor delay '00:00:05'
go

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'D:\DBs_Backups\MSSQL\' 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

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

BEGIN TRY
 OPEN db_cursor 
END TRY
BEGIN CATCH
 SELECT 
 ERROR_NUMBER() AS ErrorNumber
 ,ERROR_SEVERITY() AS ErrorSeverity
 ,ERROR_STATE() AS ErrorState
 ,ERROR_PROCEDURE() AS ErrorProcedure
 ,ERROR_LINE() AS ErrorLine
 ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
BEGIN TRY
 FETCH NEXT FROM db_cursor INTO @name 
END TRY
BEGIN CATCH
 SELECT 
 ERROR_NUMBER() AS ErrorNumber
 ,ERROR_SEVERITY() AS ErrorSeverity
 ,ERROR_STATE() AS ErrorState
 ,ERROR_PROCEDURE() AS ErrorProcedure
 ,ERROR_LINE() AS ErrorLine
 ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

IF @@FETCH_STATUS &lt;&gt; 0
 SELECT 'Initial @@FETCH_STATUS unexpected',@@FETCH_STATUS

 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
 BACKUP DATABASE @name TO DISK = @fileName 
 
 FETCH NEXT FROM db_cursor INTO @name 
END 
 
CLOSE db_cursor 
DEALLOCATE db_cursor
</code></pre>
 
and I get a syntax error: 

<pre><code class="code-10 nolinks" id="code-20-41501756-2"> 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
	Apr 22 2011 11:57:00 
	Copyright (c) Microsoft Corporation
	Express Edition with Advanced Services on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (WOW64)
 

(1 rows affected)
Msg 156, Level 15, State 1, Server OPTIPLEX\SQLEXPRESS, Line 19
Incorrect syntax near the keyword 'BEGIN'.
</code></pre>
</div>

<div>
lines 18 to 22 are currently: 

<pre><code class="code-1 nolinks" id="code-20-41501809-1">SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
 
DECLARE db_cursor CURSOR FOR 
</code></pre>
change those lines to: 

<pre><code class="code-1 nolinks" id="code-20-41501809-2">SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
</code></pre>
</div>

<div>
Do I use li.5-8, instead of 1-3
</div>

<div>
Thank you for your continued help!
</div>

<div>
<div class="content wysiwyg-content">
Hi Experts, 
 
I have the following line in a batch file that it supposed to backup my MSSQL databases 

<pre><code class="code-1 nolinks" id="code-10-28930962-1">sqlcmd -U sa -P xxx-S OPTIPLEX\SQLEXPRESS -i &quot;D:\Scripts\MSSQL_Backup.sql&quot; &gt; &quot;C:\Users\Nataliia &amp; aleks\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\MSSQL_Results.txt&quot;
</code></pre>
 
the MSSQL_Backup.sql is 

<pre><code class="code-10 nolinks" id="code-10-28930962-2">DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'D:\DBs_Backups\MSSQL\' 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
 BACKUP DATABASE @name TO DISK = @fileName 
 
 FETCH NEXT FROM db_cursor INTO @name 
END 
 
CLOSE db_cursor 
DEALLOCATE db_cursor
</code></pre>
 
The MSSQL_Results.txt file is supposed to be as follows, with corresponding .BAK files for each database 

<pre><code class="code-10 nolinks" id="code-10-28930962-3">Processed 13024 pages for database 'CSS_Reservations', file 'CSS_Reservations_dat' on file 1.
Processed 1 pages for database 'CSS_Reservations', file 'CSS_Reservations_log' on file 1.
BACKUP DATABASE successfully processed 13025 pages in 1.263 seconds (80.568 MB/sec).
Processed 192 pages for database 'CSSCharters_local', file 'CSSTChartes_local' on file 1.
Processed 1 pages for database 'CSSCharters_local', file 'CSSTChartes_local_log' on file 1.
BACKUP DATABASE successfully processed 193 pages in 0.218 seconds (6.916 MB/sec).
Processed 256 pages for database 'MYLA', file 'MYLA' on file 1.
Processed 1 pages for database 'MYLA', file 'MYLA_log' on file 1.
BACKUP DATABASE successfully processed 257 pages in 0.289 seconds (6.947 MB/sec).
Processed 4424 pages for database 'CSS_Carts', file 'CSSCarts_LIVE_Data' on file 1.
Processed 1 pages for database 'CSS_Carts', file 'CSSCarts_LIVE_Log' on file 1.
BACKUP DATABASE successfully processed 4425 pages in 0.647 seconds (53.431 MB/sec).
Processed 6944 pages for database 'MAS_BHE', file 'MAS_BHE' on file 1.
Processed 1 pages for database 'MAS_BHE', file 'MAS_BHE_log' on file 1.
BACKUP DATABASE successfully processed 6945 pages in 0.992 seconds (54.695 MB/sec).
</code></pre>
 
I have running this daily, and for the past 1 year it has been flawless, but lately at least 3 times a week the results file is empty, and no .bak files, and no errors. How can I trace whats is going on? 
 
I am running this on Windows 7 Pro and SQL server 2008 R2 
 
Any help will be greatly appreciated.
</div>
</div>

Hi Experts,

I have the following line in a batch file that it supposed to backup my MSSQL databases
(CODE)

the MSSQL_Backup.sql is
(CODE)

The MSSQL_Results.txt file is supposed to be as follows, with corresponding .BAK files for each database
(CODE)

I have running this daily, and for the past 1 year it has been flawless, but lately at least 3 times a week the results file is empty, and no .bak files, and no errors. How can I trace whats is going on?

I am running this on Windows 7 Pro and SQL server 2008 R2

Any help will be greatly appreciated.

MSSQL Backup

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Windows 7 is an operating system from Microsoft. Features include multi-touch support, a redesigned Windows Shell with a new taskbar, referred to as the Superbar, a home networking system called HomeGroup, and performance improvements.