I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2. My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of these is not an option.   How should I check?
122381
I've made a backup of a SQL Server database running on SQL Server 2008. I've restored that backup into a SQL Server 2012 instance. I've set the compatibility level on the restored instance to SQL Server 2012 (110).   What does this do to the database?   Does it, for example,    cause stored procedures to be examined for incompatibilities cause any updates to all of the data pages in the database Cause updates to to datapages as the are accessed in the future Cause errors in the future if stored procedures or views are executed that use incompatible syntax   Where in books on line, or else where, can I find more detail?
123114
My database in sql server 2008 has marked suspect.  How can I fix this problem?
39911
How do I set up criteria in Process Builder to detect Insert? I have tried setting Field to Id and Filter to IsChanged, WasSet, and IsNull, all to no avail. I want a process to take one action when a record is inserted, and make sure all subsequent criteria apply only on update.  Is this issue perhaps because I am trying to detect a change from null? I found  on the Ideas Exchange that may be relevant.
2401
Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
76944
I have a draft of requirements that look like this:   Production database should be copied in real-time to "Pre-prod" database The Pre-prod database should be available to read and update It should be possible to make schema and data changes to Pre-Prod Schema and data changes on Pre-prod should not effect Prod   The databases will be on the same SQL Server instance.  I'm wondering what approach to take? Transactional Replication seems the best option, but applying schema changes to a subscriber seems like a recipe for disaster.  Mirroring is out because the mirror will not be available; log shipping is out for similar reasons.  Are there any other technologies I should be considering?
85694
We are migrating our production data (DB and filesystem) from a fairly complex data model to another fairly complex data model. The migration process is given by the specification as a set of mapping rules and transformation functions.  I'm planning a testing strategy for this migration process. To identify the riskiest areas here, I've read some lessons learned from  and  in general and compared them with our migration process specification.   Where the things may go wrong?   For backward compatibility reasons, the new data will be provided by the new application to the old modules through adapters. Old modules will do their job using migrated data as they used. The new data model will support some new application features. The new data model will no longer support some old (usually used infrequently) application features.  Nullable fields in the old model are no longer nullable.  Files in the filesystem are defined in a different format. Data that used to be decoupled in the old system will now be joined  (e.g. customers with their projects) based on:   automatic rules administrator rules  New data formats will be used   What may go wrong?   Values for data used by new features may have incorrectly defaulted.  Adapter may translate new data to legacy format incorrectly. Not all entities may be migrated, e.g., some old client configurations cannot be found in the new system. Not all data (properties) may be migrated. New data model may use data formats may not be able to store some old data structure (e.g., VARCHAR vs. BLOB) Migrated files may not be readable for old modules etc.   My very first idea is to combine two testing strategies here:   Low-level testing strategy that will be relatively thorough but will have little coverage. The idea is to isolate a most representative sample of original data  (that makes sense for the business, e.g., single customer and its documents). Then, for each mapping rule define real expected output for the corresponding part of the sample data.  Automate checks. High-level testing strategy that will be less thorough but will cover the majority of application functionalities and try to identify integration problems with old modules. The idea is to migrate all data first. Then perform user test scenarios that involve all functionalities of the new application and of the old modules.   What other strategies would you suggest to discover bugs for the identified risks?
110465
I need to restore a SQL Server 2000 database to SQL Server 2016. Is there any possible way to do this without having to install a complete copy of SQL Server 2008, restore to 2008, change the compatibility level and then restore the 2008 backup to 2016? I know this would work but I really don't want to install 2008 R2 just for this single purpose unless I have no other choice.
23585
A dilemma probably faced by quite a few DBAs. Does one upgrade to the latest version, in this case SQL SERVER 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by now?  I will only be upgrading in Standard Edition and from what I've read there isn't much between 14 &amp; 16 in terms of functionality, enterprise is a different story.  The only things of interest to me at the moment in 2016 are probably the query store features and possibly the tabular BI stuff.  I have read 2016 does use fewer resources and depending on the tasks it executes faster, which is obviously a big bonus.  At the moment I am leaning towards 2016 as opportunities to upgrade are few and far between, just worried about potential bugs and applying them in 24/7 up time environment with no maintenance window.  Would be great to hear if anyone else has faced the same/similar upgrade path, and ultimately what decision they took.  I will probably get a down vote as there is no right answer...but its a question that must be asked a lot..
110185
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.  A number of the Stored Procedures have a pattern like this:   Create Temp table Build up dynamic SQL query to insert a bunch of records, e.g.  DECLARE @sql VARCHAR(MAX) SET @sql = 'INSERT INTO @tempTable SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'  IF @someParam = [SomeValue]     SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';  IF @someOtherParam = [SomeOtherValue]     SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';  Execute this dynamic sql  EXEC(@sql);  Select from the temp table and bring in a bunch of additional information to return to the client.  SELECT     ...  FROM     @tempTable    INNER JOIN ...    My immediate thoughts are:   There is dynamic SQL, so no cache plans, meaning plans generated every time. There is an INSERT SELECT pattern, so table locking is more likely to be an issue.   I have re-written some of the Stored Procedures in this way instead:      SELECT          ...      FROM          MyTable          INNER JOIN ...      WHERE      (         @someParam != SomeValue         OR         SomeColumn = SomeValue     )     AND     (         @someOtherParam != SomeOtherValue         OR         SomeOtherColumn = SomeOtherValue     )           From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.  I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.  Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?  The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.
2165
What is a  free tool to compare two Sql Server tables (data and schema).   It would be great if the tool can script the differences found.  I also went through some older . The closest I have seen is  but I would love to try more options.
49798
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select statements from this table.   Under default isolation settings (READ COMMITTED I think?), T1 blocks any select statements from running until the transaction commits or is rolled back.   What I'd like to see is for the select statements to function on consistent data even while the transaction is underway. I believe SNAPSHOT isolation can help, but am not sure if I'm going in the right direction. Would this be the best isolation level for this application?  Secondly, I don't have any control over the process that is calling the select statements, but I do have control over the .NET application that calls T1. Would any isolation level changes be required on both the select statements and T1, or would it be sufficient to mark  just T1 as having a different isolation level?
97686
I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from various places I think the setup is quite OK.  One element in the performance does puzzle, hence I'm reaching out here. The database that I'm testing against was built up historically and was originally created against 2008r2. The compatibility setting was carried over from that version. I've raised that setting last week in two stages, but when I raised it from 2012 to 2014 compatibility, all of sudden the CPU load went through the roof.  When changing this setting back an forth it seems to be systemic. I can run X simulated users (web app in front of the DB) on an 8 core database with the 2012 setting. If I raise it to 2014 I only get away with 60% of X simulated users. And the other way around for X users the batch requests number is 40% lower for the 2014 setting.  As I understand, switching compatibility level back doesn't revert the DB but only turns off features. This situation seems to point to a feature that eats CPU with no apparent gain, so I was wondering which feature that could be and if there's a way to turn of that specific feature separately?  Other posts on serverfault have pointed towards rebuilding the indexes after an upgrade, I've done so, but that doesn't really seem to make a difference.  It's probably important to note that the application at hand generates a lot of queries that are not prepared statements. This is probably leading to the high number of compilations I see in perfmon (compilations/batch requests : 0.25). I can't do too much about that, so telling me to do something about that first won't really help me forward.
38778
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:  Restore failed for Server '...\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600.  That version is incompatible with this server, which is running version 10.00.2531.  Either restore the database on a server that supports the backup, or use a backup  that is compatible with this server. (Microsoft.SqlServer.Smo)   Is there a way to get a backup which is compatible with the older (in my case 10.00.2531) version from the newer (in my case 10.50.1600) version of SQL Server Express?
55253
How do I check to see if a column exists in a SqlDataReader object?  In my data access layer, I have create a method that builds the same object for multiple stored procedures calls.  One of the stored procedures has an additional column that is not used by the other stored procedures.  I want to modified the method to accommodate for every scenario.  My application is written in C#.
97757
I'm trying to create an SQL query that will order the results by a version number (e.g. 1.1, 4.5.10, etc.)  Here's what I tried:  SELECT * FROM Requirements      WHERE Requirements.Release NOT LIKE '%Obsolete%'      ORDER BY Requirements.ReqNum   Now, the ReqNum field is a string field and unfortunately I can't change it to a float or something like that because I have requirement numbers like 162.1.11.  When I get the results back, I'll get ordering like this:  1.1 1.10 1.11 1.3   How can I write a query that will sort by lexicographic order?   ... or,  How can I correctly sort the data?  Thanks for the input in advance!
6636
I am using , and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.   Do I need to create linked servers on the central instance to connect to the other instances, or is there some other way to input the server names into the sp_Blitz stored procedure?
69005
Need help if anyone can offer! I currently have my select statement returning the TOP 1000 * results from a table. They are CC numbers that are currently active. I'd like to see how I can go about writing a query that could show me what has CC numbers have changed. If something has changed, it will send a report to the listed people. If nothing, then it does nothing. It only actions once something has happened. We have an inhouse system that will do the automated part... I just need a select statement that will return what has changed. If I can get some help on this, it would be amazing! Thank you!