Automatically convert contained database to non-contained for replication

This article was recently published on dev.getroadmap.com:

 
 
In one of my previous posts, I described how to setup replication to an Azure SQL database. This works like a charm, and I still highly recommend using this when you want to migrate data from an on-premise server (or Azure VM) to a Azure SQL db (PaaS).

But in our environment, we use SQL Server 2016 and contained databases for some of our datasets. Unfortunately (but totally understandable), you can’t setup replication from a contained database. So how do you deal with this? For our use-case, I’ve written a script to automatically change the database from contained to non-contained. And because I’m probably not the only one who needs to do this, I’d like to share it with you.

 
Steps to take
It might sound a bit difficult, but when you think about it, it’s actually quite easy. To go from a contained database with a user to a non-contained database with a login, you need to take the following steps:

 
1) Duplicate user as login on server level
2) Drop user on database level
3) Alter database to non-contained
4) Add login to database

 
The script
Before you start using this, I want to warn you. Using this script on your server(s) or in production is at your own risk. This worked for me, but maybe it doesn’t on your server(s)!

In order to let this script work, you must be able to stop applications and queries from executing on your database. In our case, we can just stop the service(s) that use a specific database (that’s the advantage of micro services). The reason for this is that you must be able to obtain an exclusive lock on the database, in order to switch from contained to non-contained.

If you can manage this, the script below could work for you too:

--====================================================================================================
/* 1 - Duplicate user on instance level as login */

USE master
GO

CREATE LOGIN [Login non-contained database] WITH PASSWORD=[Password]', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--====================================================================================================
/* 2 - Drop user on database level */

USE [Contained database name]
GO

DROP USER [User contained database]
GO

--====================================================================================================
/* 3 - Alter database to non-contained */ 

USE master
GO

ALTER DATABASE [Contained database name] SET CONTAINMENT = NONE WITH NO_WAIT
GO

--====================================================================================================
/* 4 - Add login to database */

USE [Non-contained database name]
GO

CREATE USER [User non-contained database] FOR LOGIN [Login non-contained database]
GO

ALTER ROLE [db_datareader] ADD MEMBER [User non-contained database]
GO

ALTER ROLE [db_datawriter] ADD MEMBER [User non-contained database]
GO

--====================================================================================================

 
Or, to make it easier to read, an example with actual names:

--====================================================================================================
/* 1 - Duplicate user on instance level as login */

USE master
GO

CREATE LOGIN [Login_RW] WITH PASSWORD=N'Password123!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--====================================================================================================
/* 2 - Drop user on database level */

USE ContainedDatabase
GO

DROP USER Login_RW
GO

--====================================================================================================
/* 3 - Alter database to non-contained */ 

USE master
GO

ALTER DATABASE ContainedDatabase SET CONTAINMENT = NONE WITH NO_WAIT
GO

--====================================================================================================
/* 4 - Add login to database */

USE ContainedDatabase
GO

CREATE USER Login_RW FOR LOGIN Login_RW
GO

ALTER ROLE [db_datareader] ADD MEMBER Login_RW
GO

ALTER ROLE [db_datawriter] ADD MEMBER Login_RW
GO

--====================================================================================================

 
Conclusion
Even though I thought that using a contained database could be a big blocking factor for us in the process of migrating data to Azure, it really wasn’t that big of a deal. Especially now that we automated the process, it doesn’t add more than 5 minutes to the process of replicating data to Azure.

Advertisements

Replication: Snapshot Agent fails on date conversion

This article was recently published on dev.getroadmap.com:

 
 

In the previous post I wrote, I explained how to setup replication from an on-premise SQL Server instance to an Azure SQL database. While doing this, I came across a very strange issue (or maybe even bug) when setting up replication.

 
The problem child
After working on reproducing the issue for a day, and trying to reduce the issue to a small-scale problem, I came to the conclusion that the problem was (probably) caused by a single primary key on a table in the database:

CREATE TABLE dbo.BuggedTable
	([Day] DATE NOT NULL,
	 SomeId VARCHAR(50) NOT NULL,
	 Amount INT NOT NULL,
	 CONSTRAINT PK_BuggedTable PRIMARY KEY CLUSTERED 
		([Day] ASC,
		SomeId ASC
		)
	)
GO

 
Creating publication & subscriber
The setup of the publication and subscriber wasn’t that difficult. As I said before, there are a few things you need to configure differently then you would do for SQL Server to SQL Server replication.

So I won’t talk you through the whole process again, but refer you to the articles instead.

 
Generate initial snapshot
Once the publication and subscription are in place, it’s time to generate the initial snapshot. The snapshot agent prepares the snapshot that contains the schema and data, needed to initialize the subscriber(s):

 
In some cases it takes a while, but in the end, I found this “warning” on my screen:

 
When you dig into this by opening the agents tab in the replication monitor, you see the actual error:

 
The complete error states:

Error messages:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause, Boolean useTableLockHint, Int32 bcpFileFormatVersion)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Message: Conversion failed when converting date and/or time from character string.
Stack: (Source: MSSQLServer, Error number: 241)
Get help: http://help/241

 
First I thought that the DATE column being part of the primary key was the problem. But then again, another table in a different database had the combination of a DATE and a UNIQUEIDENTIFIER as a primary key. So that couldn’t be it.

 
Digging deeper
During my investigation to this issue, I found the following:

– There was enough space on the disk for the snapshot
– Other database with DATE in primary key (PK) worked (even though combination was DATE + UNIQUEIDENTIFIER, instead of DATE + VARCHAR)
– Adding “-UseInprocLoader” to Snapshot Agent job doesn’t work

So after digging a bit deeper, and trying to reduce the issue to a small-scale problem, I found:

– Reproduced in an empty (new) database, with just the failing table as single object in the database –> STILL FAILS
– Changed object in test DB to use newly created IDENTITY(1,1) as PK, and added Unique Constraint to old PK columns –> WORKS
– Make DATE column part of different PK (together with IDENTITY), and no Unique Constraint –> WORKS
– Changing the PK to just the VARCHAR column –> WORKS
– Changing the PK to just the DATE column –> WORKS
– Setting database on local instance (SQL 2016 Dev) to comp. 110, just like on test –> WORKS

After a lot of different variables in the test-setup, I found out that it’s probably an old bug that wasn’t properly patched when upgrading the SQL Server engine to a newer version. Let me elaborate on that:

– The bug is reproducible on the test server, which is an upgraded engine from SQL 2012 or 2014 to SQL 2016 RTM
– The bug is reproducible on the production server, which is an upgraded engine from SQL 2014 to SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL 2014
– The bug is not reproducible on a clean install of SQL 2016 RTM
– The bug is not reproducible on a clean install of SQL vNext CTP

 
Finding a work-around
Because I couldn’t find a work-around for this issue, I requested the help from Justing Langford (Blog | @JustinLangford) from Coeo. He pointed me to an article that describes a few possible work-arounds.

For me, adding a row filter for the table did the trick:

 
As described in the article mentioned above, adding the row filter disables the BCP partioning for this article (table), and the snapshot agent completed without any problem:

 
Conclusion
Although this bug should’ve been fixed ages ago, it looks like it (re)appeared again after an engine upgrade. I’m not sure how this happened, but all I know is that it took me about 3 days to find, reproduce, reduce and work around the problem.

So hopefully this article will save you that time!

Setting up replication from on-premise SQL Server to Azure SQL DB

This article was recently published on dev.getroadmap.com:

 

For performance reasons we are looking for a way to split our write-operations from our read operations. A good way to do that is by duplicating the database onto another server (on-premise) or platform (Azure SQL DB). When you look at the options (Availability Groups (AG), database mirroring, clustering, replication, Azure sync groups, etc.) the easiest way to quickly duplicate a database and keep the data up-to-date at the same time is SQL Server replication.

Replication to another on-premise instance is easy. You just follow the steps in the wizard, it works out-of-the-box, and the chances of this process failing are small. With replicating data to an Azure SQL database it’s a bit more of a struggle. Just one single word took me a few HOURS of investigation and a lot of swearing…

 
Setting up replication and databases
Before you start with setting the whole thing up, make sure that the replication is installed on your instance. If this is not done yet, install the needed components via the installer. This component can be installed without rebooting the server, or the need to restart the instance.

If you needed to install replication, make sure you restart the SQL Server Agent. For some reason, the configuration change works on the instance without any problems, but the Agent can’t run the needed jobs until you restart it.

The next step is creating 2 databases: a publisher (on the on-premise instance) and a subscriber (an Azure SQL database):

 
And create a table that can be replicated:

CREATE TABLE dbo.SomeObject
	(ID INT IDENTITY(1,1),
	 SomeValue VARCHAR(100),
	 PRIMARY KEY (ID))

 
Setup the publisher
Now that you have 2 databases, it’s time to setup the publisher. I’m not guiding you through the whole replication setup (there are plenty of tutorials out there that are WAY better than my explanation), but I’ll show you the highlights.

First, start by clicking “New Publication” on your on-premise instance:

 
I chose to run the distributor on the same server.

If you follow the wizard, it will prompt you with the question on where to store the snapshots:

 
Copy this link into for example notepad, because it comes in handy later in the process.

As publication type, choose “Transactional publication”:

 
Again, follow the wizard and choose the objects you want to replicate (in my case just that single table):

 
For the security settings, I chose to use the SQL Server Agent

After finishing the steps in the wizard, give your publication a name, and finish this setup.

 
Create a subscriber
On the on-premise instance, right-click the newly created publication, and add a new subscriber:

 
The distribution agent will remain on the distributor (push subscription).

When you’re prompted to add a subscriber, choose “Add SQL Server Subscriber”:

 
Here comes the part that took me a while to figure out. When you try to replicate changes to an Azure SQL database, and you use the normal connection, it will fail to connect in the end. But if you use a secure connection, replication works like a charm. So make sure you use the following server name ([Server Name].database.secure.windows.net):

 
Adding that single word took me an hour or so to figure out. Hopefully this will save you a few hours of investigation, looking for dodgy error messages, ending up in finding an “errorcode 40852”, and leading you to the solution.

When this is set up, select the target database (subscriber):

 
When you’re prompted for the distribution agent security information, make sure you use a SQL Server login that has the correct permissions on the Azure SQL database:

 
Start the replication
Now that everything is in place and configured, it’s time to create the initial snapshot. This snapshot will be used to create all the objects and data that are supposed to be replicated on the Azure SQL database. Open the snapshot agent by clicking “View Snapshot Agent Status” on the publication:

 
The snapshot agent hasn’t run yet, so press “Start” to start the process:

 
When this process has completed, you should see something similar like this:

 
Now it’s time for a final test. Insert a record on the source database (publisher):

INSERT INTO dbo.SomeObject
	(SomeValue)
VALUES
	('Test value')

 
You should see this record pop-up on the subscriber after a few seconds (default latency is 5 seconds):

SELECT *
FROM dbo.SomeObject

 
You can also check the “Synchronization Status”, and you should see a command being replicated:

 
Conclusion
Out of all the available options, I still think replication is the easiest way to replicate data into Azure. The only downside is that there doesn’t seem to be a lot of information about this, especially when you look at the “database.SECURE.windows.net” part.

If you need more than just the one-way traffic, or you want a bit more functionality, maybe replication isn’t your weapon of choice. But for me, it worked like a charm… Eventually…

Easy pivot: From key-value pairs to columns

This article was recently published on dev.getroadmap.com:

 

If there is one thing I pretty much hate doing in T-SQL it’s PIVOT and UNPIVOT. Even though I used it a few times in the last couple of years, it’s an adventure to find out how it works every time. And I know a lot of people struggle with this part of T-SQL, so let’s take a look at a (hopefully) simple example.

 
Key-Value pairs
The idea of using key-value pairs to store data isn’t new, but with the rapid development of cloud solutions like Azure and the hype around NoSQL databases, using key-value pairs to store data got a big boost. Especially developers (in my experience) love using key-value pair to store their data, because it’s easy for them to consume the data in an application. But it gives the database professional an extra challenge because we’re used to retrieve columns with values instead of a record per value. So how can we turn those key-value pairs into rows?

 
Flights
To show you how to change key-value pairs to columns, let’s create an example based on flights. If one of our customers needs to travel and they take the plane, there is some basic information we need to show them the flight status in the app. So let’s create a table to store that dataset:

CREATE TABLE dbo.Flights
(
	FlightId UNIQUEIDENTIFIER,
	[Key] VARCHAR(255),
	[Value] VARCHAR(255)
)

 
and insert a flight:

INSERT INTO dbo.Flights
	(FlightId, [Key], [Value])
VALUES
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'FlightId', '3074e015-62b2-4f76-a8b1-463c53cd79c5'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'AirlineIATACode', 'VY'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'FlightNumber', '8336'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'DepartureAirportCode', 'RTM'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'DepartureAirportName', 'Rotterdam The Hague Airport'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'DepartureDateTime', '2016-08-12 12:15'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'DepartureTerminal', '1'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'Gate', NULL),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'ArrivalAirportCode', 'BCN'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'ArrivalAirportName', 'Barcelona–El Prat'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'ScheduledArrivalDateTime', '2016-08-12 14:14'),
	('2A0C8B05-682A-41CE-8516-C6070CD92851', 'FlightIsCancelled', '0')

 
So now we have a dataset that consists of 12 key-value pairs:

 
Converting to columns
But when you’re running a query you would like to have columns instead of rows. So by using a PIVOT statement, you can convert these rows into columns:

SELECT
	FlightId,
	AirlineIATACode,
	FlightNumber,
	DepartureAirportCode,
	DepartureAirportName,
	DepartureDateTime,
	DepartureTerminal,
	Gate,
	ArrivalAirportCode,
	ArrivalAirportName,
	ScheduledArrivalDateTime,
	FlightIsCancelled
FROM
	(
		SELECT
			FlightId,
			[Key],
			[Value] 
		FROM dbo.Flights
	) AS SourceTable
PIVOT
	(
	MIN([Value])	--Needs to be an aggregate function
	FOR [Key] IN
		(
			AirlineIATACode,
			FlightNumber,
			DepartureAirportCode,
			DepartureAirportName,
			DepartureDateTime,
			DepartureTerminal,
			Gate,
			ArrivalAirportCode,
			ArrivalAirportName,
			ScheduledArrivalDateTime,
			FlightIsCancelled
		)
	) AS PivotOutput

 
Because the keys are always the same for flights, I can add those Key-names in the PIVOT statement. But because the PIVOT statement needs an aggregate function to retrieve the value, I needed to add the MIN() function but this doesn’t change the output in any way.

Now we have the same dataset, but converted into rows:

 
So from now on, converting key-value pairs to columns shouldn’t be a problem anymore!

Comparing execution plans with SSMS

In SQL Server 2016 (now available as CTP 3.0), a new feature is shipped: Execution Plan Comparison Tool. This new (and very cool) feature allows you to compare 2 execution plans within SQL Server Management Studio (SSMS). And according to Amit Banerjee (Blog | @banerjeeamit), this feature is also available in the “SSMS – September 2015” release (more info here). Let’s take a look at this new feature.

Creating resources
To generate an execution plan that we can compare later on, let’s create a table with some data first:

USE Sandbox
GO


CREATE TABLE dbo.T1
	(ID INT IDENTITY(1,1),
	 VAL VARCHAR(10))
GO


INSERT INTO T1
	(VAL)
VALUES
	('X')
GO 10

 
To generate some execution plans, run the queries below with the “actual execution plan” on, and safe these plans to disk:

SELECT *
FROM T1 AS T1
INNER JOIN T1 AS T2 ON T1.ID = T2.ID

 
and:

SELECT *
FROM T1 AS T1
INNER JOIN T1 AS T2 ON T1.ID = T2.ID
INNER JOIN T1 AS T3 ON T1.ID = T3.ID

 
I’ve also made the 2 plans that were generated available for download here and here.

 
Comparing execution plans
To compare execution plans in, you need to open the first execution plan from SSMS. In the plan-window, right-click and click on “Compare Showplan”:

 
Now a pop-up window will open, that asks you what plan you want to use to compare it with. Now open “Test2.sqlplan”. The compare window opens, and you can compare plans:

 
As you can see, parts of the plan that are the same in both execution plans, are colored the same. These colors are randomly chosen, and can be different every time you compare 2 plans. This makes it easy to determine where both plans are equal or differ.

If you click on one of the highlighted parts in the execution plan, the other plan will center its view on that part of the plan (unfortunately it’s a bit hard to see that with these small execution plans). If you click on a highlighted part, you can see that there is a blue rectangle drawn around the object:

 
Another really cool thing is the properties windows. If you don’t have that open by default, right-click on the first object in the execution plan (the SELECT part), and click on “Properties”. This shows 2 property-windows, in which you can compare the memory grant for both plans for example:

 
Conclusion
At this moment, you need to save both execution plans to disk in order to compare them. If you try to compare an execution plan of a query you just ran without saving it, it throws an exception:

 
But in my opinion this is just a minor issue, and I think this will be fixed in one of the next releases.

Comparing execution plans is definitely something I’ve been missing for years. Especially when you have 2 really big plans, this can really help you speed up the analysis and debug process.

Even though I’m pretty used to using SQL Sentry Plan Explorer to open execution plans, that doesn’t give me the option to compare plans (yet). At least not in the free version of the tool, that I always recommend to colleagues and friends to use.

This is another one of the really cool features in the overhauled SSMS, and I think the SQL Server team is rocking this new release.

Tracking query progress with Live Query Statistics

How frustrating is it, to run a query on a database and it seems to be “stuck”. We’ve all seen that happen right? But how can you tell what the problem is, without letting the query complete (which could take a long time)? Microsoft (or actually the SQL Server team) gave us a new toy to play with that can help us in situations like this, and this tool is called “Live Query Statistics”.

 
Creating resources
To show you how the Live Query Statistics work, let’s create a sample table first, and insert 1.000 rows:

CREATE TABLE LiveQueryStats
	(ID INT IDENTITY(1,1),
	 VALUE VARCHAR(10))
GO

INSERT INTO LiveQueryStats
	(VALUE)
VALUES
	('X')
GO 1000

 
Because we want to actually see something happen, let’s multiply the number of rows coming from the single table by using the query below:

SELECT *
FROM LiveQueryStats T1
CROSS APPLY LiveQueryStats T2
CROSS APPLY LiveQueryStats T3

 
The query will return 1 billion rows (1.000.000.000), and will run for a while. This gives you the opportunity to look at the different features without rushing or losing your running query.

 
Live execution plan
Before running the query above, you need to enable the Live Query Statistics just like you would do to the normal execution plan. This is an extra button added in the SQL Server 2016 SSMS (and the downloadable version of course). When that’s enabled, you can execute the query, and SQL Server Management Studio (SSMS) will automatically switch to the live execution plan.

In this execution plan, you’ll see the data flow through the components, so you can actually see what SQL Server is doing at a specific moment:

 
Query completion percentage
Another great addition is the overall completed percentage, that you can find at the bottom of your SSMS. This shows you the percentage of completion, that can help you estimate the time till completion:

 
Live rowcount
The properties window normally shows you the in-depth information of your query after completion (number of threads, memory grant, etc). With the live execution plan enabled, it can provide you with real-time statistics as well:

 
Live execution plan from Activity Monitor
From the Activity Monitor you can also open the live execution plan of running queries on your instance. In the Activity Monitor, you have an additional tab in SQL Server 2016 called “Active Expensive Queries”. In this tab you can right-click on a running query, and click on “Show Live Execution Plan”. This opens a new tab in SSMS with the execution plan:

 
Unfortunately this only works for queries that have the live statistics enabled before execution. I don’t expect this to change in the final product, mainly because of the negative performance impact this feature can have on your queries and instance.

 
Drawbacks, Limitations and Bugs
Because this is only a CTP version of SQL Server 2016, we can expect some bugs and limitation, so I’ll be the last one to judge. And I don’t think these limitations are a big drawback on the feature.

One of these bugs is a crashing SSMS. If you open a live execution plan from the Activity Monitor, and close that tab, SSMS crashes every now and then.

 
Another weird thing is that exiting SSMS by clicking on the close button (top right) when the live execution plan is open, causes the list of recently used SQL Servers (the list used when connecting object explorer for example) to be cleared for some reason.

This behavior is reproducible, so I think this is a small bug in SSMS, or the fact that I upgraded this instance from the first CTP version till the current version. But I’m sure this will be fixed by the SQL Server Team in the next releases.

 
The biggest pitfall (in my opinion) of this feature is also mentioned in the documentation. This feature is “primarily intended for troubleshooting purposes” and “can slow the overall query performance”. And I know, it look SO COOL to have this on all queries you’re running, but please be careful with this. Don’t enable this on every running query, but only use this to debug issues!

Another limitation for this feature (at least at the moment I’m writing this), is the use in combination with columnstore indexes, memory optimized tables and natively compiled stored procedures. You can read more about this here.

And I shouldn’t even have to mention this, but remember: you can only use Live Execution Plans when you have SHOWPLAN permissions on the database (same permissions you need to view normal execution plans).

 
Alternatives
One of the questions I had when I read about this: are there any alternative for this? One of the only things that come to mind is the “Track My Query” tool, written by Matan Yungman (Blog | @MatanYungman). This tool allows you to monitor your query, and it will show you what part of your query is currently running. For more in-depth information, I recommend the SQLBits session where he explains the inner-workings of this tool. I’ve seen this session in person, and it was an interesting session.

 
Conclusion
With all these awesome new features that will be shipped in SQL Server 2016, I think there’s a whole new way of looking at SQL Server as a product. I’ve always liked working with SQL Server, but this version is taking it to the next level.

With all these new features, debugging issues is going to be a bit easier, developing new stuff is going to be faster, and the overall usability is going to skyrocket. I can’t wait to get my hands on the finished product!

Where to find backup- and restore history

Interesting questions don’t always need to be hard to answer. Earlier this week I was asked “How can I see what backup was restored on environment X?”. The answer is as interesting as the question…

 
Backup
To see what backups are taken, you can take a look at the history of your maintenance job (depending on whatever type you use in your environment). But there is another way: just ask SQL Server.

First, let’s create a new database named Sandbox (before you run the scripts in this blog post, change or create the directories that are used!):

USE master
GO

CREATE DATABASE Sandbox
	ON PRIMARY (NAME = N'Sandbox', FILENAME = N'C:\Database\Sandbox.mdf', SIZE = 4096KB, FILEGROWTH = 1024KB)
	LOG ON (NAME = N'Sandbox_log', FILENAME = N'C:\Database\Sandbox_log.ldf', SIZE = 1024KB, FILEGROWTH = 1024KB)
GO

 
Before we start to backup this database, I want to make sure the information I see is only for this backup. In order to do so, I’m going to cycle the SQL Server error log, and clean all my backup history from msdb. You can do that by running these statements:

EXEC sys.sp_cycle_errorlog
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date =  '2016-01-01'

 
By setting the “@oldest_date” to a date in the future, all backup history will be deleted.

Now that we’ve done that, let’s take a backup of our database:

BACKUP DATABASE [Sandbox]
 TO DISK = N'C:\Database\Backup\Sandbox_2010828_0832.bak' WITH STATS = 10;
GO

 
The backup will complete in an instant, because there is no data to backup:

 
The result of the backup can be found in 2 locations. The first one is the SQL Server error log:

EXEC xp_readerrorlog

 
If you search in the error log you can find the exact time the backup was taken, the amount of pages that were processed, the location of the backup file, etc (image clickable for larger version):



 
The SQL Server error logs are also available from the SQL Server Management Studio (SSMS). If you connect to your instance with the object explorer, and navigate to Management -> SQL Server Logs, you can find the same information.

The second location you can use to retrieve this information is msdb. The msdb contains the history of your backups:

 
You can retrieve this information by running a query on the backup-tables:

SELECT
	B.user_name,
	B.database_name,
	M.physical_device_name,
	B.backup_start_date,
	B.backup_finish_date,
	CASE B.type
		WHEN 'D' THEN 'Database'
		WHEN 'L' THEN 'Log'
	END AS BackupType
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily M ON M.media_set_id = B.media_set_id

 
Restore
The information for restored backups can also be found in the same 2 locations. Let’s take a backup of the Sandbox database first:

USE master
GO

RESTORE DATABASE Sandbox
FROM DISK = N'C:\Database\Backup\Sandbox_2010828_0832.bak' WITH REPLACE, STATS = 10
GO

 
If we look at the SQL Server error log again we can find the restore time, used backup, etc:



 
We can also query the msdb again. But this time, we need to use the restore-tables:

SELECT
	destination_database_name,
	user_name,
	restore_date,
	destination_phys_name
FROM msdb.dbo.restorehistory H
INNER JOIN msdb.dbo.restorefile F ON F.restore_history_id = H.restore_history_id

 
This shows you all recent restores on your instance:

 
Conclusion
Being able to retrieve the information you need to determine which database was backed-up or restored can help you solve some weird issues. In my case, it explained why a certain record wasn’t restored on environment X: it was inserted after the backup was taken. Issues like that can be quite hard (or even impossible) to find without information about backups and restores.