I like this concept, but I do have a question... Why would you need these scripts instead of using the GUI to restore?  When wouldn't you have access to msdb to run your restore - an entire server crash???  But in that case, we store the backups on the box itself and wouldn't have access to the most current backups anyways...  What case(s) would there be where the server would be available (such that you could get to the backup files) but that SQL Server msdb wouldn't be available?

A lot of times as soon as a backup is complete Full or Transaction log it is copied to some other source.  If you have to pull the backups from another source and restore them to another server that is already running this could help.  I generally don't rely on the GUI to perform everything.  If you had to manually write the script for some reason this would be a big time saver in a disaster scenario.

[quote user="ppcx"]I'm new to MSSQL so this may be a simple question, but I can't get the generated script to run. If I take each line separately copy/paste into a sqlcmd prompt and type "go" then that line will work. But I can't figure out how to make the whole script run. This seems like it would be a great disaster recovery script if I can figure out how to make it work.[/quote]

What are you having an issue with?  Do you get an error message?

RESTORE DATABASE master FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
RESTORE DATABASE master WITH RECOVERY -- [SQLSTATE 01000]
----------------Script to Restore the msdb Database-------------- [SQLSTATE 01000]
RESTORE DATABASE msdb FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
RESTORE DATABASE msdb WITH RECOVERY -- [SQLSTATE 01000]

this is what sqlcmd looks like:

S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup>sqlcmd -S sqlcluster01 -E
1> RESTORE DATABASE master FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
2> RESTORE DATABASE master WITH RECOVERY -- [SQLSTATE 01000]
3> ----------------Script to Restore the msdb Database-------------- [SQLSTATE 01000]
4> RESTORE DATABASE msdb FROM DISK = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_200811070200.bak' WITH NORECOVERY -- [SQLSTATE 01000]
5> RESTORE DATABASE msdb WITH RECOVERY -- [SQLSTATE 01000]

It doesn't see each line as a command to run and just spools them all up together and then complains that the whole huge line isn't valid.

 Hello everone,

From the discussion here I decided to modify the orginal code with the following

1) Added the GO statements so you can now execute the complete output

2) Added STAT=10 meaning that you will get an indication of the restore as show as 10% increaments

3) No restore scripts generated for any system databases (master, model, msdb) as you need to place the server in single user mode to restore the master and then you should manually restore the msdb and model

4) No restore script generated for tempdb

This script works fine with SQL 2000 (x32) and SQL 2005 (x32 and x64)

Enjoy

SET NOCOUNT ON
DECLARE @databaseName sysname

CREATE TABLE #TmpCommands
(ID INT IDENTITY(1,1),
Cmd VARCHAR(8000) )

PRINT '            *************************************************************************'
PRINT '            *                Database Recovery Script Auto Generator                    *'
PRINT '            *                            Version 3.0                                    *'
PRINT '            *                                                                        *'
PRINT '            * Execution of this script will create an output to restore databases    *'
PRINT '            * from this server. Just copy the output, verify and execute.            *'
PRINT '            *                                                                        *'
PRINT '            *Note that this script will not generate the code to restore the system *'
PRINT '            *    databases. But if needed, it can be easily modified                    *'
PRINT '            *                                                                        *'
PRINT '            *                There is no output for the tempdb database!                *'
PRINT '            *                                                                        *'
PRINT '            *************************************************************************'
PRINT ''
PRINT 'USE MASTER'
DECLARE dbnames_cursor CURSOR
FOR
 SELECT name
 FROM master..sysdatabases
 WHERE name NOT IN ( 'master','model','msdb','tempdb', 'pubs','northwind')
 AND (status & 32) =0 -- Do not include loading
 AND (status & 64) =0 -- Do not include loading
 AND (status & 128) =0 -- Do not include recovering
 AND (status & 256) =0 -- Do not include not recovered
 AND (status & 512) =0 -- Do not include offline
 AND (status & 32768) =0 -- Do not include emergency
 AND (status & 1073741824) =0 -- Do not include cleanly shutdown
 
 OPEN dbnames_cursor
 
 PRINT ''
 
 FETCH NEXT FROM dbnames_cursor INTO @databaseName
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 IF (@@FETCH_STATUS <> -2)
 BEGIN


INSERT INTO #TmpCommands(Cmd)
VALUES ('                   -- Script to Restore the ' + @databaseName + ' Database -- ')

DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

INSERT INTO #TmpCommands(Cmd)
SELECT Cmd FROM(
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
 + mf.physical_device_name + ''' WITH NORECOVERY, STATS =10' Cmd
FROM msdb.dbo.backupset b,
 msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
 AND b.database_name = @databaseName
 AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
 + mf.physical_device_name + ''' WITH FILE = ' + CAST(position AS VARCHAR(10)) + ', NORECOVERY, STATS =10' Cmd
FROM msdb.dbo.backupset b,
 msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
 AND b.database_name = @databaseName
 AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
 AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY, STATS =10' Cmd
) A
ORDER BY backup_set_id

END


FETCH NEXT FROM dbnames_cursor INTO @DatabaseName
 END

CLOSE dbnames_cursor
 DEALLOCATE dbnames_cursor

DECLARE @PrintCommand VARCHAR(8000)

DECLARE Print_cursor CURSOR
FOR
 SELECT Cmd FROM #TmpCommands
 ORDER BY ID
 
 OPEN Print_cursor
 
 FETCH NEXT FROM Print_cursor INTO @PrintCommand
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
 IF (@@FETCH_STATUS <> -2)
 BEGIN
 PRINT @PrintCommand
PRINT 'GO'
 END
 FETCH NEXT FROM Print_cursor INTO @PrintCommand
 PRINT ''

 END
 CLOSE Print_cursor
 DEALLOCATE Print_cursor
 
DROP TABLE #TmpCommands

GO

If I'm building scripts for disaster recovery of the database, even though I would need to recover the master in single-user mode, wouldn't it be nice to have the commands already generated? For example, "model" is not simple-recovery so it has transaction logs. It would be nice to have the commands to apply all of the transaction logs to the database.

That depends on your situation. Most of the times you do not have to worry about the model database because it is just used as a model to create new databases, but if you make changes to the model database that get applied to any new database, you should back it up.  Generally the main system databases I worry about is master and msdb. You can modify the script to include whatever databases you may need.

GO [SQLSTATE 01000]

Just by looking at the results, it looks like HTSQLServerINOW6 may be the name of a backup device.  Do you know if anyone may have changed the backup to use a backup device?

Not that I'm aware of....all backups write to the same drive on the server (SAN spaced assigned to the box) here is a snapshot of that:

Not sure but it's just strange that it's referencing the log files corrrectly but not the backups....

It is getting the information from the backupmediafamily table. Run the following query and see if you see anything like that in the physical_device_name column.

Select * from msdb.dbo.backupmediafamily

There sure is....it has to be this Bridgehead agent that's creating these other backups....

First let me say thank you for another, excellent, time-saving tip.  I love this script and have been using it on my SQL 2005 servers without issue for several months now.  I'm now trying to use it with three, legacy SQL Server 2000 instances but am getting mixed results. 

In two of the three SQL 2000 (sp4) environments, I get the following error when the script is parsed:

The point of failure is the last @DatabaseName; the fetch from dbnames_cursor into @DatabaseName.  The DatabaseName variable was declared at the beginning of the script and used 9 times by this point.