Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Should I backup the transaction log?


Should I backup the transaction log?

Author
Message
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 1945
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"
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1927
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,RodConnect with me on LinkedIn.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1927
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,RodConnect with me on LinkedIn.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 1945
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.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 1945
Also, you don't need to specify -E if you are using -U and -p switches and they are case sensitive.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1927
I copied the text from the KB article and pasted it into my batch file.

Kindest Regards,RodConnect with me on LinkedIn.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 1945
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1927
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,RodConnect with me on LinkedIn.
Sreekanth B
Sreekanth B
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 1945
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.
Rod
Rod
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 1927
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,RodConnect with me on LinkedIn.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search