How to create SQL Server database split backup files

Introduction

One of the main tasks for every database administrator is creating a reliable disaster recovery plan. The plan always includes multiple backup and restore operations. Usually, opting for conventional, single file backups should suffice, but in some cases, resources like disk space, backup time, or both could be the issue. This is usually the case when working with large databases.

May 6, 2016

How to create multiple SQL Server backup mirrors

One of the most important tasks for any database administrator is to create a foolproof disaster recovery plan. This plan usually includes multiple backup and restore operations. Most of the time, opting for conventional backups should suffice, but in some cases, storing all backups on a single backup device may prove to be a bad idea. As the databases grow with time, the backups become larger, and backup devices less stable due to frequent read/write operations. If the backup device fails, all of the backed up data might be lost. To avoid this scenario, some administrators take multiple copies of their backup files, and store them on different backup devices. There are a few ways to do this:

May 6, 2016

Improving productivity with SQL snippets

SQL snippets can be a big boost to productivity when writing T-SQL. First, to use a SQL snippet in a script, there is no need to know the syntax, only the purpose of that SQL snippet (e.g. delete an object, create a table, etc.). Using snippets also reduces of the lines of code that has to be typed, and thus decreases the potential for errors that could occur from typing.

May 5, 2016

How to deploy changes directly to a SQL database from a source control repository

One of the challenges these days is how to pull the latest changes (of SQL objects) from the source control repository and deploy them into a SQL database. This process is particularly helpful in the CI workflow Build step, when developers want to build a SQL database from the committed changes in the source control repository, so they can test if their changes compromised SQL objects from the source control repository or they will be built successfully.

April 27, 2016

SQL Server database continuous integration workflow BUILD step – Building a SQL database from a source control repository

In this article, the second Build step of the CI workflow will be described. The Build step is a step in which a database is built using the latest changes in the source control repository and once the build process is finished, a feedback of success/failure is provided to developers.

April 27, 2016

How to automate trigger-based SQL Server auditing

More often than not, a top level requirement for all SQL Server DBAs is to have mechanisms to capture who changed what and when and to have stored audit information available for further investigation. In order to achieve this, database auditing needs to be set up using various mechanisms. One of those mechanisms is a trigger-based auditing.

April 27, 2016

Document SQL Server data model diagrams

This article will be about how to include Data model diagrams in the documentation generated by ApexSQL Doc. With this feature, it’s possible to see graphically display database tables and their relationships.

April 26, 2016

Synchronize SQL database changes between local and remote repositories in distributed source control systems

Having a SQL database being version controlled locally, by storing all changes in a repository on a local machine can be quite handy. In the context of team based database development, it is necessary to establish the environment where changes can be tested locally, specific revisions reverted from the commit history, and doing such things before pushing changes to the remote repository where the rest of the team will be able to review them, and apply against a local database copy.

April 26, 2016

How to create and manage SQL backup policies

Performing regular database back-ups is a crucial part of any disaster recovery plan. There are many aspects that should be taken into consideration when planning long term backup strategies. It is necessary to choose the backup type and backup location and to plan how frequently to perform the backups. Additionally, the processes of verification or encryption could also be included into the plan.

April 22, 2016

SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository

Nowadays, it’s quite common that database changes are made several times a day and that a number of developers are making changes against the same database. Due to these often changes, a certain “process” has to be followed in order to avoid any potential problems in the later stages of development (Quality Assurance (QA), Staging, User Acceptance Test (UAT)) and at the end in production. When talking about the Continuous integration (CI) for SQL databases today, it’s often referred to a process of several steps:

April 22, 2016

Revision history of an object change in a SQL database using Git

The essence of each source control system is the ability to easily review the history of committed revisions. In addition, to comparing revisions, a user needs to get a specific revision and apply it against a database. Each revision should have a unique timestamp and should carry the information about the user who did the commit. Such system ensures that it is easy to determine who committed what and when, and in some way provide a complete auditing trail of committed revisions.

April 22, 2016

Manage and monitor SQL Server backups from a central location

Introduction

Running and maintaining multiple SQL Server instances can often be a formidable challenge, especially if these instances run on multiple servers. It is easy enough to set up a SQL Server agent job for each server to automate the backups, but what happens if there are 20, 30, or 100 servers that need maintenance? In this scenario, configuring agents on each server would take forever, and monitoring the entire setup would prove to be a nightmare for any administrator. Of course, there are several solutions for this scenario:

April 15, 2016

SQL Server database object search

Developers and DBAs often perform database searches in order to find and obtain additional information related to specific objects, sometimes without knowing the full name of the object.

SQL Server provides sys.objects system view that list all the objects from the database.

SELECT *
FROM sys.objects

Which will return all user-defined (tables, view, functions) and system objects in that database:

To find desired SQL Server database objects, type the following:

USE AdventureWorks2014

SELECT NAME AS ObjectName
	,schema_name(o.schema_id) AS SchemaName
	,type
	,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
	AND o.NAME LIKE '%Add%'
ORDER BY o.NAME

This will provide a list of all user-defined objects (is_ms_shipped=0), which names contain Add characters:

Quick tip:

sys.objects does not show DDL triggers. To view all DDL and DML triggers use the sys.triggers system view.

SQL Server Management Studio provides the Object Explorer Details feature that allows searching for any object in a database which is chosen in the Object Explorer pane.

To open the Object Explorer Details panel, press the F7 key or from the View menu choose the Object Explorer Details option:

Which will open the Object Explorer Details panel:

To find SQL Server database objects first in the Object Explorer panel, select the database over which wants to search the objects and in the Search text box from the Object Explorer Details panel type in the search criteria and press enter. This will return all user-defined and system objects that match with chosen criteria:

Right-click on an object from the list in the Object Explorer Details panel, will popup menu with the Synchronize option, which leads directly to the location of the object in the Object Explorer panel:

The Go to object feature

ApexSQL Complete is a free SSMS and VS add-in that speeds up coding, and improves productivity using features such as, auto-completing SQL keywords and code fragments, SQL snippets, auto inserting statements, text auto-replacement, Go to object and more.

To perform a quick object search over a database in order to find SQL Server database objects and get the appropriate information (e.g. how many tables have the same name in the database with different schemas) or to locate objects in the Object Explorer panel in SSMS, the Go to object feature can be very useful.

There are three ways to initiate the Go to object feature:

  1. From the Object Explorer, select the desired database and from the ApexSQL Complete tool bar, choose the Go to object button:
  2. When the query window is opened, from the Available Database combo box in the SQL Editor toolbar, select the desired database and click the Go to object button from the ApexSQL toolbar:
  3. Use the Ctrl+Alt+Y shortcut

The Go to object window will be shown with all objects from chosen database in the display list:

Quick tip:

The Go to object feature lists only user-defined objects

As soon as the user starts to type some text in the text box, the appropriate objects will be shown in the display list and on the right side of the text box will appear the number of the objects that matches with criteria that is typed in the text box:

Additionally, the Go to object feature provides the option to list only the desired object types (e.g. tables). To do that, from drop-down menu, which is located on the upper right side of the display list, select the desired object type:

Double-click on the object in the list or right-click on the object and from the popup menu choose the Go to object command, and the chosen object will be shown in the Object Explorer panel:

The Navigate to object feature

To find SQL Server database objects in the Object Explorer panel via the query window, right-click on the name of the object in the query window and from menu choose the Navigate to object command:

Another object search feature

ApexSQL Search is free add-in for database objects and text searching, which integrates with SSMS and Visual Studio.

To perform more complex database object searches and find desired SQL Server database, over one or all databases, the Object search can be very handy.

There are two ways to initiate this option. Right-click on the database or any object from the Object Explorer panel and from the context menu, choose the Object search command:

Another way to initiate the same option is from ApexSQL Search menu, assuming that the database which will be searched is selected in the Object Explorer panel:

This will open the Object search query window:

In the Search text drop-down box any type of strings can be entered, including strings that contains special or Unicode characters. T-SQL wildcards can be also used as part of the search phrase. For instance, to find objects for which only the part of the name is known, typing the %Add% and press enter or the Find button, the Object search query window will find SQL Server database objects that contain this phrase in the name of the objects:

Quick tip:

The Search text drop-down box is case insensitive. For example, the result for the search phrase ADDRESS or address will be the same.

Additionally, the search phrase can be adjusted by specifying whether to match (e.g. Exact match, Object name, Object body) to the searched text:

The Search text drop-down box keeps all committed phrases and this is quite handy when used one phrase over and over again:

The Object search allows users search over the all databases objects on the SQL Server by choosing the All databases option under the Database combo box:

Quick tip:

Searching in all databases can be time consuming so make sure to apply rigorous filters if possible

The Object search has the option to quickly identify the desired results by filtering out unwanted results using the Filter row option. Right-click on result grid and from the popup menu choose the Filter row command:

This will open filter row on top of the grid, where each field in the filter row contains values from the appropriate column:

By default, when it opens the Object search query window the filter row is active.

ApexSQL Search supports regular expressions in order to find required results.

In addition, to show the results from two databases (e.g. AdventureWorks2014 and WideWorldImporters), in the Database combo box, choose the All databases option and press the Find button. After the results are shown, in the Text Filters tab type 2014 or Wide::

To navigate to the specific object in the Object Explorer, from the Object search results grid right-click on the desired object and from the popup menu select the Navigate to object explorer node option. The same result can be achieved by double-click on the object from the result grid:

 

April 11, 2016

Recover a SQL Server database using only a transaction log file (.ldf) and old backup files

Having a good recovery plan that ensures database backups are being taken on a regular basis and safely stored on specific or multiple storage drives greatly increases database safety and recovery options when a disaster or accident occurs. Nevertheless, creating regular full database backups doesn’t provide full point-in-time recovery, since restoring the latest full backup will restore a database to a proper state at the time a backup was created, but all changes, both schema and data, which have occurred after the last full backup will be practically lost.

April 5, 2016

How to automate SSAS cube database documentation

SSAS is a powerful and popular feature in the SQL Server ecosystem. That’s why ApexSQL Doc has implemented support for automating SSAS cube documentation so you can have full documentation of your database in no time. We have created this article so you can see how easy it is to create an automated process which will generate documentation with ApexSQL Doc.

April 5, 2016

How to automate SQL database documentation

Wouldn’t it be a neat trick if you could document a database with a double click? With this article, we’ll demonstrate a few simple steps to allow you to make a script file which will run a process that generates documentation for your database. After that you can just execute it to repeat the process with just two mouse clicks or even set up a job to run it automatically every night. In this article you will learn how to create this automated process of database documentation by using ApexSQL Doc.

March 28, 2016

Automatically script SQL Server table data and object schema directly to source control

Imagine a scenario where you want to get your database into source control quickly and easily, including all schema objects and data from certain code tables that won’t change aka Static data. Then, once you have ported your database successfully to source control, to be able to update the repository nightly with any and all changed objects. In this way, you’ve fully and automatically source-controlled your database, without having to worry about direct integration, check ins, check outs etc. essentially providing much of the “gain” of database source control integration, with little of the “pain”. This article will describe how to build this “poor man’s” SQL database source control integration system using a 3rd party tool, ApexSQL Script

March 28, 2016

Retrieve unsaved SQL query scripts due to SQL Server Management Studio crash

Recovering work after SQL Server Management Studio crashes

Imagine a situation of working in SSMS, and suddenly the power goes out, the system gets crashed, SSMS gets crashed or the work session was abruptly terminated for any other reason, which causes the loss of unsaved work on various SQL scripts. In this situation SSMS provide prompt window for recovered data after restarting:

March 21, 2016