Overview of SQL Joins in SQL Server

The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause.  It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.

The aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.

 

1

Continue reading

SQL Joins

 

Advertisements
Posted in SQL Joins | Tagged , , , , , , | Leave a comment

SQL Substring function in SQL Server

The requirement of data refactoring is very common and vital in data mining operations. In the previous article, you’ll learn the tips for getting started with SQL string functions, including the SQL substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.

After reading this article, you’ll understand more about:

  1. SQL String functions
  2. Understand the SQL Server SUBSTRING function
  3. How to handle data using the SUBSTRING SQL function
  4. How to use the SQL Server SUBSTRING function in the where clause
  5. How to dynamically locate the starting and end character position in the SQL Server SUBSTRING function
  6. How to work with date-time string using the SQL Server SUBSTRING function
  7. How to Create a simple sub-select using the T-SQL SUBSTRING function
  8. And more…

further reading…

SQL Substring

 

Posted in SQL String | Tagged , | Leave a comment

SQL Server string functions for Data Munging (Wrangling)

In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts.

In this guide, we’ll see the following topics:

  • What is the data munging?
  • How you can reduce your data preparation time
  • How to easily get started with SQL string functions
  • How to process data using SQL string functions
  • And more…

We’ll look at specific SQL string function examples including

  • SQL concatenate string
  • SQL Server substring functions
  • SQL string functions
  • SQL Server convert string to date
  • SQL replace string
  • SQL convert INT to String
  • SQL convert String to DateTime
  • SQL string comparison
  • And more …

continue reading…

String functions

This article is an effort to showcase the available SQL string functions to manipulate the raw data to make it more meaningful data-set for the data scientist to perform the data analysis using SQL Server.

 

Posted in String handling | Tagged , | Leave a comment

Getting started with SQL Server Tools

This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.

In this article, I will explore various SQL tools. I will be discussing in detail about each of the following tools in the upcoming articles:

  1. Sqlcmd
  2. BCP
  3. SqlPackage
  4. MSSQL-Scripter
  5. SQL Operations Studio

Continue reading …

Getting started with SQL Server Tools

Summary

Microsoft has provided a platform and tools to manage modern data applications. The focus of this series is to help the DBAs to give the glimpse of currently available SQL Server DevOps tools.

  1. Sqlcmd – Overview of SQLCMD utility in SQL Server
  2. BCP – BCP (Bulk Copy Program) in Action
  3. SqlPackage – Continuous Deployment using SQL Server Tools SqlPackage.exe
  4. MSSQL-Scripter – SQL Server cross-platform MSSQL-Scripter scripting Tool
  5. SQL Operations Studio – Getting started with SQL Operations Studio (SOS)
Overview of SQLCMD utility in SQL Server
The BCP (Bulk Copy Program) command in action
Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe
All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool
Getting started with SQL Operations Studio (SOS); initial installation and configuration
Posted in SQL Server Tools | Tagged , , , , | Leave a comment

What’s new in SQL Server 2017

SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing a lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features.

SQL Server 2017 …

  1. is now on the platform of our choice; SQL Server 2017 on several Linux distros, that can run on Docker containers, and SQL VM in Azure, along with the good ol’ SQL Server on Windows.
  2. expands its reach to support Graph database technology
  3. includes automatic database tuning
  4. supports Machine Learning by adopting Python
  5. contains new dynamic management views and functions
  6. has new string functions
  7. And more…

Continue reading ..SQL Server 2017 Top Features

 

Posted in SQL Server 2017 | Tagged , , | Leave a comment

SQL Server – Stairway to Database Backup-and -Restore, (and Recovery)- Complete Guide

It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures.

As you move through the list of 20+ articles, you’ll see most of the features that include various database Backup-and-Restore concepts, T-SQL programming techniques, PowerShell Scripting, implementation of backup solutions to Docker containers, data management using SQL Ops Studio, handling bacpac and dacpac files, ingress and egress data from Cloud and more. The articles are organized to make specific topics easy to find so that you can jump start depending on your skill level.

  1. Article 1: An overview of the process of SQL Server backup-and-restore
  2. Article 2: Understanding the SQL Server Data Management Life Cycle
  3. Article 3: Understanding SQL Server database recovery models
  4. Article 4: Understanding SQL Server Backup Types
  5. Article 5: Backup and Restore (or Recovery) strategies for SQL Server database
  6. Article 6: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
  7. Article 7: Understanding Database snapshots vs Database backups in SQL Server
  8. Article 8: SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
  9. Article 9: Smart database backups in SQL Server 2017
  10. Article 10: How to perform a page level restore in SQL Server
  11. Article 11: Backup Linux SQL Server databases using PowerShell and Windows task scheduler
  12. Article 12: SQL Server Database backup and restore operations using the Cloud
  13. Article 13: Tail-Log Backup and Restore in SQL Server
  14. Article 14: SQL Server Database Backup and Restore reports
  15. Article 15: Database Filegroup(s) and Piecemeal restores in SQL Server
  16. Article 16: Backup and Restore of a SQL Server database with Memory-Optimized objects
  17. Article 17: Backup and Restore using SQL Server Docker Containers
  18. Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio
  19. Article 19: Interview questions on SQL Server database backups, restores and recovery – Part I
  20. Article 20: Interview questions on SQL Server database backups, restores and recovery – Part II
  21. Article 21: Interview questions on SQL Server database backups, restores and recovery – Part III
  22. Article 22: Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL
  23. Article 23: Understanding Database Backup Encryption in SQL Server

Continue reading….

https://www.sqlshack.com/database-backup-and-restore-process-in-sql-server-series-intro/

 

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server MSDB log store for database backups, restores, and recovery: Q& A – Part 3

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database.

  1. How do you delete six months old data to reduce the size of the backup and restore history tables?
  2. How do you get the Backup History for a specific database including the size, location, and LSN?
  3. How do you create and restore a marked transaction?
  4. How do you find the RESTORE HISTORY of the database?
  5. How do you list the last 30 days restore history at the instance level?
  6. How do you measure the database backup or database restore operation progress?
  7. How do you measure the database growth using backup size?
  8. How do you define or estimate the storage required for database backup?
  9. How do you get most recent database backup time for each database?
  10. How do you get recent database backup time for each database using PowerShell?
  11. How do you get recent database backup time for each database across multiple servers using PowerShell?
  12. How do you find the backup history with duration and compressed backup size columns?

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-iii/

 

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server database backups, restores, and recovery: Q& A – Part 2

In this article, you’ll see the Q&A about the database restore and recovery internals. To learn a lot about SQL Server database backup-and-restore (recovery) internals, you can refer to the following full list of topics.

  1. Define database restore
  2. Define the database recovery process
  3. Check the state of the backup file
  4. Check the number of file(s) in a backup file
  5. Identify the database version from a backup file
  6. Check the backup software tools used for backup
  7. Perform simple and multiple ways to restore a database
  8. Explain WITH OVERWRITE option
  9. Explain WITH NORECOVERY option
  10. Restore differential or t-log backup file
  11. Understand Other restore types STANDBY/READONLY
  12. Explain WITH REPLACE option
  13. Explain WITH MOVE option
  14. Restore the database using a Split files
  15. Detail Piecemeal restore process
  16. Explain Point-in-time recovery
  17. Describe the Page-Level-Restore process
  18. Explain Recovery-Only database restore
  19. Explain WITH STOPAT option
  20. Generate restore data commands using dynamic T-SQL

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-ii/

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server database backups, restores, and recovery: Q& A – Part 1

Let’s deep dive and review each of the database backup command topics to get a better understanding of what it is all about. In this article, we will find an answer for FAQs about the SQL Server database backup. We will learn more about database backup.

In this article, we’ll discuss following Q&A:

  1. What are database backups?
  2. What are the factors to consider while planning for backup, restore, and recovery strategy?
  3. What are the different stages of data life-cycle management?
  4. How to do plan or setup SQL Server Backup and Restore strategy in a multi-server environment using native tools?
  5. How does the database recovery model impact database backups?
  6. Explain the different types of database backup in SQL Server?/a>
  7. How can I verify that backups are occurring on a daily basis?
  8. How can I verify that backups are occurring on a daily basis?
  9. What are the system tables that store backup and restore related information?
  10. What are the new enhancements added for Backup and Restore SQL Server 2017?
  11. What is a smart T-Log backup in SQL Server 2017?
  12. Assume that the database recovery model is full. The full database backup runs every week at 9 PM. Sunday, differential backup runs daily at 9 PM. Monday to Saturday, and hourly transaction log backups. Now, database crashed on Friday 5:10 PM. How to do a point-in-time recovery of the database?
  13. How to do database refresh automation?
  14. Explain the process of database backup and restore operations using the Cloud?
  15. In a given situation, assume that the system has 3 drives with 30 GB of free space on each drive. Now, how can you perform a database backup for 80 GB database?. Is it possible?
  16. Explain piecemeal restore operation?
  17. What are database recovery phases and how it is different for in-memory optimized objects?
  18. How to perform database backup and restore operation on SQL Server Docker containers?
  19. What is the native toolset that is available to perform database backup and restore operation?
  20. What are the top 10 trace flags can be used with database backup?

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-i/

 

 

Posted in Backup and Restore | Tagged , | Leave a comment

Backup and Restore operations with SQL Server 2017 Docker containers using SQL Operations Studio

In this 18th article of the series, we’ll discuss the concepts of database backup-and-restore operations with SQL Server 2017 Docker containers using SQL Ops Studio (SOS). Before proceeding, you need to have Docker engine installed and SQL Ops studio configured on your host machine.

This article covers the following topics:

  1. Overview of SQL Operations Studio (SOS)
  2. How to use SQL Ops Studio integrated terminal
  3. Definition of Docker containers
  4. Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the SQL Ops Studio interface
  5. And more…

Continue reading…

Backup-and-Restore SQL Server 2017 Docker containers using SQL Operations Studio (SOS)

Posted in Backup and Restore, docker, SQL 2017 | Tagged , , , | Leave a comment