SELECT SUBSTRING
( 'filename',
LEN('filename') - CHARINDEX('\',REVERSE('filename')) + 2 ,
CHARINDEX('\',REVERSE('filename'))
)
Better still, and to be posted later, have a stanard folder location and naming convention and move the logical files to a standard named file based upon the name of the DB.
/****CommentStart
Author : Kevin Foreman
Date: 21-11-2006
Description:
Generates restore copmmands for all databases on a server
where dbid > 4
Uses WITH MOVE, STATS = 10 and REPLACE
char(13) used to format it nicely if exec in text
Maintained:
CommentEnd****/
SET NOCOUNT ON
-- setup varaibles
DECLARE
@db_name sysname ,
@sql nvarchar(2000) ,
@aCur_db_name sysname ,
@bCur_name varchar(200),
@bCur_filename varchar(200)
DECLARE
@Database_Name TABLE
(
name sysname,
Done bit DEFAULT 0
)
CREATE TABLE
#Database_Files
(
name sysname ,
fileid int,
filename varchar(2000),
filegroup varchar(100) ,
size varchar(32) ,
maxsize varchar(32) ,
growth varchar(32) ,
usage varchar(32)
)
CREATE TABLE
#Database_Files_Summary
(
database_name sysname NULL,
name sysname ,
fileid int,
filename varchar(2000),
)
--get user database names
INSERT
@Database_Name
(Name)
SELECT
sd.name AS 'Name'
FROM
master.dbo.sysdatabases AS sd
WHERE
sd.dbid > 4
-- get user database file info
WHILE EXISTS (SELECT * FROM @Database_Name WHERE Done = 0)
BEGIN
SELECT @db_name =
(SELECT TOP 1 dn.name
FROM @Database_name as dn
WHERE Done = 0)
SELECT @sql = @db_name + '.dbo.sp_helpfile'
INSERT #Database_Files
EXEC sp_executesql @sql
INSERT #Database_Files_Summary
(database_name,name,fileid,filename)
SELECT
@db_name,name,fileid,filename FROM #Database_Files
DELETE FROM #Database_Files
UPDATE @Database_name
SET Done = 1
WHERE name = @db_name
END
--cursor through database names and then files to generate restore commands
--database name cursor
DECLARE aCur CURSOR
FOR SELECT name FROM @Database_Name ORDER BY name ASC
OPEN aCur
FETCH NEXT FROM aCur INTO @aCur_db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'RESTORE DATABASE ''' + LTRIM(RTRIM(@aCur_db_name)) + ''' FROM DISK = ''[filename]''' + char(13) + 'WITH ' + char(13)
--database file cursor
DECLARE bCur CURSOR
FOR SELECT name , filename FROM #Database_Files_Summary WHERE database_name = @aCur_db_name ORDER BY fileid ASC
OPEN bCur
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
SELECT @sql = @sql + ' MOVE ''' + LTRIM(RTRIM(@bCur_name)) + ''' TO ''' + LTRIM(RTRIM(@bCur_filename)) + ''' ,' + char(13)
END
CLOSE bCur
DEALLOCATE bCur
FETCH NEXT FROM aCur INTO @aCur_db_name
SELECT @sql = @sql + ' STATS = 10 , REPLACE'
--return the restore command
SELECT @sql
END
CLOSE aCur
DEALLOCATE aCur
--cleanup
DROP TABLE #Database_Files
DROP TABLE #Database_Files_Summary
