[SQL] Scripting backup and restore all databases on a server (Part 2 - Extra Credit)

In the first post of this series, I discussed scripting database restore statements. It seems simple enough, but there's a complication - the restore statement requires the database logical name. The logical name usually follows the default format (a database named Example would have a data logical name of Example_Data and a log logical name of Example_Log), but it doesn't have to. The logical name could be just about anything.

The easiest solution is to change the logical filenames to match the database name. Barring that, we've got a bit of a conundrum on our hands, because there's no one database that holds the logical filenames for all databases.

Normally I'd jump all over the INFORMATION_SCHEMA views. These things are easy to use, they're part of the SQL-92 standard, they've got a great beat, and they're easy to dance to. But these views don't know a thing about the way the data is actually persisted to files. It makes sense when you think about it - SQL-92 is a broad standard written for all kinds of databases and operating systems, which could store the data in all kinds of ways.

So, we'll look in the master database and sort this out, right? Not so fast, Slick! The actual file information in SQL Server isn't stored in the master database, it's stored in each database - in sysfiles, to be exact. No problem if you're only dealing with one database, but tricky if you need to deal with all databases on a server. That's what got me into this mess, remember?

Well, to the rescue comes sp_MSforeachdb, which loops through all databases on a server calling whatever SQL string you feed it. It even subs in the database name if you give it a question mark (?). Maybe we've got a shot at this then...

The following script builds a temp table (#fileinfo) which holds the logical and physical names of both the data and log files for every database on a server. No, this probably won't help that guy with multiple data or log files for a single database (I think his name was Raphael), but he stopped reading back at the first paragraph. For everyone else, this script first builds #fileinfo, then it uses it to generate RESTORE statements.

The obvious use of this script is to - wait for it - restore databases. You'd use the backup script generator I wrote about before, and you'd use the following script to crank out the restore statements. I'd encourage you to take a look at the data returned by "select * from #fileinfo", though. Maybe you can think of something even more exciting to do with a table that holds the logical and physical names of every database file on a server. Please promise to use your powers for good...

create table #fileinfo (
    [db] varchar(100),
    name varchar(100),
    filename varchar(100),
    logname varchar (100),
    logfilename varchar(100))

exec sp_MSforeachdb 
'use ?; 
insert into #fileinfo ([db],name,filename) select 
    ''?'',
    rtrim(name),
    rtrim(filename)
from sysfiles
where status & 0x40 != 0x40'

exec sp_MSforeachdb 
'use ?; 
update #fileinfo set 
    logname = rtrim(s.name), 
    logfilename = rtrim(s.filename)
from    sysfiles s
where    status & 0x40 = 0x40
and        [db] = ''?'''

delete from #fileinfo where db in ('model','master','tempdb','pubs','Northwind','msdb')

select 
    'restore database ' + quotename(db) 
    + ' from disk=''' + db + '.bak'' WITH MOVE ''' 
    + name + ''' TO ''' + filename + ''', MOVE ''' 
    + logname +''' TO ''' + logfilename + ''''
from #fileinfo

--select * from #fileinfo

drop table #fileinfo