Trim filename via batch command?

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.
PROJHOPEAsked:
Who is Participating?
 
t0t0Connect With a Mentor Commented:
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
 
QlemoDeveloperCommented:
set name=DBSallc03312009.bak
set name=%name:~0,-12%.bak

That cuts the name beginning from first char (~0) to end-12 chars.
0
 
PROJHOPEAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
QlemoDeveloperCommented:
Wrong zone (MS-DOS)? That script looks more like something else.
0
 
PROJHOPEAuthor Commented:
ahh you are right...that was a former code in kix!  So using the kix shell what would be the dos equivalent?

0
 
QlemoDeveloperCommented:
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
 
t0t0Commented:
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
 
PROJHOPEAuthor Commented:
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
 
t0t0Commented:
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
 
QlemoDeveloperCommented:
t0t0,
"I knew that trick already" ;-) But it is bit oversized for this purpose, of course.
0
 
t0t0Commented:
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
 
PROJHOPEAuthor Commented:
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
 
QlemoDeveloperCommented:
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
 
t0t0Commented:
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
 
PROJHOPEAuthor Commented:
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
 
QlemoDeveloperCommented:
and then perform what and how with the result?
0
 
PROJHOPEAuthor Commented:
simply replace the DBSallc03132009.bak with DBSallc.bak in the same folder

0
 
QlemoDeveloperCommented:
With replace you mean to physically rename the file? Or should we just copy it?
0
 
t0t0Commented:
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
 
PROJHOPEAuthor Commented:
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
 
QlemoDeveloperCommented:
Put the full path plus \*, so it's
e:\SQL Backup\Data\*
0
 
PROJHOPEAuthor Commented:
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
 
t0t0Commented:
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
 
t0t0Commented:
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
 
PROJHOPEAuthor Commented:
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
 
PROJHOPEAuthor Commented:
You guys rock!  Thanks it does work!
0
 
t0t0Commented:
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
All Courses

From novice to tech pro — start learning today.