Solved

sqlcmd add timestamp to output file

Posted on 2013-06-12
4
2,434 Views
Last Modified: 2013-06-17
Hello,
  I have code:
::TIME
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
::Darom diff backup
echo .....Runing DIFF Backup.....Do not close this window....
echo .......
SQLCMD -E -S %SERVERNAME% -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK='%BACKUPDIR%%DATABASENAME%_DIFF_MANUAL_%mytime%-%mydate%.bak' WITH DIFFERENTIAL,INIT, COMPRESSION, STATS = 10"

Open in new window


Now i am using time and date from cmd, but how can i use sqlcmd time stamp to add date to backup file. if i run backup from sql server i can add
"+ REPLACE(convert(nvarchar(20),GetDate(),120),':','-')" and it will work.

Is it possible to use same thing on sqlcmd
0
Comment
Question by:thinkbot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39242267
Batch files make my head hurt...  How about some powershell?!

$databaseName = "testing";
$serverName = "servar";
$backupDirPath = "C:\testing\path\";

$currDate = get-date -format MMddyyyy_hhmmss;
$fullPath = $backupDirPath + $databaseName + "_DIFF_MANUAL_" + $currDate + ".bak";
$expression = "SQLCMD -E -S " + $serverName + " -Q `"BACKUP DATABASE " + $databaseName + " TO DISK='" + $fullPath + "' WITH DIFFERENTIAL,INIT, COMPRESSION, STATS = 10`"";

echo $expression;

#Invoke-Expression $expression;

Open in new window


That, in it's current form is only going to echo the command to use in the invoke-expression cmdlet, but I figure you should probably look at it first before you run it.  You would just remove the comment in front of the invoke-expression cmdlet.
0
 

Author Comment

by:thinkbot
ID: 39243760
i think i asked in wrong way. Lets try again. Is it possible to use sql function  getdate() in sqlcmd ?

i need to make name to be same as from sql server backup:
database__2013_06_08_010000_8411342.bak

First part is easy "2013_06_08_010000", date and time, but how to add "_8411342" this number.

Thanks,
  Tadas
0
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39248722
The answer is yes, but I think the problem lies in the fact that even out of SSMS, you would need to do this differently.  You can't concatenate like this:

BACKUP DATABASE <<dbname>> TO DISK=N'<<PATH HERE>>' + REPLACE( convert( nvarchar( 20 ),GetDate(),120), ':', '-') + N'.bak' WITH DIFFERENTIAL, INIT, COMPRESSION, STATS = 10;

Open in new window


You would need to do something like this:
declare @fileName nvarchar( 200 ) = N'<<PATH_HERE>\<<DBNAME>>_DIFF_MANUAL_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + N'.bak';
BACKUP DATABASE <<dbname>> TO DISK=@fileName WITH DIFFERENTIAL, INIT, COMPRESSION, STATS = 10;

Open in new window


Which you can absolutely plop into a SQLCMD:
set servername=<<serverName>>
set databasename=<<fileName>>
set backupdir=<<path>>

::Darom diff backup
echo .....Runing DIFF Backup.....Do not close this window....
echo .......
SQLCMD -E -S %servername% -Q "declare @fileName nvarchar( 200 ) = N'%backupdir%%databasename%_DIFF_MANUAL_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + N'.bak'; BACKUP DATABASE %databasename% TO DISK=@fileName WITH DIFFERENTIAL, INIT, COMPRESSION, STATS = 10;"

Open in new window


Is that closer to what you were looking for?

Edit:

Looking at the format you wanted, where is that 010000 coming from?
0
 

Author Comment

by:thinkbot
ID: 39255356
@didnthaveaname, yes, thank you.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Know what services you can and cannot, should and should not combine on your server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question