June 9, 2015 at 3:16 pm
Hello, I am running backups on SQL Sever express and I take backups via the batch file executing the TSQL as below. This works perfectly fine, I just need to be able to spool out the backup completion log. What TSQL command can I use to do that? Can you please help me with that command.
My batch file looks like this:
@echo off
sqlcmd -S SERVER01 -E -i H:\master_scripts\TOM_FAM_log.sql
Which will the call the TOM_FAM_log.sql :
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
SET @path = 'H:\Backup\TOM_FAM\'
--SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) -- original
--SELECT @timestamp = CONVERT(timestamp,GETDATE()) -- did not format
SELECT @fileDate = REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','');
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name = N'TOM_FAM'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.LOG' + '.TRN'
BACKUP LOG @name TO DISK = @fileName
WITH NAME = N'TOM_FAM 15 MINUTE LOG BACKUP',
NOINIT, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
--------------------------------------------------------
Usually if we schedule the backups using the maintenance plan, we can chose reporting options that can spool the result of the backup to a text file. What is the T-SQL for the spool report to a text file.
This option produces a file that writes logs with the details I posted below. I would like to do the same or similar using T-SQL in my code.
Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.3000
Report was generated on "SERVER01".
Maintenance Plan: Backup logs
Duration: 00:00:00
Status: Succeeded.
Details:
Back Up Database (Transaction Log) (SERVER01)
Backup Database on Local server connection
Databases: COU_FAM
Type: Transaction Log
Append existing
Task start: 2015-06-08T23:00:01.
Task end: 2015-06-08T23:00:01.
Success
Command:BACKUP LOG [COU_FAM] TO DISK = N''H:\Backup\COU_FAM\COU_FAM_backup_2015_06_08_230001_0808933.trn'' WITH NOFORMAT, NOINIT, NAME = N''COU_FAM_backup_2015_06_08_230001_0808933'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
____________________________________
Your help will be greatly appreciated.
June 10, 2015 at 3:47 am
Hi,
An option could be using the -o (output) option in sqlcmd or redirecting output to a file using the "> logfile.txt" pipe in the command prompt.
I hope that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply