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.

Problem:
If you’re trying to open an Office Document in SharePoint 2010 with the included WebApps you might encounter the following error in the diag log/screen:

Corresponding diag log entry:
System.Data.SqlClient.SqlException: Cannot open database "CONTENTDB" requested by the login. The login failed. Login failed for user 'DOMAIN\ACCOUNT'.

Description:
If you’ve not used the Farm Configuration Wizzard the service accounts you’re using for the Web App Service Applications might not be provisioned correctly and have not the neccessary rights to connect to the content database.

Fix:
Grant at least read/write permissions to the service application account at the corresponding content database.

PS: There might be an PowerShell CmdLet for this operation – this post will get updated if i have found a better solution.

Update:
As promised – the PowerShell Script:

$w = Get-SPWebApplication -identity  http://URL
$w.GrantAccessToProcessIdentity("DOMAIN\SERVICEAPP")

Problem:
If you try to install SQL Server 2008 (without SP1) on a Windows 2008 R2 Server the setup fails with an application error.

Fix:
Install SP1 before starting the installation of SQL Server 2008. SP1 updates the required components which are used by the setup. Rerun SP1 after setup is finished.

References:

• http://support.microsoft.com/kb/955725

SQL Server Log Entry:
Login failed for user 'NT-AUTHORTIY\SYSTEM'. [CLIENT: ]
Error: 18456, Severity: 14, State: 16.

Event Log Entry:
Event ID: 18456
Login failed for user 'NT-AUTHORTIY\SYSTEM'. [CLIENT: ]

Fix:
Open SQL Management Studio, locate the SQL Agent Jobs & deactivate/delete the corresponding “Shared_ServicesXXX_DB_Job_DeleteExpiredSessions“.