Implementing Automated Database Backup and Recovery with built-in Tools

Implementing Automated Database Backup and Recovery with built-in Tools
5 (100%) 2 votes

Introduction

There are quite a lot of examples on the web for creating database backups and recoveries. We are going to provide another example of the built-in tools in MS SQL Server.

In this example, we collected several approaches, including the database integrity check before creating a backup and restore it from the previously created backup.

Read More

Creating Dynamic Pivot Table with QUOTENAME Function

Creating Dynamic Pivot Table with QUOTENAME Function
Rate this post

In my previous article on the basic pivot operator, we saw how pivot operator could be used to convert rows to columns, resulting in pivot tables. We saw that there were three main steps to create a pivot table. The first step was selecting the base data. The second step was converting the base data to a table-valued expression, and the final step involved applying a pivot operator to the temporary data, which resulted in the pivot table.

Read More

Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()

Methods to Rank Rows in SQL Server: ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE()
5 (100%) 5 votes

SQL Server provides us with a number of window functions that help us to perform calculations across a set of rows, without the need to repeat the calls to the database. Unlike the standard aggregate functions, the window functions will not group the rows into a single output row, they will return a single aggregated value for each row, keeping the separate identities for those rows. The Window term here is not related to the Microsoft Windows operating system, it describes the set of rows that the function will process.

Read More

Setting Database Access Permissions

Setting Database Access Permissions
2 (40%) 2 votes

Server security mainly depends on how correctly you can configure access permissions on objects. Providing a user with excessive permissions may cause many issues. No, a user will not use your errors. Instead, any hacker or I will do this. In this case, you can forget about your tables with data or the whole database.

For some reason, the security of the database is protection from the outside, such as a hacker. However, this happens very seldom. I am a programmer in a big company and an administrator does not even think about protecting the server ports, where everything is open. There is a bunch of databases, programs, and even an FTP server on a single server and it has never been hacked over the past 5 years. Fortunately, I persuaded the administrator to deploy the WEB server on a separate hardware. Otherwise, if someone knew the IP address of our main server, any slacker would be able to hack it. Neither the database nor Windows has been patched for several years.

Read More

Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET

Sending a Query to All Databases of All Specified Servers in MS SQL Server and C#.NET
Rate this post

Often, it is necessary to send a query to all databases of all specified servers. Many DML-queries can be created with built-in tools. However, what about DDL-queries?

In this article, we are going to explore an example of implementing the application that sends a query to all databases of all specified servers, using MS SQL Server and C#.NET.

Read More

Analysis of MS SQL Server for those who see it for the first time – Part 2

Analysis of MS SQL Server for those who see it for the first time – Part 2
Rate this post

Analysis of MS SQL Server for those who see it for the first time – Part 1

We continue to analyze what is happening on our MS SQL Server. In this article, we are going to explore how to retrieve information about user performance: who makes what, and how much resources are consumed.

I think the second part will be interesting for both database administrators and developers who need to understand what is wrong with the requests on the production server that used to work fine on the test server.

Read More