In last days, I was trying to restore SharePoint web application backup using PowerShell, but run into following error (although my sharepoint version was newer then the backup version)
Restore-SPSite : Your backup is from a different version of Microsoft SharePoint Foundation and cannot be restored to a server running the current version. The backup file should be restored to a server with version '14.0.0.5138' or later.
Following is the quick overview / steps which I followed and successfully restored the SharePoint backup. Might be this could be applicable in your case
If you have the source database files (mdf and ldf) not the SharePoint backup file then follow step 2, otherwise step 1 and 2
Step 1.
- Create a virtual machine if you don’t have
- Install SharePoint on that vm if your don’t have it on that vm
- Make the environment version same by installing the same cumulative update(s) which source server has. Once the server has the same version as of the backup, in my case it was (14.0.0.5138). (to check SharePoint version central administration -> upgrade and migration -> check product and patch installation)
- Create new web application and Restore the SharePoint backup.
- In sql server, detach the restored SharePoint database and physically copy database files (mdf and ldf)
Step 2.
On SharePoint environment where you want to restore the backup (this SharePoint version obviously needs to be later version, in my case it was 14.0.6029.1000)
- Create web application (no need to create site collection) (central administration -> application management -> manage web application-> new)
- Remove content database of newly created web application (central administration -> application management -> manage content database -> choose web application from the dropdown -> click on database name -> choose offline from database status and check mark Remove content database) (make sure you have selected the newly created web application and its content database)
- In sql server, detach that content database of newly created web application
- Physically replace the detached content database files (mdf and ldf) from the SharePoint backup's mdf/ldf file (which were taken from step1 or from the original server / source)
- Attach Database (while attaching, after selecting mdf file, Change the Database name as of file name in Database to Attach's -> Attach As textbox, then on same screen, select/browse both mdf and ldf file in database details section -> Current File Path)
- Open SharePoint 2010 Management Shell, and add content database using following command
stsadm -o addcontentdb -url <URL name> -databasename <database name>
- Then follow remaining application specific restore steps (for e.g. change site collection administrators, changes in web.config, add or change global resource files etc.)
Then access web application, hope so it would restored successfully
If you want to show records (Parent Child concatenation), then following SQL Server recursive query could help you in achieve. For a clear picture have a look at following snap
Recursive Query showing only child Records
with parentChildResult as
(
select ItemId,
ItemDescription,
ItemCategoryId,
cast('' as nvarchar(max)) as ParentNames
from Items
where ItemCategoryId is null
union all
select i2.ItemId,
i2.ItemDescription,
i2.ItemCategoryId,
parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription
from Items as i2
inner join parentChildResult
on parentChildResult.ItemId = i2.ItemCategoryId
)
select ItemId,
stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames
from parentChildResult
where ItemId in
(
SELECT i.ItemId FROM Items i
WHERE NOT EXISTS(SELECT 1 FROM Items I2 WHERE i.ItemId = I2.ItemCategoryId)
)
order by ParentNames
Recursive Query showing All parents and Child Records
with parentChildResult as
(
select ItemId,
ItemDescription,
ItemCategoryId,
cast('' as nvarchar(max)) as ParentNames
from Items
where ItemCategoryId is null
union all
select i2.ItemId,
i2.ItemDescription,
i2.ItemCategoryId,
parentChildResult.ParentNames + ' > ' + parentChildResult.ItemDescription
from Items as i2
inner join parentChildResult
on parentChildResult.ItemId = i2.ItemCategoryId
)
select ItemId,
stuff(ParentNames, 1, 3, '') + ' > ' + ItemDescription as ParentNames
from parentChildResult
order by ParentNames
Query to find all the Tables and Index with number of days statistics being old. Then run Update Statistics command to update statictics for optimized query plan.
SELECT
OBJECT_NAME(A.object_id) AS Object_Name,
A.name AS index_name,
STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),
getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC
Accessing JD Edwards Data on iSeries or AS/400 from a ASP.NET
Facing problem, while retrieving field data from JD Edwards to SQL (SSRS Reporting), giving Error or Special unicode characters,
because, the returning data was a stream that needs to be converted into ASCII (ebcdic37String) using Cp037 Encoding.
public static String convertEBCDIC37ToUnicode(String ebcdic37String)
{
String encoding = "Cp037";
byte[] ebcdic;
String converted = null;
try
{
ebcdic = ebcdic37String.getBytes();
converted = new String( ebcdic, encoding );
}
catch (Exception e)
{
//Handle it
}
return converted;
}
Yesterday, after restoring CRM DB backup on other maching and importing Organization using Deployment Manager of CRM 4.0, I received this error.
The SELECT permission was denied on the object 'BuildVersion', database 'MicrosoftCRM_MSCRM', schema 'dbo'
or the variation
The SELECT permission was denied on the object 'SystemUser', database 'MicrosoftCRM_MSCRM', schema 'dbo'
If you have tried changing the owner of new database with admin login making it as dbowner but did not help then following may help solving this problem
Right click the affected object (table/view etc) in my case it was 'BuildVersion' and 'SystemUser' in SQL Server Management Studio,
click on properties
click on "Permissions" on left tab
click on "View schema permission" link on right tab
click on "Add" button,
click on "Browse"
select "CRMReaderRole" of type Database role.
click ok,
click ok,
now you will be again in object properties.
Place check mark in "Grant" column against "Select" permission
click ok
Now check hope so it has been resolved.
================
To change the dbowner command is (If required)
use <CRMDatabaseThatYouNeedToChangeTheOwner>
exec sp_changedbowner 'DOMAIN\AdministratorMappedWithCrmAdmin'
go
===============
I was getting this error
No authority could be contacted for authentication
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.Crm.ReportingServices2005.ReportingService2005.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties)
at Microsoft.Crm.Reporting.ReportServer.UploadReport(String path, Byte[] reportDefinition, String name, String description)
along with this error
Stack Trace Info: [MissingParameterException: The 'CRM_CalendarType' parameter is missing a value]
at Microsoft.Reporting.WebForms.ParametersArea.ValidateAllReportInputsSatisfied()
at Microsoft.Reporting.WebForms.ReportViewer.OnPreRender(EventArgs e)
This error may have many resons. The resoultion which help me in solving this problem was to first unjoin then again join report server machine to/from domain.
If above did not help in solving then do the following which is "totally not recommended" and "should not do" in production server
Allow annonymous access and select priviliged user / administrator in ReportServer's virtual directory -> properties -> Directory Security
If you get following exception while uploading / creating new report using Source Report Type -> Exising file
==============
[CrmException: Exception of type Microsoft.Crm.CrmException was thrown.]
Microsoft.Crm.Application.Platform.Report.InternalCreate(String xml) +721
Microsoft.Crm.Application.Platform.Entity.Create() +109
Microsoft.Crm.Application.Forms.AppForm.RaiseDataEvent(FormEventId eventId) +408
Microsoft.Crm.Application.Forms.EndUserForm.Initialize(Entity entity) +57
Microsoft.Crm.Application.Forms.EndUserForm.Execute(Entity entity) +13
Microsoft.Crm.Web.Tools.ReportProperty.ReportPropertyPage.ConfigureForm() +202
Microsoft.Crm.Application.Controls.AppPage.OnPreRender(EventArgs e) +30
==============
The issue lay with our RS permissions.
In addition to failing to upload reports, we tried downloading them from CRM too. This gave us an NT permissions error.
So, we opened up (localhost)/reports, navigated to the CRM datasource (typcially 'Organization_MSCRM), then properties, then security,
and then added a user / group called NT AUTHORITY\NETWORK SERVICE,
and gave them the permissions of CRM Publisher.
After that it all worked fine.
Thanks to Lee/Ronald
If you get following exception while uploading / creating new report using Source Report Type -> Exising file
=============
Stack Trace Info: [NullReferenceException: Object reference not set to an instance of an object.]
at Microsoft.Crm.Reporting.SRSReport.convertDataSource()
at Microsoft.Crm.Reporting.SRSReport..ctor(String xmlContent, String originalFilter, Boolean convertReportToCrm, ExecutionContext context)
at Microsoft.Crm.ObjectModel.ReportService.CreateInternal(IBusinessEntity entity, Boolean isScheduledReport, ExecutionContext context)
at Microsoft.Crm.ObjectModel.ReportService.Create(IBusinessEntity entity, ExecutionContext context)
=============
The reason can be the Shared Datasource used while creating Report in Visual Studio 2005.
1. Create a new Report Project in Visual Studio 2005
2. Right click on Reports, choose to add Existing Item
3. Double Click on the report to open it.
4. Click on the Data (top tab) above the open report file.
5. Click the "..." button just to the right of Dataset to open Dataset Properties.
6. Click on the "..." button to the right of the Data Source drop down list.
7. Clear the "Use shared data source reference" option.
8. Click on the "Edit..." button to the right of Connection string.
9. Enter the correct SRS Server Name, enter the authentication method, and choose the correct database name.
10. Click OK, this will change the source for the entire report.
11. Click OK to the Visual Studio window again.
12. Choose, File, Save <Report>.
13. Try to upload it again. This time it should load.
Thanks to mennotk
Today I tried to find out which table is taking much space in DB or to find the numbers of columns and rows in all tables of DB
Following query will return the required result
=========
USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused "?"'
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, 'KB', '') AS integer) DESC
DROP TABLE #temp
|
|