Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Should I backup the transaction log? Expand / Collapse
Author
Message
Posted Monday, March 10, 2014 1:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 287, Visits: 1,154
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698


sreekanth, I went to the link you provided. I've gotten the SQL script that's on the page, put it into its own .sql file and ran it in my SQL Express, putting the new SP into the master database. Then I created a .bat file, appropriate for my situation, and scheduled it into my Task Scheduler. I let it run at noon, my time. I then checked the status of the run; it ran fine. However, when I went to look to see if the .bak file was where I specified it should be, it wasn't. Is it because my recovery model is specified as Simple?


Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.

sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"
Post #1549454
Posted Tuesday, March 11, 2014 7:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
Keith, in this situation I am OK with it. You see our application is written so that it saves the data in two places, at the same time. It saves it to the SQL Express database and then saves it to a backend SQL Server database. The backend database gets backed up much more frequently, so restoring from backup is no more than an hour off.

BTW, as an aside, we designed it to work like this because of how the previous version of this application use to be used. It had to be able to run on a laptop, saving the data all on the laptop. It might save several days worth of data to the laptop before it was brought back in house to have the data uploaded to the backend database. At this point the new application isn't going to be used that way. The infrastructure is in place to do it that way, but not fully implemented. We won't fully implement it, unless out users go back into the field to do work with the app.


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1549751
Posted Tuesday, March 11, 2014 7:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
sreekanth bandarla (3/10/2014)
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698


sreekanth, I went to the link you provided. I've gotten the SQL script that's on the page, put it into its own .sql file and ran it in my SQL Express, putting the new SP into the master database. Then I created a .bat file, appropriate for my situation, and scheduled it into my Task Scheduler. I let it run at noon, my time. I then checked the status of the run; it ran fine. However, when I went to look to see if the .bak file was where I specified it should be, it wasn't. Is it because my recovery model is specified as Simple?


Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.

sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"


Here's what I've got in my batch file:

sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"

In comparing your code and mine, the thing that looks different is you've specified "master..sp_BackupDatabases" whereas I've specified just "sp_BackupDatabases". Should I qualify that it start in the master DB?


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1549757
Posted Tuesday, March 11, 2014 8:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 287, Visits: 1,154
Rod at work (3/11/2014)
sreekanth bandarla (3/10/2014)
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...
http://support.microsoft.com/kb/2019698


sreekanth, I went to the link you provided. I've gotten the SQL script that's on the page, put it into its own .sql file and ran it in my SQL Express, putting the new SP into the master database. Then I created a .bat file, appropriate for my situation, and scheduled it into my Task Scheduler. I let it run at noon, my time. I then checked the status of the run; it ran fine. However, when I went to look to see if the .bak file was where I specified it should be, it wasn't. Is it because my recovery model is specified as Simple?


Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.

sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"


Here's what I've got in my batch file:

sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"

In comparing your code and mine, the thing that looks different is you've specified "master..sp_BackupDatabases" whereas I've specified just "sp_BackupDatabases". Should I qualify that it start in the master DB?


Since I've deployed in master database on my test instance, I am using "master..proc". That's just my practice. Yes, You can give it a try. BTW, did you copied and pasted the sqlcmd command from the KB article or did you typed the sqlcmd command manually in a new file? The reason why am asking this is, There is a hidden char which is being copied over if we simply copy and paste the code from article!!! (Error Message: Sqlcmd: 'ûE': Unexpected argument). It's not visible in Notepad(but any hexeditor should expose it). So.....make sure you type this manually in a new text file and change the extension to cmd and give it a shot.
Post #1549768
Posted Tuesday, March 11, 2014 8:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 287, Visits: 1,154
Also, you don't need to specify -E if you are using -U and -p switches and they are case sensitive.
Post #1549772
Posted Tuesday, March 11, 2014 9:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
I copied the text from the KB article and pasted it into my batch file.

Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1549799
Posted Tuesday, March 11, 2014 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 287, Visits: 1,154
I think, That's where you are having issues with. Type it manually and let me know how it goes...
syntax:
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'" -o "C:\SQLBackups\Backupjob_Log.txt"

Also, double check your Instance Name. Looks like you are using "EXPRESS" instead of "SQLEXPRESS" in your batch file.

Post #1549810
Posted Tuesday, March 11, 2014 1:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
sreekanth bandarla (3/11/2014)
I think, That's where you are having issues with. Type it manually and let me know how it goes...
syntax:
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'" -o "C:\SQLBackups\Backupjob_Log.txt"

Also, double check your Instance Name. Looks like you are using "EXPRESS" instead of "SQLEXPRESS" in your batch file.



I've made a change to the batch file, but it appears to not have made a difference. Do I have to remove the job from the Task Scheduler and re-enter it, in order for it to see the change? (I ask this because I've seen that sort of behavior before; especially with Crystal Reports. Crystal would tend to read the whole SQL query, even if it's a stored procedure, and then store that into itself, so if you made any change to the SP, Crystal never saw it.)


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1549918
Posted Tuesday, March 11, 2014 1:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 287, Visits: 1,154
Rod at work (3/11/2014)
sreekanth bandarla (3/11/2014)
I think, That's where you are having issues with. Type it manually and let me know how it goes...
syntax:
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'" -o "C:\SQLBackups\Backupjob_Log.txt"

Also, double check your Instance Name. Looks like you are using "EXPRESS" instead of "SQLEXPRESS" in your batch file.



I've made a change to the batch file, but it appears to not have made a difference. Do I have to remove the job from the Task Scheduler and re-enter it, in order for it to see the change? (I ask this because I've seen that sort of behavior before; especially with Crystal Reports. Crystal would tend to read the whole SQL query, even if it's a stored procedure, and then store that into itself, so if you made any change to the SP, Crystal never saw it.)


Okay, Let's forget about scheduling here for a while....Have you tried running the batch file from command prompt manually and see the results file? May be....you can also try running the batch file something like yourbatchfile.cmd > output.txt and verify the output.txt file.
Post #1549923
Posted Wednesday, March 12, 2014 7:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
When I run the batch file and redirect it's output, the first thing I see is this:

Sqlcmd: 'ΓÇôE': Unexpected argument. Enter '-?' for help.


The contents of the output.txt file are as follows:


C:\Users\rfalanga\Documents\SQL Server Management Studio\Backup Files>sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"


Kindest Regards,

Rod
Connect with me on LinkedIn.
Post #1550237
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse