Jamik

Another undocumented SP in Microsoft SQL Server is the ‘sp_MSForEachDB‘ procedure. This procedure enumerates every database on a Microsoft SQL Server Instance and let you perform different actions on every database. This can be quite useful in several situations. For example the following two scripts are

• creating database backups of  all databases on a SQL Server Instance
• creating a restore script for those databases

To use those scripts you may want to modify the paths  (‘c:\#Backup’, ‘C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.mdf’,….) to match your environment:

Backup

EXEC sp_MSForEachDB
 'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]= ''?'')) NOT IN (''tempdb'',''master'',''model'',''msdb'')
 BACKUP DATABASE [?] TO DISK = N''C:\#BACKUP\?.bak'' WITH NOFORMAT, NOINIT, NAME =
 N''?-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD'

Restore

EXEC sp_MSForEachDB
'IF DB_NAME((SELECT dbid FROM sysdatabases WHERE [Name]= ''?'')) NOT IN (''tempdb'',''master'',''model'',''msdb'')
 PRINT "RESTORE DATABASE [?] FROM DISK =N''C:\#BACKUP\?.bak'' WITH FILE =1,
 MOVE N''?'' TO N''C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.mdf'',
 MOVE N''?_log'' TO N''C:\MS SQL Data\MSSQL.1\MSSQL\Data\?.ldf'',
 NOUNLOAD,  REPLACE,  STATS = 10
GO"
'

Microsoft has released the feature pack for SQL Server 2008 R2. The feature pack is a collection of stand alone packages which extends the functionality of SQL Server in several ways. Among other things the package contains:

• Microsoft® SQL Server Report Builder 3.0 for Microsoft® SQL Server 2008 R2
• Microsoft® SQL Server® PowerPivot for Microsoft® Excel
• Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft® SharePoint® Technologies 2010
• Microsoft® SQL Server® 2008 Reporting Services Add-in for Microsoft® SharePoint® Technologies 2007

Get it from here.