Solved

Trim filename via batch command?

Posted on 2009-04-01
27
1,619 Views
Last Modified: 2012-05-06
I have a SQL server backup file (.bak) that has the timestamp as part of the file name

For example:  DBSallc03312009.bak

The SQL Script restores based on the db name alone (DBSallc.bak)

Need a script that could trim the 03312009 from the file name.
0
Comment
Question by:PROJHOPE
  • 10
  • 9
  • 8
27 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 24040802
set name=DBSallc03312009.bak
set name=%name:~0,-12%.bak

That cuts the name beginning from first char (~0) to end-12 chars.
0
 

Author Comment

by:PROJHOPE
ID: 24041474
Qlemo

Sorry but not seeing the forest within the trees..is it something like this?

cd e:\SQL Backup\Data
$filename = DIR("E:\SQL Backup\Data\*.*")
While $fileName <> "" and @ERROR = 0
set name=e:\SQL\Data\%name:~0,-12%.bak
loop
end



0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24041536
Wrong zone (MS-DOS)? That script looks more like something else.
0
 

Author Comment

by:PROJHOPE
ID: 24041759
ahh you are right...that was a former code in kix!  So using the kix shell what would be the dos equivalent?

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24044738
I do not get the full sense of that command, however here you get my interpretation. It would work even with several DB backups, but of different DBs only! If there are more than one backup of the same DB, the script would have to find the newest.

@echo off

setlocal EnableDelayedExpansion

cd e:\SQL Backup\Data

for %%F in (*.*) do (

   set name=%%~dpnF

   set name=!name:~0,-12!.bak

REM restore command here

)

Open in new window

0
 
LVL 16

Expert Comment

by:t0t0
ID: 24045383
Qlemo, hiya...

Just been messing around with come code and discovered the following. Neat eh?

@echo off
setlocal enabledelayedexpansion
cd e:\SQL Backup\Data
for %%a in (*) do (
   set file=%%a
   RESTORE !file:%file:~-12,8%=!
)
0
 

Author Comment

by:PROJHOPE
ID: 24053818
What does the RESTORE command do?  Because when I create the script above into a .bat file and run it  I get

RESTORE is not recognized as internal/external command
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24054798
PROJHOPE

I apologise if I've misled you. Although RESTORE is a valid keyword, it was actually meant as a symbolic phrase to indicate where your own restore statement should be placed in the code. The actual code which I was drawing Qlemo's attention to was the  "!file:%file:~-12,8%=!" bit following the restore statement.

I don't mean to steal this question from Qlemo who is a first class batch file programmer. You are in good hands so I'm happy to pass you back to his care.

Oh, and don't forget the double-quotes where necessary.

@echo off
setlocal enabledelayedexpansion
cd "e:\SQL Backup\Data"
for %%a in (*) do (
   set file=%%a
   YOUR_RESTORE_STATEMENT "!file:%file:~-12,8%=!"
)
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24055194
t0t0,
"I knew that trick already" ;-) But it is bit oversized for this purpose, of course.
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24055304
Qlemo. I had a sneaky suspicion you knew that and I'm sure your reason for not going with it is a good one however, personally, I find It eliminates the addition of an intermediate SET assignment.

Anyway, I leave it in your capable hands.
0
 

Author Comment

by:PROJHOPE
ID: 24059457
t0t0 and Qlemo thanks but I only need the script to strip the file, I already have my restore SQL statement so would the syntax above simply be just..

@echo off
setlocal enabledelayedexpansion
cd "e:\SQL Backup\Data"
for %%a in (*) do (
   set file=%%a
   )
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24060261
What you need depends on your abilities to include the result into your restore command. How do you want to achieve this? The script could set a environment variable, or echo it on standard out, or write it into a text file, or ...

0
 
LVL 16

Expert Comment

by:t0t0
ID: 24060571
PROJHOPE

No, that only returns the whole filename itself. The bit that chops out the date is also required.

Qlemo used the following method:

   set name=%%~dpna
   set name=!name:~0,-12!.bak

whereas I used the following method:

   set name=%%a
   set name=!name:%name:~-12,8%=!

The difference is, Qlemo's code chops off the last 12 characters (03312009.bak) leaving just: 'DBSallc' and then he adds the '.bak' onto the end making 'DBSallc.bak'.

My code looks at the filename as a whole and just removes the date bit (03312009) closing the gap up between the 'DBSallc' and '.bak'. - if that makes any sense to you.

Both methods rely on delayed variable expansion however, Qlemo's method takes the simpler approach.

(code modifed slighly to fit the frame of the code)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:PROJHOPE
ID: 24060785
Let me step back I am leaving this job today so forgive me if my mind isn't in 5000 places.  I did a google on RESTORE and my understanding of the DOS RESTORE is similar to the SQL RESTORE command where it restores from a backup file.

All I want is to take the files in the SQL Backup folder and trim the filename to exclude the date in between the database name and the .bak file

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24061001
and then perform what and how with the result?
0
 

Author Comment

by:PROJHOPE
ID: 24061099
simply replace the DBSallc03132009.bak with DBSallc.bak in the same folder

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24061158
With replace you mean to physically rename the file? Or should we just copy it?
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24061168
Okay...

Suppose you set a variable to equal the filename such as:

   set name=DBSallc03312009.bak

then you'll need the following:

   set name=%name:~0,-12%.bak

to set name to DBSallc.bak

The purpose of most of the code is to get the filename (DBSallc03312009.bak) into the variable 'name' to start with. But because we don't know what the actiual name of the file is we need to 'search' for it - which is where the FOR loop comes in. Also, because we're using a FOR loop, we need to instruct the batch file interpretter to expand variables inside the FOR loop which is why we used 'set name=!name:~0,-12!.bak' instead of 'set name=%name:~0,-12%.bak' (note the differences in the '%' and '!' characters).

Once the program jumps out of the FOR loop, the variable name will contain the shortened filename of the last file processed. The problem is, we don't know how many files there are so rather than process the file outside the FOR loop (after it's finished) we process the file(s) INSIDE the FOR loop ensuring that every file encountered gets processed - not just the last one.

If you intend to just rename the file then you need something like:

@echo off
setlocal enabledelayedexpansion
cd e:\SQL Backup\Data
for %%a in (*) do (
   set name=%%~dpnF
   ren %%a !name:~0,-12!.bak
)

The problem with this though is if there is more than one file starting with 'DBSallc' things are going to get sticky.

0
 

Author Comment

by:PROJHOPE
ID: 24062583
Ok to answer, there isn't going to be more than one file and for the section (*) do I put the path?  (e:\SQL Backup\Data)

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 24062717
Put the full path plus \*, so it's
e:\SQL Backup\Data\*
0
 

Author Comment

by:PROJHOPE
ID: 24062796
Ok check my work one more time, we are getting close I get the system cannot find the specified file.



@echo off
setlocal enabledelayedexpansion
cd e:\SQL Backup\Data
for %%a in (e:\SQL Backup\Data\*) do (
   set name=%%~dpnF
   ren %%a !name:~0,-12!.bak
)
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24063254
Change this line:

   set name=%%~dpnF

to

   set name=%%~dpna

It appears you've mixed bits of Qlemo's code and my code (Qlemo used 'F', I used 'a').

Also, you don't need the following line:

   cd e:\SQL Backup\Data

if you're using:

   for %%a in (e:\SQL Backup\Data\*) do (

instead of:

   for %%a in (*) do (

However, you must use double-quotes, as in:

   for %%a in ("e:\SQL Backup\Data\*") do (

and:

   ren "%%a" !name:~0,-12!.bak

0
 
LVL 16

Expert Comment

by:t0t0
ID: 24063277
So your code should look something like:

@echo off
setlocal enabledelayedexpansion
for %%a in ("e:\SQL Backup\Data\*") do (
   set name=%%~dpna
   ren "%%a" !name:~0,-12!.bak
)
0
 

Author Comment

by:PROJHOPE
ID: 24063559
Last try and then I will give up and give credit...

@echo off
setlocal enabledelayedexpansion
for %%a in ("e:\SQL Backup\Data\*") do (
   set name=%%~dpna
   ren "%%a" !name:~0,-12!.bak
)

That's the script and this time it does loop through each file and gives the error for each file

'The syntax of the command is incorrect'

in the command prompt

0
 
LVL 16

Accepted Solution

by:
t0t0 earned 500 total points
ID: 24063690
This will work!!!

@echo off
setlocal enabledelayedexpansion
for %%a in ("e:\SQL Backup\Data\*") do (
   set name=%%~na
   ren "%%a" !name:~0,-8!.bak
)
0
 

Author Comment

by:PROJHOPE
ID: 24063988
You guys rock!  Thanks it does work!
0
 
LVL 16

Expert Comment

by:t0t0
ID: 24065605
Thank you PROJHOPE, I'm pleased we got there in the end.

It's a shame you didn't share points to Qlemo who put a lot of work into this too. What happens so often on EE is that experts are not often available at the same time as they may be based in different parts of the world. I'm sure if Qlemo was available to answer this question when I did he would have supplied the same answer.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

One of my most closely kept secrets is revealed in this discussion How to output text on the same line This question was recently posted in EE by Simon336697 (http://www.experts-exchange.com/Programming/Languages/Scripting/Shell/Batch/Q_2459…
TOMORROW TOMORROW.BAT is inspired by a question I get asked over and over again; that is, "How can I use batch file commands to obtain tomorrow's date?" The crux of this batch file revolves around the XCOPY command - a technique I discovered w…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now