SQL Server Express Edition and Its Limitations

SQLServerF1

Microsoft SQL Server Express Edition is a free and feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs.

Express (SQLEXPR) – Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.

Express with Tools (SQLEXPRWT) – This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server Management Studio for 2014 version. Choose either LocalDB or Express depending on your needs.

SQL Server Management Studio Express (SQLManagementStudio) – This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure for SQL Server 2014, full version of SQL Server Management Studio for SQL Server 2014, etc. This can be used if you already have the database and only need the management tools.

Express with Advanced Services (SQLEXPRADV) – This package contains all the components of SQL Server Express including the SQL Server Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

Although SQL Server Express Edition is great for low cost applications, but it has many limitations too as mentioned below.

Maximum Number of CPUs – SQL Server 2005 and SQL Server 2008 R2 Expression Editions can only use one CPU. Starting with SQL Server 2012, it can be either 1 Socket or 4 cores which ever is lesser.

Maximum Memory – SQL Server 2005, 2008 and R2 Express Editions can only use up to 1 GB of memory per instance. If you install 2 instances of SQL Server Express Edition on the same server, then each one can use up to 1 GB RAM. Start with SQL Server 2012 and 2014 SQL Server Express Edition can use 1.4 GB for Database Cache and other caches can use some more memory, so up to 2 GB can be used but 1.4 GB for data and index pages.

Maximum Size of Each Database – SQL Server 2005 Express Edition can have each database only to a maximum of 4 GB size, however this has been extended to 10 GB starting from SQL Server 2008 or higher.
This 4 GB for SQL 2005 and 10 GB for SQL 2008 or higher is only for data file, but not for Log files or FileStream data. So in SQL Server Express Edition a database can be of more than 10 GB size as the data file may be less than 10 GB and transaction log file or filestream data may be of any size making it look larger.

Maximum Number of Databases Per Instance – There are no limits to the number of databases that can be attached to the server.

Maximum Number of Connections – There is no limit on number of connection for SQL Server Express Edition, so it will default to 32,767, but you will not be able to use these many connections as before that you will hit one of the other above mentioned limitations.

There are other limitations which include that SQL Server Express Edition cannot be used in features like Log Shipping, AlwaysON Availability Groups, Alwayson Failover Cluster Instances, SQL Server Failover Cluster Instances, Online Indexing, Page level restores, Fast Recovery, etc. Almost SQL Server Express Edition does not have any features other than just a basic database engine.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Install SQL Server Analysis Service due to The folder path specified is invalid Error

SQLServerF1

I have been trying to install SQL Server Analysis Service as standalone default instance and it kept failing with the below mentioned error. It reports this error for Data, Log, TempDB and Backup folders.

The folder path specified is invalid. Please enter a valid folder path. Folder Path = “C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data”.

As part of troubleshoot process, I have tried below options.

– Ran the setup by right clicking on the setup and choose “Run as Administrator” but still I hit the same error.

– I have granted Local administrator permissions to My account and SQL Server Analysis Service account, but still got the same error.

– I deleted the folder MSAS11.MSSQLSERVER from below path and attempted the install, but still had the same error.
C:\Program Files\Microsoft SQL Server\

– Copied the SQL Server setup media to local drive.

– Made sure there is no disk corruption.

– Verified and the disk or the folders are not compressed nor encrypted nor Read_Only.

– Disabled Anti-Virus and rebooted the server, but still had same error.

– Researched about this error, but did not find any conclusive solution.

– Later I have downloaded another latest SQL Server Media from the MSDN site and extracted the content using Magic ISO tool and ran the installation by “Run as Administrator” and this time the installation proceeded further and completed successfully.

So, the issue here appears to be with the SQL Server Setup Media which seem to be buggy or corrupted.

If above solution does not work for you, then you may first see if even Analysis Services is required to be installed and is it being used by any users or applications, if not, you can just avoid the installation altogether.

What is SQL Server Analysis Service?
Analysis Services is an online analytical data engine used in decision support and business intelligence (BI) solutions, providing the analytical data for business reports and client applications such as Excel, Reporting Services reports, and other third-party BI tools. A typical workflow for Analysis Services includes building an OLAP or tabular data model, deploy the model as a database to an Analysis Services instance, process the database to load it with data, and then assign permissions to allow data access. When it’s ready to go, this multi-purpose data model can be accessed by any client application supporting Analysis Services as a data source.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Upgrade Advisor Error Requested registry access is not allowed. (mscorlib)

SQLServerF1

SQL Server Upgrade Advisor is used by DBAs to Analyze and Evaluate the problems that can arise due to upgrading SQL Server to the specified version. Upgrade Advisor for the specified SQL Server version to which we want to upgrade needs to be downloaded and installed on the SQL Server machine where the SQL Server instance resides(We can install upgrade advisor or other servers and connect remotely to other SQL instances, but best way would be to install on same server or on Test server which is a similar replica of production server).

Mostly Upgrade Advisor runs fine without any problems, but at times you may face some errors. One of the issue we faced with running upgrade advisor on a SQL Server instance is as mentioned below.

Requested registry access is not allowed. (mscorlib)
——————————
Program Location:

at System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name)
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo()
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetSqlInstances()
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.EngineExists()
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.GetAnalyzer(AnalyzerType analyzerType)
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.RunAnalyzer(AnalyzerType analyzerType, String& reportFile)
System.Security.SecurityException: Requested registry access is not allowed.
The Zone of the assembly that failed was: MyComputer

– The above error can occur if the account which is used to run the Upgrade Advisor is not an Administrator account.
– RDP to the server using a local administrator account and this account needs to be part of SysAdmin server role in SQL Server.
– Launch Upgrade Advisor by right clicking on it and choose “Run as Administrator” option on Windows Server 2008 or higher.
– Make sure you use the Upgrade Advisor of same platform. For SQL Server x64 instance, use x64 Upgrade Advisor.

Purpose of SQL Server Upgrade Advisor
SQL Server Upgrade Advisor helps us in preparing for upgrades from lower version to higher versions of SQL Server. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues and the objects affected to be fixed either before or after you upgrade.

How to run Upgrade Advisor

– Install SQL Server Upgrade Advisor for SQL Server version to which you are planning to upgrade.

– Click Start, point to All Programs, point to Microsoft SQL Server (version) , and then click SQL Server (version) Upgrade Advisor.

– Provide with Server Name and select the required components.

– Click on Next, Choose SQL Server Instance Name and the authentication mode.

– Verify or Provide Login Credentials provided is asked.

– Select the Databases to analyze and Click Next.

– Click Run which will start the analysis and once completed will be provided with Report.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

CPU Power Plan Setting Best Practices for Servers Running SQL Server instances

SQLServerF1

Starting with Windows Server 2008 R2, Microsoft has introduced option for customizing the CPU Power Plan. By default, Windows Server 2008 R2 or higher comes with Balanced Power plan which basically means, that the processor will be using less power thus the performance is slower for regular workloads on the server. When ever there is higher work load on the system, the processor increases its speed, but this change from lower speeds to higher speeds is not very fast, thus can cause problems for applications which immediately require high speed immediately. There is another power plan which is “High Performance” which basically run the processor with high speed and with high performance all the time, so there is no need to switch the processor speed from lower to higher.

SQL Server is an application which relies heavily on server resources like CPU, Memory, Disk IO and Network. SQL Server generally benefits with servers running with “High Performance” power plan, so that when the SQL Server work load increases there is no issues noticed.

Although this setting looks simple, it is very effective and can dramatically show the improvement in performance and I have seen this on multiple servers and multiple environments. Most of the Article, Blogs, MVPs suggest to set CPU power plan to “High Performance”. On servers where High CPU usage is noticed, it is important to make sure this option is set to “High Performance”. Also, if you notice queries taking more time during high load and queries showing waits related to CPU, then it is good to check this setting and set it to “High Performance”.

There are various places where we may need to change the power plan settings to “High Performance” for it to take effect.

– In Windows Server 2008 R2, you can find the Power plan Options from Control Panel or by typing powercfg.cpl from Run window. There are three default power plans namely Balanced, Power saver, and High performance. By default, Windows Servers are set to Balanced, but as mentioned before the default Balanced power plan is not the best choice for database server performance. So, we need to change the power plan to “High Performance” from here.

– There may be servers which have power plan at BIOS level and it take precedence If Power options are enabled at BIOS level, then work with System Administrators and either disable this from BIOS or turn this on from BIOS.

– On Virtual servers, VMWare or Hyper-V, it is important that this Power plan setting to changed at individual guest VM systems and also at the ESX host server hosting the VMs. Also, important to note that this may need to be changed from control panel and also from BIOS on VM Guest and Host servers.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Send DAILY E-Mail Report with SQL Server Agent Job History with Status, StartDate, EndDate and Duration

DBAs create various jobs and schedule those jobs and the jobs that are created may be related to regular maintenance activities like Database Backups, Index Rebuild or Index Reorganization, Database Integrity and Consistency Check, Application related jobs running queries or Stored Procedures, SSIS packages, etc. In most of the environments there may be automated monitoring tools which raises an alert or ticket when there is any job failure and DBA’s take appropriate action.

Sometimes DBA’s are asked to send Daily or regular reports related to jobs to management or Developers or other teams that may require list of all jobs which were running since last 24 hours(or different threshold) along with their job run status, job start date and time, job end date and time and job run duration. There may be request to send this data regularly, so manually getting these details can take lot of time especially when there are many SQL Server instances and jobs.

Below are the scripts which generates a quick report and sends E-Mail with list of job names, Job Step Name, Last Job Start Date, Last Job End Date, Last Job Run Status, Last Job Run Duration, and description of the job if available for past 24 hours.

First create the below Stored Procedure which is used to generate a report and we will be using this SP to send E-Mail later in the second script


CREATE PROC DailyJobReport
AS
BEGIN
SET NOCOUNT ON

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,
EndDate datetime, RunStatus int, JobModifiedDate datetime, JobEnabled nvarchar(10) default NULL)

Declare @query1 nvarchar(1024)
Set @Query1 = 
'SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime, 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime, run_status,
j.Date_Modified, j.Enabled
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id and 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) >
DATEADD(HOUR, -24, GETDATE())
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
--print @query1
Insert into @temp_jobhistory Exec sp_executesql @query1
select JobName,JobStepName,StartDate,EndDate,  
CASE 
      WHEN RunStatus = 0 THEN 'Failed' 
      WHEN RunStatus = 1 THEN 'success' 
      WHEN RunStatus = 2 THEN 'Retry' 
      WHEN RunStatus = 3 THEN 'Canceled' 
      WHEN RunStatus = 4 THEN 'In progress' 
   END as JobStatus, 
DATEDIFF(minute,StartDate,EndDate) as 'DurationMinutes'
from @temp_jobhistory

END



This below script will be sending E-Mail to mentioned recipients. Read the comments in below code and make changes where ever required specific to your environment.

Add the below code to a SQL Server job and schedule it as per your requirement

--Database Mail needs to be configured for this to work
--Make sure to change the E-Mails addresses to the list which you want the e-mail to be sent
--Make sure to change the profile name configured for Database Mail feature in SQL Server

DECLARE
  @date varchar(11),
  @profile_name sysname,
  @recipients varchar(max),
  @subject nvarchar(255),
  @body nvarchar(max),
  @query nvarchar(max),
  @execute_query_database sysname,
  @query_attachment_filename nvarchar(255)

SET @date = convert(varchar(11), getDate()-1, 106)  -- DD Mon YYYY

SELECT
  @profile_name = 'DBA Alert Database Mail Profile',
  @recipients = 'DBA@MyCompany.com;Mgmt@MyCompany.Com;Developers@MyCompany.com',
  @subject = 'ServerName Daily Jobs Report - ' + @date,
  @body = 'Please see the attached report for ' + @date+'.' + char(13) + char(10) +
	  'These are the job run status in the SQLInstanceName instance for last 24 hours.' + char(13) + char(10),
  @query = 'exec DailyJobReport',
  @execute_query_database = 'master',
  @query_attachment_filename = 'SQLInstName ' + @date + ' DailyJobReport.csv'

EXEC msdb..sp_send_dbmail
  @profile_name = @profile_name,
  @recipients = @recipients,
  @subject = @subject,
  @body = @body,
  @execute_query_database = @execute_query_database,
  @query = @query,
  @attach_query_result_as_file = 1,
  @query_attachment_filename = @query_attachment_filename,
  @query_result_width = 9999,
  @exclude_query_output = 1,
  @query_result_no_padding = 1,
  @query_result_separator = '	' ,
  @append_query_error = 0,
@query_result_header =1;

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Database Restore Failing on Server on Another Network

SQLServerF1

This is one different issue I found in MSDN forum, thought to BLOG it so that it can be helpful to others as well who face this kind of issue.

SQL Server 2008 R2 database backup was performed on a server and then the backup file is copied over to a VMDK disk which is accessible to the server. Later this VMDK file is attached to another server in a different network which does not have access to the server where backup was performed. Backup file is now copied from the VMDK server into the new server and when Restore of database is attempted, then the restore runs for some time and then throws an error that the restore operation failed. Below is more detailed error message returned by the restore.

Msg 3203, Level 16, State 1, Line 1
Read on “D:\TestDB.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
Restore database is terminating abnormally.

Initially the suspect was that the backup may be corrupt, so the backup file was copied to another server directly without the use of VMDK and the restore was successful, so this narrowed down the problem to be more related to copy of the backup file from server to VMDK and from VMDK to new server, some where it is getting corrupted.

The solution to this problem was to copy the backup file by using the option /j for Xcopy command

/j option Copies files without buffering. Recommended for very large files. This parameter was added introduced in Windows Server® 2008 R2.

Once the backup file was copied using the /j option there was no more corruption related to the backup and the restore completed successfully.

Read on failed: 13(The data is invalid.) error generally occurs if the backup file is corrupted or the disk where we are trying to backup or restore is having consistency problems.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Connect to SQL Server Embedded edition Instance Used by SharePoint

On one of the servers, we started receiving alerts related to SQL Server instance MICROSOFT##SSEE. DBAs were not aware of this SQL Server instance as none of the DBAs have installed it on the server. We tried to connect to this SQL Server instance from SQL Server Management Studio, but it returned below error.

Cannot connect to SERVER\MICROSOFT##SSEE.
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

After a quick search, found that this instance is related to Windows Internal Database. SSEE refers to SQL Server Embedded edition as well in previous versions. This is generally gets installed and used by Windows Server Update Services (WSUS) or Windows Sharepoint Services (WSS).

At times, there can be requirement to connect to this particular instance to manage or administer using SQL Server Management Studio (SSMS) by DBAs, however we cannot connect to it using TCP/IP or SharedMemory protocols, we would need to use NamePipes to connect to this instance.

We will need to use the below name in the server name box in SQL Server Management Studio (SSMS)
np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

If you do not have SQL Server Management Studio installed on the system, then best way to go would be to download and install SQL Server Express Edition Management Studio (SSMS). Once installed, we can use the above mentioned command to connect to this instance and administer it if required.

What is Windows Internal Database?
Windows Internal Database is a relational data store used by the following applications and services.
Active Directory Rights Management Services (AD RMS)
Windows Server Update Services
Windows System Resource Manager

These applications and services share a single instance of the data store. Windows Internal Database is installed by the first application or service that requires it. Because multiple applications and services use the same instance of Windows Internal Database, removing these applications and services does not remove the data store. If you have removed all applications and services that use Windows Internal Database, you can then remove it by using the Server Manager Remove Features Wizard.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

CTRL+R to Toggle the Results Pane Not Working Properly in SQL Server Management Studio (SSMS)

SQLServerF1

It is common for SQL Server DBAs to use SQL Server Management Studio (SSMS) for managing and administering SQL Server instances. SSMS offers variety of shortcuts to enable quick access to certain functions, one of such is the use of CTRL + R which can be used to Hide the results window or to show the results pane.

Recently, While I was using SQL Server 2014 Management Studio (SSMS) and when I tried to use the CTRL + R button, the results window was not getting cleared or not getting disappeared which ideally should. At the bottom left corner, I noticed a message that says “CTRL+R) was pressed. Waiting for second key of chord…”

The message displayed does not make any sense. After quick search, I found that this option can also be enabled from Menu options in SSMS. So, I clicked on “Window” menu option, but did not find the “Hide Results Pane” or the “Show Results Pane” options. After further research, found KB article which explains this issue to be caused because the .vssettings file for SQL Server Management Studio is corrupted.

However, this article says this is applicable for SQL Server 2012, but the issue happened for SQL Server 2014 SSMS. I went ahead and tried the below work around suggested in the above mentioned article and after which (CTRL + R) started working fine.

– Opened the SQL Server 2014 Management Studio.
– From the menu bar, clicked on “Tools”, and then clicked on Options.
– In the tree-view pane on the left side of the dialog box, clicked Keyboard.
– Clicked on “Reset” to reset the default keyboard mapping scheme.
– Next, In the dialog box that appeared, clicked on “Yes” and finally clicked on OK.
– Restarted SSMS and then I was able to use (CTRL + R) to toggle Results pane without any issues.

Use and Importance of SQL Server Management Studio
SQL Server Management Studio (SSMS) is used by DBAs and Developers for accessing, configuring, managing, development, and administering of SQL Server instances. SSMS combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SSMS works with all components of SQL Server such as Reporting Services and Integration Services. Developers get a familiar experience, and database administrators get a single comprehensive utility that combines easy-to-use graphical tools with rich scripting capabilities. SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Using Lower Version SSMS to Connect to Higher Version of SQL Server Instance

SQLServerF1

SQL Server Management Studio (SSMS) is one of the most useful and important tool for SQL Server DBAs to manage the SQL Server instances. Without SQL Server Management Studio (SSMS), it will be very difficult for DBAs to perform their daily duties or troubleshooting any issues with SQL Server instances.

One of the doubt or question many DBAs or Developers have regarding the SQL Server Management Studio is that weather they can connect to a SQL Server Instance of higher version using the SQL Server Management Studio (SSMS) of lower version.

For Example, Can we cannot to SQL Server 2014 instance using the SQL Server 2012 SQL Server Management Studio (SSMS)?

Yes, we can definitely connect to lower higher version of SQL Server instance using the lower version SQL Server Management Studio (SSMS). In our example, we can certainly connect to SQL Server 2014 instance using the SQL Server 2012 SQL Server Management Studio (SSMS), however certain features cannot be used or does not work. With every version of SQL Server, new features are introduced, so the old SSMS cannot know about the new features, thus fails while accessing the new features from old Management Studio.

As a best practice, it is always good to use latest version to SSMS to connect to any version of SQL Server instance. Latest version of SSMS will have no problem connecting to lower versions of SQL Server instances. You can access all features of lower version SQL Server instance features using the higher version SQL Server Management Studio (SSMS). If there are any features which are retired or removed in the new version of SQL Server, still the Management Studio mostly will work when using those features for lower version SQL Server instances. In special cases, we may have to use lower version SSMS to connect to lower version SQL Server instances.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Restore SQL Server 2000 Database Backup to SQL Server 2012 Instance

SQLServerF1

Although SQL Server 2000 is out of support, still there are some legacy applications still using SQL Server 2000. As there are many latest versions of SQL Server available in the market including SQL Server 2012 and SQL Server 2014 being the latest.

User may think that they can migrate directly from SQL Server 2000 to either SQL Server 2012 or SQL Server 2014, but it is not possible. We cannot even restore a database backup performed on SQL Server 2000 on to an instance running SQL Server 2012 or SQL Server 2014.

Below is the error which you will receive if you try to restore SQL Server 2000 backup file to SQL Server 2012.

Restore of database ‘DBName’ failed . (miscrosoft.sqlserver.management.relationalenginetasks)
The database was backed up on a server running version 8.00.0194. That version is incompatible with this server, which is running version 11.00.2100.

So, in order to resolve this issue, you will first need to restore this database on SQL Server 2005 or SQL Server 2008 R2 SQL Server instance and then perform backup of this database from the new instance and restore it on the SQL Server 2014 instance.

This error “The database was backed up on a server running version **.**.****” can also occur if you are trying to restore a database which was backed up on hither version and then trying to restore on the lower version of SQL Server instance. Example, this error can also occur if you try to restore a database which was backed up on SQL Server 2008 R2 and then you try to restore it on SQL Server 2008(non R2) instance.

This is applicable on below versions of SQL Server

SQL Server 2000
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4 5 8