MSSQL Backup
Hi Experts,
I have the following line in a batch file that it supposed to backup my MSSQL databases
the MSSQL_Backup.sql is
The MSSQL_Results.txt file is supposed to be as follows, with corresponding .BAK files for each database
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.
I have the following line in a batch file that it supposed to backup my MSSQL databases
sqlcmd -U sa -P xxx-S OPTIPLEX\SQLEXPRESS -i "D:\Scripts\MSSQL_Backup.sql" > "C:\Users\Nataliia & aleks\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\MSSQL_Results.txt"the MSSQL_Backup.sql is
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_cursorThe MSSQL_Results.txt file is supposed to be as follows, with corresponding .BAK files for each database
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).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.
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\L
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\L
Hi,
do you prefer using script?
Otherwise i suggest you to use http://www.sqlbackupmaster.com/ - 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.
do you prefer using script?
Otherwise i suggest you to use http://www.sqlbackupmaster.com/ - 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.
ASKER
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.
When I run the script manually, it always works. What can I do? Can I add a line to wake the SQL server?
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.When I run the script manually, it always works. What can I do? Can I add a line to wake the SQL server?
> 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.
> Can I add a line to wake the SQL server?
Certainly, you can add a query to request anything ("Select @@version" for example), and WAITFOR a couple seconds before starting the rest of your script.
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.
> Can I add a line to wake the SQL server?
Certainly, you can add a query to request anything ("Select @@version" for example), and WAITFOR a couple seconds before starting the rest of your script.
ASKER
What lines should I add, and how can I use waitfor in my situation?
> 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:
Which would give your server a few seconds to 'wake up' before the rest of your script ran. That all assumes that the problem is that the server isn't 'waking up' fast enough. Or if you want to give it a full minute, change '00:00:05' to '00:01:00'.
> 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. 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. 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. 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. 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. When everything is working... you don't care about this output. If this ever returns no lines, you do care.
Again, if it were me, I'd replace lines 17-18 with something like:
Should run just as fast... but will hopefully trap the only parts I can imagine might possibly be giving you trouble.
If you believed the problem was that the server was asleep when you started backups, you could add the following lines before line 1:
select @@version
go
waitfor delay '00:00:05'
goWhich would give your server a few seconds to 'wake up' before the rest of your script ran. That all assumes that the problem is that the server isn't 'waking up' fast enough. Or if you want to give it a full minute, change '00:00:05' to '00:01:00'.
> 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. 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. 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. 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. 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. When everything is working... you don't care about this output. If this ever returns no lines, you do care.
Again, if it were me, I'd replace lines 17-18 with something like:
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 <> 0
SELECT 'Initial @@FETCH_STATUS unexpected',@@FETCH_STATUSShould run just as fast... but will hopefully trap the only parts I can imagine might possibly be giving you trouble.
ASKER
Rich- After adding your lines, my scripts now is:
Where my output is
...and still nothing backed up.
--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)
--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 <> 0
SELECT 'Initial @@FETCH_STATUS unexpected',@@FETCH_STATUS
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_cursorWhere my output is
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 <X64> (Build 7601: Service Pack 1) (WOW64)
(1 rows affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16916 16 1 NULL 15 A cursor with the name 'db_cursor' does not exist.
(1 rows affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
16916 16 1 NULL 27 A cursor with the name 'db_cursor' does not exist.
(1 rows affected)
--------------------------------- -----------
Initial @@FETCH_STATUS unexpected -1
(1 rows affected)
name
--------------------------------------------------------------------------------------------------------------------------------
CSS_Carts
CSS_Reservations
CSSCharters_local
MAS_BHE
MYLA
Msg 16916, Level 16, State 1, Server OPTIPLEX\SQLEXPRESS, Line 43
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Server OPTIPLEX\SQLEXPRESS, Line 48
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Server OPTIPLEX\SQLEXPRESS, Line 58
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Server OPTIPLEX\SQLEXPRESS, Line 59
A cursor with the name 'db_cursor' does not exist.
(5 rows affected)...and still nothing backed up.
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. 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. (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.)
The bad news is there isn't a cursor created anymore. 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. (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.)
ASKER
Now, my script is
and I get a syntax error:
--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 <> 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_cursorand I get a syntax error:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 <X64> (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'.
lines 18 to 22 are currently:
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
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databasesASKER
Do I use li.5-8, instead of 1-3
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you for your continued help!
Have you already checks the SQL ERRORLOG file? 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. Write out the output of the cursor to confirm the cursor isn't ending up empty for some reason. Use a Try around the open cursor and the first fetch? And/Or output something if the initial @@Fetch_Status isn't zero...