Restore With Standby; A Review and A Lesson Learned (2023-09-25)

SQLServerCentral Newsletter for September 25, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Restore With Standby; A Review and A Lesson Learned]( - [AI-900: Microsoft Azure AI Fundamentals Certification Exam Tips and Tricks]( - [“Oops, I Lost My Indexes in Azure SQL DB.”]( - [From the SQL Server Central Blogs - Yet another kind of orphan users in SQL Server]( - [From the SQL Server Central Blogs - Quickly Creating a tsqlt Test for a Query]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [The Upgraded Procedure part 2]( The Voice of the DBA  Are You Worried About AI? The AI revolution is happening. I don't know how successful it will be, but regardless, people are trying to get AI into everything. That might be good or bad in various situations, but it has people worried about AI. [This piece]( looks at a survey from the American Staffing Association that says 47% of people somewhat or strongly agree that their job is replaceable by automation with an AI. There is more data to dig into, but that's a lot of people who worry about their jobs. Most people do think that tech is generally a good thing. I do find that many of us in tech, and some other industries, are buried in work. There's never a shortage and we could easily work 60, 70, or more hours a week and not complete everything. The flip side of that is a lot of work doesn't necessarily need to be done now and could be delayed, so I think it's hard to decide whether we need more help or more patience. The various places I've seen AI used are often helping people get started or unblock their creativity struggles. Recently someone posted [this link on some prompts]( a person had used to get moving. I found a few of these possibly helpful. Not writing, but maybe having an AI proof something, or even get me started in some new area. I worry about book summaries, or even text summaries, as I'm not always confident the AI understands which pieces I might find important. However, that's also a danger with humans. I've seen no shortage of people asked to summarize something for others. When I understand the subject well, I sometimes disagree with another's summary (or think it's just plain wrong). I don't think AIs are a big danger to many people's employment. I do think that as people learn to use AIs are assistants we might find that we grow or enhance staff less. We might also find that the 10x engineer knows how to engage 10 AIs to help them get things done, especially simple, busy work. The low performers will likely be those working in the same way in the future as they do today. That might not threaten your job, but it might mean lower bonuses and less opportunities in the future if you can't adapt your skills to this new world. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Restore With Standby; A Review and A Lesson Learned]( Andy Warren from SQLServerCentral A lesson learned when trying to restore backups with standby. You can't upgrade versions. [External Article]( [AI-900: Microsoft Azure AI Fundamentals Certification Exam Tips and Tricks]( Additional Articles from MSSQLTips.com Microsoft has an exam related to Artificial Intelligence: Exam AI-900. It's related to the Microsoft Azure AI Fundamentals. Are there recommended materials to help pass this exam? [External Article]( [“Oops, I Lost My Indexes in Azure SQL DB.”]( Additional Articles from Brent Ozar Unlimited Blog I got an interesting request for consulting, and I’m going to paraphrase it: We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back? [Blog Post]( From the SQL Server Central Blogs - [Yet another kind of orphan users in SQL Server]( SQLPals from Mission: SQL Homeostasis Yet another kind of orphan users in SQL Server Or YAOU for short. (This silly acronym, I just made it up so don’t try... [Blog Post]( From the SQL Server Central Blogs - [Quickly Creating a tsqlt Test for a Query]( Steve Jones - SSC Editor from The Voice of the DBA One of the things that I feel is important to building better software is testing your code. It should be easy and simple to test code and determine if... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Upgraded Procedure part 2 I had a SQL Server 2014 database. In it, I created this function: CREATE FUNCTION OPENJSON (@json varchar(1000)) RETURNS TABLE AS RETURN SELECT CASE WHEN SUBSTRING (@json, 1, 1) = '{' THEN 1 ELSE 0 END AS json_string; This works, and this call: SELECT * FROM OPENJSON('{ "key":1 }') Returns this result: json_string -------------- 1 Now I restore this database on a SQL Server 2019 instance and set the compatibility level to 150. I run this code: SELECT * FROM dbo.OPENJSON('{ "key":1 }') What happens? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Deleting the Standby file I restore a database on SQL Server 2022 with the STANDBY option and include a file path and name. When is this file deleted? Answer: During the next restore operation on this database Explanation: Any restore operation on this database will delete the STANDBY file. Ref: RESTORE Arguments - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Public Role Permissions concern]( - All, I've been double-checking security for one of my servers and I found the below permissions on the public role. I'm trying to decide if those are default / system-granted permissions that are necessary for the functioning of SQL Server or if these might have been granted by someone some time ago. Does anyone know? […] SQL Server 2016 - Administration [SSRS on Alwayson Availability Group]( - Hi, Installed SSRS feature in Primary replica and configured ReportServer and ReportTEMPDB by using SQL listener name. ReportServer and ReportTEMPDB brought into Availability Database and two databases are synchronized between two primary and secondary replica. SSRS reports rdl files kept it Node1 and able access reports � working as expected and tested AG failover between […] SQL Server 2016 - Development and T-SQL [How to get total sum across the table and sum grouped by date ranges]( - Here is sample code and what I tried to achieve, but not getting the output, any advice? select k.charges, sum(case when k.range>='101' then charges else 0 end) as '101charges', sum(case when k.range>='201' then charges else 0 end) as '201charge' from (select charges,substring(daterange,1,3) as range from dbo.charges, )k   CREATE TABLE charges ( charges money […] [Converting file name MMDDYYYY.txt to a Date Field]( - Hello, Converting file name MMDDYYYY.txt to a Date Field but CONVERT or CAST do not seem to work perhaps because I also have a REPLACE? Can someone help me understand how to do this if it is possible? See sample code below and many thanks in advance! CREATE TABLE #t (getFileName varchar(100)) INSERT INTO #T […] SQL Server 2019 - Development [extracting data from JSON with TSQL]( - Hi, I need help extracting data from the below JSON. What I need is the data from the "frequencyData" array. on the first row of the resultset I would like the first column to contain the first data sample of the "frequency"child array, the second column should contain the first data sample of the "df" […] [Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)]( - Good morning experts, Over the past few releases, my team and I have been running into this issue where when we deploy an SSIS Project from one SQL Server to another we get this error on the "Changing Protection Level part: Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) We have about 15 projects […] [Script for deleting data from a table and corresponding FKTables]( - Hi, Looking for a script to delete data from a table and also from the FK tables of data exists. I have seen some online using procedures and function but just looking for a base script SQL Server 2008 - General [P2V os sql vm to new host]( - I have used P2V to replicate my sql vm to a new host alongside all its disks storage, the problem is that these disks are part of a failover cluster and when I try to bring them online on the new host the following error occurs: The disk must be in cluster maintenance mode and […] SSDT [Partial Model Project With Same DB Reference]( - Hello, I am trying to create an SSDT project (in VS2022) for a database whose objects are primarily owned by a vendor application. This application dynamically creates views that we interface with from 'under-the-hood' tables that have non human-readable names. We have some custom integration code including some procedures, views, and functions, which are in-house […] Analysis Services [How to connect SSAS TABULER with mysql data source]( - Hello I have a mysql data source and I want to use it as data source to my analysis service… I could connect it through ssis and ssrs using odbc data source but I face a hard time finding odbc or ado.net data source in my ssas… can anyone help me with steps and photos […] SQL Server 2022 - Administration [Kubernetes Hands-On Courses]( - Hello, All I appreciate it may not be directly relevant to SQL Server, but I will be grateful if someone can recommend me (a company I work for are ready to chip in) a good Kubernetes course with practical element in it, please. There are plenty of free courses over the internet or event on […] [SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC?]( - This is a scenario I haven't had to consider. We have a UTC time set SQL Server running jobs. We have set the jobs to run in based on UTC time converted from the timezone we're in (+10 hours) eg 6:45pm UTC (4:45am AEST) However one of the jobs calls a view that uses a […] SQL Server 2022 - Development [Both values between Start and End date]( - good afternoon, I'm trying to determine if both values (101 and 102) both occur within a 'Shift' (startdate/enddate). It originally just needed 101, but our facility added 102 and for the 'goal' to be compliant (1), they need to be both present within a shift. I'm having a bit of trouble working this out within […] [Creating a subquery which conflicts with the where clause...]( - Hi All, I have created a table named issues where incidents are being tracked. Every record has a master indicator column ("Y" or "N")which denotes that its the master incident. If the same incident type is created by another user, then the Master Indicator will be set to N and the master_issue column will be […] [Database Query Optimization: How to Improve Query Performance in SQL]( - I have a SQL database that stores a large amount of data, and I'm experiencing slow query performance when retrieving information. What are some strategies and best practices for optimizing database queries in SQL to achieve better performance? Here's an example query I'm working with: SELECT first_name, last_name, email FROM users WHERE registration_date >= '2023-01-01' […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -