[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
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')

'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



  • Very nice. Minor mod -- enclose your database name in the "Use" statements in [] (e.g. [?]) in case the database name has hyphens in it.

  • Why is this site so slow to load up... in comparison to many other blogs i read. Haacked for example loads way quicker.

    Not relevant to this post, but how else do we find out.

  • @anon = I don't host my blog; it's part of the weblogs.asp.net site. Haacked runs his on a server with very low load (I'm an admin on that box; it's usually under 10% CPU).

    The load time on this system varies through the day; it's really good right now.

    Better question for you - why are you reading this in a browser? You gotta get yerself an RSS aggregator.

  • Isn't the information needed in the msdb database...

    select distinct 'restore database '
    + quotename(bs.database_name)
    + ' from disk='''
    + bs.database_name + '.bak'' WITH MOVE '''
    + bf1.logical_name + ''' TO '''
    + bf1.physical_name + ''', MOVE '''
    + bf2.logical_name +''' TO '''
    + bf2.physical_name
    + ''''
    from msdb..backupfile bf1 join msdb..backupfile bf2
    on bf1.backup_set_id = bf2.backup_set_id
    and bf1.file_type = 'D'
    and bf2.file_type = 'L'
    join msdb..backupset bs
    on bf1.backup_set_id = bs.backup_set_id
    and bs.server_name = 'yourservername'
    and bs.backup_finish_date > getdate()-7

  • The revision (mod) about adding the brackets for the hyphen was a big help. Our script was getting errors and found out another vendor had a hyphenated database name. Thanks!

  • Thanks for the article post.Thanks Again. Keep writing.

Comments have been disabled for this content.