November 4, 2009 at 9:22 am
Hi,
I am trying to automate a restore of 2500+ databases. The files are named like so:
survey_p12345_db_200911012014.BAK
I have the following code so far
USE master
DROP TABLE #databases
SET NOCOUNT ON
DECLARE
@backupPathsysname,
@cmdsysname,
SET @backupPath = '"D:\SQL Backup\"'
SET @cmd = 'dir ' + @backupPath + '*.bak /a:-d /b'
/* ===================
Create a new temp table and populate with all *.bak files in the
backup folder (as assigned in @backupPath)
=================== */
CREATE TABLE #databases
(dbname sysname NULL,
datafilename sysname NULL)
INSERT INTO #databases (datafilename)
EXECUTE xp_cmdshell @cmd
UPDATE #databases
SET dbname = REPLACE(datafilename,'.bak','')
As you can tell, dbname is currently set to 'survey_p12345_db_200911012014' and I'd like it to be 'survey_p12345'. Anyone have any ideas how I can easily get the database name out of the datafilename?
November 4, 2009 at 9:25 am
Is there a reason that you're pulling the data out of the directory instead of out of the msdb backup tables? msdb.dbo.backupset has the file name and the database name in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 10:13 am
1) it's a new server and it does not have that data
2) the file locations are different from when they were backed up so that wouldn't even work if I restored the msdb database from the original server.
November 4, 2009 at 10:51 am
by using the CHARINDEX2 function below, which returns the nth position of a given string, this worked fine for me:
/*
Example:
SELECT dbo.CHARINDEX2('a', 'abbabba', 3)
returns the location of the third occurrence of 'a'
which is 7
*/
CREATE FUNCTION CHARINDEX2
(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int
)
RETURNS int
as
begin
declare @pos int, @counter int, @ret int
set @pos = CHARINDEX(@TargetStr, @SearchedStr)
set @counter = 1
if @Occurrence = 1 set @ret = @pos
else
begin
while (@counter < @Occurrence)
begin
select @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
set @counter = @counter + 1
set @pos = @ret
end
end
RETURN(@ret)
end
GO
declare @path varchar(200)
SET @path='survey_p12345_db_200911012014.BAK'
select LEFT(@path,dbo.CHARINDEX2('_',@path,2) -1) --one char less than where the_ to exclude the _
--results: survey_p12345
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply