August 11, 2010 at 12:38 pm
Hi All,
I have just come across unusual issue with two different editions of Sql Sever 2008.
I created a back of my database in SQL Server 2008 Enterprise then tried to restore it in another machine having Sql Server 2008 Standard. But it gave me an error.
Then I tried to restore on another machine having SQL Server 2008 Enterprise installed on it and it got restored. Again I took another machine having Standard SQL but time it gave error again.
For curosity I did reverse. I created back in SQL Server 2008 Standard and tried to restore in Enterprise and it failed same as Enterprise to Standard. Then I tried same backup to restore from Standard to Standard and it restored without any problem.
Is it not possible to restore a back from Enterprise to Standard and vice versa editions of sql server 2008?
August 11, 2010 at 12:51 pm
Should be possible. What were the errors?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2010 at 12:01 am
Sorry for missing the attachment of error screen. I have attached that now as RestoreError.zip. See in Attachments.
Please have look at it and let me know your opinion.
August 12, 2010 at 1:18 am
vishkk47 (8/12/2010)
Sorry for missing the attachment of error screen. I have attached that now as RestoreError.zip. See in Attachments.Please have look at it and let me know your opinion.
From the error it seems that the mdf file is being used by another database. you can do a restore database with move command which will allow you to restore the files to a different location / name.
August 12, 2010 at 11:47 pm
Even I thought this and tried restoring the backup after stopping the source sql server from which I took my backup but the same error persisted.
Regards
Vishal
August 13, 2010 at 4:45 am
Is the database partitioned? From the error message it looks like it may be (both NRJData_Data3 and NJRData_Data claiming MDS2DataED.mdf). If this is the case then it may be because SQL Server 2008 Standard Edition does not support partitioning whereas Enterprise Edition does.
August 14, 2010 at 5:47 am
The database is using 4 data files and one log file. To tell you more I haven't created this database myself, it was already there for existing applications. So I am not sure whether its partitioned or not.
So how to check whether database is partitioned or not?
how can one take backup and restore the partitioned database across the editions of sql server?
It's quiet shocking to know that SQL Server Standard does not support partitions.
Thanks.
August 14, 2010 at 11:19 am
Two things. First, would you please post the SQL for your restore. Second, could you script the SQL to create your database and post that as well.
August 15, 2010 at 11:31 pm
I am not using any sql script. I used SQL Management Studio and right clicks for backing up and restore as well.
While backing up I had selected Backup Type as Full, Recovery Model as Simple and Overwrite existing backup set was also checked. All other options I left as default.
For Restore I only checked Overwrite existing database.
Please let me know if I need to check/uncheck any related option(s) to avoid this error.
For your reference I generated the sql script for Create Database:
USE [master]
GO
/****** Object: Database [MDS2Data_Local] Script Date: 08/16/2010 11:02:15 ******/
CREATE DATABASE [MDS2Data_Local] ON PRIMARY
( NAME = N'NJRData_Data', FILENAME = N'D:\HealthDatabase\MDS2Data_Local.mdf' , SIZE = 605504KB , MAXSIZE = 6145024KB , FILEGROWTH = 5%),
( NAME = N'NJRData_Data3', FILENAME = N'D:\HealthDatabase\MDS2Data_Local_1.mdf' , SIZE = 16192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'NJRData_Data2', FILENAME = N'D:\HealthDatabase\MDS2Data_Local_2.mdf' , SIZE = 2097152KB , MAXSIZE = UNLIMITED, FILEGROWTH = 5%),
( NAME = N'NJRData_Data4', FILENAME = N'D:\HealthDatabase\MDS2Data_Local_3.mdf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'NJRData_Log', FILENAME = N'D:\HealthDatabase\MDS2Data_Local_log.ldf' , SIZE = 1536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [MDS2Data_Local] SET COMPATIBILITY_LEVEL = 80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MDS2Data_Local].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [MDS2Data_Local] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MDS2Data_Local] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MDS2Data_Local] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MDS2Data_Local] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MDS2Data_Local] SET ARITHABORT OFF
GO
ALTER DATABASE [MDS2Data_Local] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MDS2Data_Local] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MDS2Data_Local] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MDS2Data_Local] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MDS2Data_Local] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MDS2Data_Local] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MDS2Data_Local] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MDS2Data_Local] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MDS2Data_Local] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MDS2Data_Local] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MDS2Data_Local] SET DISABLE_BROKER
GO
ALTER DATABASE [MDS2Data_Local] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MDS2Data_Local] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MDS2Data_Local] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MDS2Data_Local] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MDS2Data_Local] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MDS2Data_Local] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MDS2Data_Local] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MDS2Data_Local] SET READ_WRITE
GO
ALTER DATABASE [MDS2Data_Local] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MDS2Data_Local] SET MULTI_USER
GO
ALTER DATABASE [MDS2Data_Local] SET PAGE_VERIFY NONE
GO
ALTER DATABASE [MDS2Data_Local] SET DB_CHAINING OFF
GO
Thanks.
August 16, 2010 at 1:41 am
See it seems to be some file mismatch issue.
Try below
1. Backup destination db.
2. Delete this db. (Anyhow this will be overwritten)
3. Restore the db using move options if you want to create your physical files somewhere else.
4. Check foe orphaned users.
You are done...
Tell me if this helps.
Rohit
August 16, 2010 at 3:10 am
Apologies, I may have introduced an unnecessary "thought" by bringing partitioning into this thread. Partitioning[/url] is applied at the table level and the different partition schemes linked to different filegroups. However, a database without partitioning can still use more than one filegroup.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply