Control store procedure outputs

Question

I have the following store proc

SET @sql = 'RESTORE DATABASE ' + quotename(@dbname) + '
            FROM  DISK = N''E:\sql\template_' + @dbnamebak + '.bak''
            WITH  FILE = 1,
            MOVE N''FromTemplate' + @dbname + '.Data'' TO N''E:\sql\' + @dbname + '.mdf'',
            MOVE N''FromTemplate' + @dbname + '.Log'' TO N''E:\sql\' + @dbname + '_log.LDF'', NOUNLOAD';
EXEC (@sql);

I would like to make the output either 1 or 0 instead of:

Processed 8584 pages for database 'DatabaseName', file 'DatabaseName.Data' on file 1.
Processed 1 pages for database 'DatabaseName', file 'DatabaseName' on file 1.
RESTORE DATABASE successfully processed 8585 pages in 3.929 seconds (17.899 MB/sec).

Is it possible to control this?

Using MS SQL Server 2005.

Answer 1

What does "output" mean? There are at least four ways to return information from a stored procedure: result set(s), output parameter(s), PRINT and the return code.

I don't think you can suppress the output, but you could put your RESTORE statement inside a TRY/CATCH block. If the RESTORE fails, return your failure code from the CATCH block, otherwise return success from the main procedure body.

Alternatively, don't use a stored procedure but an external script. I find that's actually much easier in many cases when working with backups and other operations that require you to manipulate filenames and even physical files. You could use Smo from a Powershell script, for example.