Using SQL Server Dynamic Online Catalog to Solve Problems: Stairway to Exploring Database Metadata Level 2 (SQLServerCentral 5/30/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Using SQL Server Dynamic Online Catalog to Solve Problems: Stairway to Exploring Database Metadata Level 2]( - [Stairway to Server Management Objects (SMO) Level 1: Concepts and Basics]( - [Free eBook: Defensive Database Programming]( - [PowerShell Day-to-Day Admin Tasks – Part 6: Real-Time IT Dashboard]( - [Azure PowerShell – List Data Center Locations]( (From the SQLServerCentral Blogs) - [Azure SQL Database / SQL Server – Paused Index Rebuild]( (From the SQLServerCentral Blogs) Featured Script - [SQL Server Long Query Script]( The Voice of the DBA How Far Back? At the Redgate SQL Privacy Summit recently, there was panel I hosted that I thought was really interesting. At one point someone asked a question and there was a bit of a discussion about the right to forget in the EU. One of the fascinating things wasn't about the rights, but about backups. One of our panelists noted that we often keep too many backups, most of which are really useless. This portion of the discussion started by asking how many backups people keep. Many of those in the audience kept many backups, sometimes dozens. Many of them had some rotation scheme that determined how many files were kept over time. I know I've worked in places where we kept over a week or two worth of backup files, often along with a monthly or quarterly backup for a year and then multiple year end backups in perpetuity. I guess since we didn't really know how long those backups were valid as we just marked some tape as the end-of-year one and left it in a vault somewhere. What was also interesting is the panelist asked if many people had restored a backup more than a month old. Almost no one had, but a few had restored a year old backup. I think that's common, and I find I've only restored one backup more than a month old. That one was actually for a legal case, and it was six or seven years old. I actually had to find someone at Microsoft that could provide us with an old version of an SP to restore the database. I'm wondering today about the rest of you. Not if you have done it, but would you do it today? How far back would you go for a backup of an important database? Assume this isn't any requirement to find the data at a point in time, but for recovery purposes in a disaster. For me, I'd expect that most of the systems I've managed really would have struggled to go back a week. After that point in time, so many transactions are missing that there are serious repercussions. This is why I've often ensured I had automated restores happening all the time, since I need to be sure that we can restore from yesterday or two days ago. If I went back more than a week, it would be because we had corrupt backups. While the business would probably survive, I'm not sure my job would. If I hadn't been able to produce a workable backup for the last week, I think most organizations would be justified in terminating me. Whether they would or not is another debate. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 4.4MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [GDPR](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180530-slot1) How to make your SQL Server development GDPR ready Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. [Find out more about how our tools help with GDPR compliance](www.red-gate.com/gdpr?utm_source=ssc&utm_medium=pubemailad&utm_content=gdpr-ready&utm_campaign=GDPR&utm_term=20180530-slot1) [SQL Monitor]( Don’t just fix SQL Server problems, prevent them from happening SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. [Download SQL Monitor now and get a 14 day free trial]( Featured Contents  [] [Using SQL Server Dynamic Online Catalog to Solve Problems: Stairway to Exploring Database Metadata Level 2]( Phil Factor from [SQLServerCentral.com]() In this second level of the Stairway, Phil looks at where we go to find information about objects, using triggers as the example.[More »](Series/145317/) ---------------------------------------------------------------  [] [Stairway to Server Management Objects (SMO) Level 1: Concepts and Basics]( Laerte Junior from [SQLServerCentral.com]() Learn how to get started working with SMO and PowerShell.[More »]( ---------------------------------------------------------------  [] [Free eBook: Defensive Database Programming]( Press Release from [Redgate]() Resilient T-SQL code is code that is designed to last, and to be safely reused by others. The goal of defensive database programming, the goal of this book, is to help you to produce resilient T-SQL code that robustly and gracefully handles cases of unintended use, and is resilient to common changes to the database environment.[More »]( ---------------------------------------------------------------  [] [PowerShell Day-to-Day Admin Tasks – Part 6: Real-Time IT Dashboard]( Additional Articles from [SimpleTalk]() Creating a real-time IT dashboard is essential for IT professionals for managing their environments and to be proactive. A Few months ago, a great tool called ‘PowerShell Universal Dashboard’ or ‘PoshUD’ was officially released. In this article, Nicolas Prigent describes PoshUD, and how to use it to monitor your infrastructure using PowerShell.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Azure PowerShell – List Data Center Locations]( Bradley Schacht from [SQLServerCentral Blogs]( Occasionally I will come across the need to use PowerShell for my day to day activities. One such need came...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Azure SQL Database / SQL Server – Paused Index Rebuild]( Arun Sirpal from [SQLServerCentral Blogs]( I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when...[More »]( Question of the Day Today's Question (by Steve Jones): I have a data frame that looks like this: > HR.hitters rank players Hr 1 1 Barry Bonds 762 2 2 Hank Aaron 755 3 3 Babe Ruth 714 4 4 Alex Rodriguez 696 5 5 Willie Mays 660 6 6 Ken Griffey, Jr. 630 7 7 Albert Pujols 619 8 8 Jim Thome 612 9 9 Sammy Sosa 609 10 10 Frank Robinson 586 I want to get just the rows that have players who have hit more than 660 home runs (Hr) and return the entire row. What should I write? Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: R Language. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Expert Scripting and Automation for SQL Server DBAs]( Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): What are the Sources for the Data Migration Assistant? Answer: SQL Server 2005 through SQL Server 2017 Explanation: The sources are SQL Server 2005 through SQL Server 2017, meaning these versions: - SQL Server 2005 - SQL Server 2008 - SQL Server 2008 R2 - SQL Server 2012 - SQL Server 2014 - SQL Server 2016 - SQL Server 2017 on Windows Ref: Data Migration Assistant - [click here]( --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [SQL Server Long Query Script]( Yusuf Kahveci from [SQLServerCentral.com]() In general, I'm going to give you a script that will give you access to the data that everyone is curious about and wants to learn. What is the longest running query in SQL Server? Yes, the question that companies usually ask first is this question, which does not end with it. How do we correct after the birth? etc. as. With the Query below, you can find your long running queries. Yusuf KAHVECI yusufkahveci@sqlturkiye.com www.sqlturkiye.com Thanks. [More »]( 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]( : [SQL Server 2017 - Development]( [If today is Monday show Friday to Sunday]( - Hi Guys, I am using the below to show data relating to yesterdays date, It works great but on a Monday... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [TDE enabled but database backups don't *appear* to be encrypted]( - I have a database that is using TDE and this can be confirmed through: SELECT     CASE database_id         WHEN 2 THEN 'tempdb'         ELSE 'encryptedDB'     END... [Upgrading SSIS Solution from 2014 to 2016]( - Hi, I'm testing out an upgrade of our ETL solution from SSIS 2014 to 2016. These are the steps I've... [Upgrade edition of SQL Server 2016 SP1 from Developer to Standard]( - Hi all Having a strange issue. We have a production server which incorrectly got installed with Developer edition. The upgrade path... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [insert into: ignore identity column's value]( - Hi, is there any Chance to get this working? CREATE TABLE .(  IDENTITY(1,1) NOT NULL,  (10) NULL, ) Now insert something with... [YTD running balance including missing months]( - Hi All, I am trying to get the YTD Account Balances for a financial statement. There are accounts which does not... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [transactional replication @force_invalidate_snapshot = 1 parameter]( - hello, I have transactional replication set up in my enterprise sql 2014 server. Publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [by what size will nvarchar(max) column get stored in LOB_DATA allocation unit?]( - I have a table defined as follows: CREATE TABLE (  Col1 INT IDENTITY PRIMARY KEY,  Col2 NVARCHAR(MAX) NULL DEFAULT REPLICATE('A',... [Trying to select both "paths" through data]( - I'm working with one of the developers, he has an application which logs the "path" taken by a process. The... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Providing a password to an SSIS package]( - I have a need to provide a password to an SSIS package so that the package can be deployed to... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [How to get Last Occurance of % value in the string...]( - Hello Good Morning How to get Last Occurance of % value in the string... please help me with this situation CREATE [Maximum Consecutive Count]( - I have sat with this problem for over two weeks now, it is time that I admit defeat. Time for... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [KB4057113 Installation Issue - sqlsupport.msi]( - Ok, I'm trying to install this security patch. When I first started trying, it kept saying I can't files... I... [Deleted SQL Agent Job Sending Failed Notifications]( - Hi All, 2008 R2 instance. We frequently copy databases for new DBs as it's a lot less work to get clients... [Rebuilding Indexes very often is good?]( - Hi Friends, I was about to work on the production server all day last week. I was doing ETL process. Every... [Transaction Log Too Big and Won't Shrink]( - I have been tasked with figuring out haw to shrink a 2.5 Terrabyte log file. When I took it over... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [String column shows up as Number Stored as text error]( - In my report i have a column which is of varchar type but currently it is storing int value and... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [Transform Logfile in a table]( - Hi guys, I need a help on a demand here, so I'd transform one LOG File (example in the image) into... [Visual Studio 2017 - SSDT]( - Hi, I'm hoping someone can tell me that I'm mistaken and have missed something but I've installed Visual Studio Professional 2017... --------------------------------------------------------------- [SQLServerCentral.com]( : [SQLServerCentral.com Announcements]( [Website Work]( - We are beginning a project to redo the website in its entirety, with the exception of the forums. The forums... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com