TSQL: Restore commands for al DBs

Generate restore commands for all user databases on a server. To replace curent file locations with a new file location you can strip out the filenames and feed in a paramer for the new data/log folder. Or edit/replace the output.

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