[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