Restore of database failed! What now?

Restoration of a database from a backup file sometimes can be very tricky. Especially when you don’t know on what server (what environment or what SQL Server version) it was taken. Sometimes you, as a DBA, are just asked to restore database from a given backup on the pointed server. You have got a backup file, you do everything as always but for some reason, the restore operation fails.

Restore of database failed

In this blog post, I describe what is the reason behind the below error.

SSMS GUI error

Error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'AdventureWorks2017' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.5026. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

So… what we can do in such case?

Let’s ask for help 🙂

First of all, as you already noticed, in the left bottom corner we have a help button. Did you try to use it? I can bet you didn’t. Let’s see how Microsoft will try to help us in our case.

SSMS GUI error - get help 01

As you can see the help for the first error is not available. This option in the menu is grayed out. However, the help for the second, more detailed error seems to be available. It is quite promising, isn’t it? When we click this we’re getting a new dialog with the notification that some data will be sent to Microsoft and we need to agree on that if we want to see the help for our problem.

SSMS GUI error - get help 02

Product Name, Product Version, and LinkId… I think I’m not afraid to share this data if that suppose to give me a solution for my problem. So what I get after clicking [Yes] button? I get nothing… New webpage opens in my browser and the only thing we get is an advertisement to buy new Surface Pro… I’m not kidding…

SSMS GUI error - surface

Additional funny thing is that Microsoft collects data about SSMS version I use: 14.0.17254.0+((SSMS_Rel_17_4).180502-0908), but why they described it as release 17.4 while I use 17.7?

SSMS version

Ok. Now we know that MSFT will not help us in this case.

Let’s try using T-SQL

We’re not able to restore a database using SSMS GUI so maybe it will work when using T-SQL? Let’s give it a try:

USE [master]
RESTORE DATABASE [AdventureWorks2017] 
FROM DISK = N'C:\iso\DB - AdventureWorks\AdventureWorks2017.bak' WITH FILE = 1, 
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SS2016\MSSQL\DATA\AdventureWorks2017.mdf', 
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SS2016\MSSQL\DATA\AdventureWorks2017_log.ldf', 
NOUNLOAD, STATS = 5
GO

No, it doesn’t work neither.

Msg 3169, Level 16, State 1, Line 2 The database was backed up on a server running version 14.00.1000. That version is incompatible with this server, which is running version 13.00.5026. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
That version is incompatible with this server

So what does it mean? It simply means that database backup was taken on newer SQL Server version than the SQL Server version on which you’re trying to restore it. Unfortunately, such an operation is not supported. All SQL Servers are backward compatible and you’re always able to restore a database from a backup taken on an older version to the newer one but not vice versa.

If you want to decrypt build numbers from the error message you can use this simple cheat sheet:

Build number SQL Server version
14.0 SQL Server 2017
13.0 SQL Server 2016
12.0 SQL Server 2014
11.0 SQL Server 2012
10.50 SQL Server 2008 R2
10.0 SQL Server 2008
9.0 SQL Server 2005
8.0 SQL Server 2000
7.0 SQL Server 7.0

You can find much more details about SQL Server builds on this page: https://sqlserverbuilds.blogspot.com/. I recommend, to add it to your bookmarks in your favorite browser. It’s invaluable when you need to quickly check SQL Server version or find latest Service Pack or Cumulative Update.

Now, armed with this knowledge, you know that this database backup file has been created on SQL Server 2017. That is the reason why I cannot restore it on SQL Server 2016.

On what SQL Server version this backup was created?

You don’t have to try to restore a database from a backup file in order to check on what version it was created. You can safely verify it using simple RESTORE HEADERONLY command.

RESTORE HEADERONLY FROM DISK = 'C:\iso\DB - AdventureWorks\AdventureWorks2017.bak';
GO

In the resultset, you will find such information as:

  • Backup Name and Description
  • Who created it and on what Server (Login Name, Server Name, and version)
  • Database Name
  • Creation Date (Start and Finish)
  • and much more…

SQL Server RESTORE HEADERONLY

What to do when we cannot restore database from backup?

You already know that you will not be able to restore your database on the SQL Server you need. What can you do in such a situation? The solution is simple – you need to use a different database migration method. Here is a short list of few possibilities you have:

Option 1

In the case of very small databases, you can use SSMS to generate the SQL script that includes schema and data (INSERT statements). In next step, you can use this script to generate a new database on the target server.

Option 2

For bigger databases, you can generate the SQL script with the schema only and then use it to create an empty database on the target server. In the second step, you can use Import and Export Wizard or BCP command to migrate data from one database to another.

Option 3

You can also use the Export Data-Tier Application functionality to generate BACPAC file consisting of database schema and data. On the target server, you can use the Import Data-Tier Application functionality to create the new database from this file.

Do not mistake DACPAC with BACPAC. The former includes only database schema, and the latter includes database schema and data.

Option 4

Another possibility is to use the Copy Database Wizard with the SMO transfer method.

Option 5

The last solution is to use some available third-party tools that deliver Data Compare functionality.

Thanks for reading!

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

I’m in the TOP 30 SQL Server Bloggers of 2018

Top 30 SQL blog 2018Today, I accidentally discovered that I’m in the TOP 30 SQL Server Bloggers of 2018 by SQLShack. This is a big surprise and I’m very happy that I received such amazing recognition. It’s a great feeling to be on such a list with all these well-known #SQLFamily members.

I couldn’t imagine more perfect timing. Speaking quite frankly, I have a hard time to get back to blogging after my vacation break and this recognition is a huge motivation for me to resume work on my posts.

I will try to publish my blog posts regularly in September, however, it may be difficult because I’m also preparing to pass the AWS Certificate exam by the end of this year. Keep fingers crossed!

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

The most useful Docker commands for DBA

In my latest blog post, I described How to start SQL Server Docker container in few simple steps. Since then I was playing a bit with Docker and now I want to share with you a list of the most useful Docker commands.

Check Docker version

You can check Docker version very easily using this command:

docker version

Example output:

Docker version

List downloaded images

To display a list of already downloaded (pulled) images use one of these commands:

docker image ls
docker images

Example output:

Docker images

Search for new image

You can use https://hub.docker.com/ to search for new images, but you can do this also from command line.

docker search [searchphrase]

Example output:

Docker search

Pull the image

To pull (download) the image you need to run this command:

docker pull <imagename>

Example output:

Docker pull

Run new container

To run new container use this command:

docker run <imagename>

Example output:

Docker run hello-world

Sometimes you need to provide additional parameters.

docker run --name <CONATINER_NAME> -d -p 1433:1433 -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
Docker run mssql
List containers

To list running containers use:

docker ps

Docker ps

To list all created containers use:

docker ps -a

Docker ps -a

Stop container

To stop container:

docker stop <container ID>

To stop all running containers:

docker stop $(docker ps -a -q)
Start container

To start container:

docker start <container ID>

To start all containers

docker start $(docker ps -a -q)
Remove container

To remove container:

docker rm <conatiner ID>

To remove all containers:

docker rm $(docker ps -a -q)
Remove image

To remove image:

docker rmi <image ID>

Docker rmi

Copy files

To copy files between host and container use cp command:

docker cp <Container ID>:<Container path> <host path>
docker cp <host path> <Container ID>:<Container path>

 

I hope you find this list useful. Thanks for reading!

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

How to start SQL Server Docker container in few simple steps

SQL Server 2017 is the first SQL Server version that is available on Windows, Linux, and in Docker containers. In this blog post, I will show you how to create your first SQL Server Docker container in a few very simple steps.

What is Docker?

Docker is an open-source container platform provider written in GO programming language. It’s available for Linux, Windows, and MacOS. In addition, it also works in Azure and AWS environments. Containers allow developers to package up an application with all of the parts it needs, such as libraries and other dependencies, and deliver it all out as one package. It’s something similar to Virtual Machines but without the guest operating system. They’re isolated, but they share Host OS. That makes them smaller and more lightweight.

container vs vm

Docker installation

Currently, for Windows users, Docker is available in two editions. Docker Community Edition for Windows (CE) is a free solution designed for Windows 10 users. Docker Enterprise Edition for Windows Server (EE) is dedicated for Windows Server 2016 users. There is also third (legacy) solution – Docker Toolbox with support for Windows 7 and 8. However, this edition has some additional requirements and limitations, for example, it supports only Linux containers.

Docker CE

To install Docker CE you need to follow two very simple steps:

  1. Download Docker CE installer from Docker Store.
  2. Double-click Docker for Windows Installer.exe and follow the install wizard to accept the license, authorize the installer, and proceed with the install.
Docker EE

Installation of Docker EE is more automated and can be performed using PowerShell:

  1. Open PowerShell command prompt and run following command to install Docker:
    Install-Module DockerProvider -Force
    Install-Package Docker -ProviderName DockerProvider -Force
  2. If prompted to install any prerequisites such as NuGet, type in “Y”.
  3. Run the following command to check if a reboot is required:
    (Install-WindowsFeature Containers).RestartNeeded
  4.  If the result is Yes, restart your computer with this command:
    Restart-Computer
    Docker Toolbox

Installation of Docker Toolbox is quite similar to the installation of Docker CE.

  1. Download Docker Toolbox installer.
  2. Install Docker Toolbox by double-clicking the installer.

The installer installs following software on your machine:

  • Docker Client for Windows
  • Docker Toolbox management tool and ISO
  • Oracle VM VirtualBox
  • Git MSYS-git UNIX tools

Installation validation

When Docker is already installed and running you may want to check if it works properly. For Docker CE and EE, start PowerShell command prompt. For Docker Toolbox, open Docker Toolbox Terminal (Docker QuickStart  Terminal). Then run the following command to check your installed version:

docker --version

docker version

Next step is to check if Docker can pull and run containers:

docker run hello-world

docker run hello-world

Creating SQL Server Docker container

Currently, Microsoft provides three SQL Server Docker containers:

You may look for them on Docker Hub page or you also may run the following command to list available images:

docker search microsoft

docker search microsoft

Pull needed image

The first thing to start your container is to pull the image from the repository. Depending on what image you want to use you need to execute one of the following commands:

docker pull microsoft/mssql-server-linux
docker pull microsoft/mssql-server-windows-developer
docker pull microsoft/mssql-server-windows-express

Important: Docker Toolbox supports only Linux containers. Docker CE and EE support Linux and windows containers, however, to pull and run docker image without any issues you need to ensure that your Docker installation is running in the proper mode. You may do it by right-clicking on the Docker whale icon in your tray docker icon.

I decided to download SQL Server Developer on Windows image.

docker pull

To check what images are downloaded run the following command:

docker images

docker images

Run new container

Finally, you’re ready to run your container. To do it you need to execute docker runcommand and provide few mandatory parameters.

  • --name – provide a name for your container
  • -d – run container in detached mode (in the background)
  • -p – publish a container᾿s port or a range of ports to the host. For example -p 14333:1433 maps port 14333 on the host to port 1433 in the container.
  • -e – create environment variables under the container execution runtime
    • sa_password (to assign the SQL Server SA password)
    • ACCEPT_EULA (to accept end-user license agreement)

Example:

docker run --name <CONATINER_NAME> -d -p 1433:1433 -e sa_password=<SA_PASSWORD> -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

docker run mssql

Here you can find more details about docker run command.

How to connect to your new SQL Server?

The last thing is to connect to new SQL Server instance. To do this open SQL Server Management Studio and as Server name provide localhost with the port you specified for mapping. Then, choose SQL Server authentication and log in using sa account (with the password you specified during container setup).

SSMS - connect

Now you can run any queries you want.

SSMS - query

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

QuickQuestion: How to start and stop SQL Server instance from a command line?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.

Today’s question:

How to start and stop SQL Server instance from a command line?

Windows

SQL Server instance as any other Windows network service can be managed using the NETcommand from elevated Command Prompt (Run as Administrator).

net options

To start default SQL Server instance use following command:

net start "SQL Server (MSSQLSERVER)"

or

net start MSSQLSERVER

To start named SQL Server instance use following command:

net start "SQL Server ( instancename )"

or

net start MSSQL$ instancename

SQLServer - net start

To stop default SQL Server instance use following command:

net stop "SQL Server (MSSQLSERVER)"

or

net stop MSSQLSERVER

To stop named SQL Server instance use following command:

net stop "SQL Server ( instancename )"

or

net stop MSSQL$ instancename

SQLServer - net stop

To restart instance you need to first stop it and then start it.  On Windows, there is no one single command that can be used for this purpose.

Linux (RHEL and Ubuntu)

On Linux, we don’t have yet named instances, so all commands are executed against default instance. SQL Server can be managed using thesystemctl command.

To check the current state of SQL Server instance you can run this command:

sudo systemctl status mssql-server

To start instance run following command:

sudo systemctl start mssql-server

SQLServer - systemctl start

To stop instance run following command:

sudo systemctl stop mssql-server

SQLServer - systemctl stop

To restart instance run following command:

sudo systemctl restart mssql-server
Docker

Docker provides us a very similar set of commands.

To start container run following command:

docker start <container ID>

SQLServer - docker start

To stop container run following command:

docker stop <container ID>

SQLServer - docker stop

To restart container run following command:

docker restart <container ID>

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

QuickQuestion: How to enable a trace flag on SQL Server startup?

QuickQuestion series is a series of short posts in which I answer database related questions asked by my colleagues, friends, and co-workers, mainly application developers.

Today’s question:

How to enable a trace flag on SQL Server startup?

If you want to have a certain Trace Flag enabled after every SQL Server startup, then you have to set it as one of SQL Server startup parameters. To do that you need to execute few simple steps.

  1. Open SQL Server Configuration Manager (as Administrator).
    TraceFlag - SQLServer Configuration Manager
  2. Open Properties of the SQL Server instance you want to configure.
    TraceFlag - SQLServer Configuration Manager - properties 1
  3. As a next step, go to Startup Parameters tab. Provide new startup parameter. For Trace Flag it will be -T#### (where #### needs to be replaced with Trace Flag number). Click Add button.
    TraceFlag - SQLServer Configuration Manager - startup parameters
  4. Notice that additional parameter was added to the parameters list. Click Apply button.
    TraceFlag - SQLServer Configuration Manager - startup parameters 2
  5. You will get the warning that SQL Server instance needs to be restarted in order the change takes effect.
    TraceFlag - SQLServer Configuration Manager - startup parameters 3
  6. Finally, you have to restart your SQL Server instance.
    TraceFlag - SQLServer Configuration Manager - restart

That’s it.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

SQLDay2018 – Brief introduction into SQL Server Execution Plans

Last week I had a great pleasure to speak at SQLDay 2018. For those who don’t know – it is the biggest conference focused on databases, data platform, big data, advanced data analysis and business intelligence in Poland. It has been organized for 10 years already and is a great opportunity to meet many special people. This year it was a great chance to attend workshops and sessions delivered by real SQL Server experts: Itzik Ben-Gan (b|t), Kalen Delaney (b|t), and Niko Neugebauer (b|t), and many many more…

I had an amazing occasion to present my session titled Brief introduction into SQL Server Execution Plans.

Here you can find my slide deck and materials: SQLDay2018 – Brief introduction into SQL Server Execution Plans

I also posted it on my SlideShare profile.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

24 HoP – SQL Operations Studio – slide deck

Last Thursday I had a great pleasure to present a session during 24 Hours of PASS Cross-Platform SQL Server Management. 24HOP is an exceptional online event that provides free training for database professionals. It’s 24 back-to-back hours webinar series that features a new 60-minute webinar each hour.

I had an amazing occasion to present my session about SQL Operations Studio – a New Multi-Platform tool for SQL Server Database Development, Administration, and Monitoring.

Here you can find my slide deck: SQLOps

I also posted it on my SlideShare profile.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail

A list of the software I use

Two weeks ago I was blogging about The Essential SQL Server Toolkit in my stack. That reminds me of an idea I had in January to post a list of software I usually use. At that time I was forced to create such list for myself because I was changing my laptop to the new one. I decided to post it here, so next time when I will have to reinstall my laptop I will have everything already in one place. Additionally, maybe you will find something interesting here as well.

Basic
  • Microsoft Office – Word, Excel, Outlook, Powerpoint, OneNote. (link)
  • Chrome – Web browser. (link)
SQLServer
  • Microsoft SQL Server – I  think this doesn’t require any explanation. (link)
  • Microsoft SQL Server Management Studio – Data Management Tool. (link)
  • Microsoft SQL Operations Studio – Data Management Tool. (link)
  • Microsoft SQL Server Data Tools – SQL Server Tools for developers. (link)
  • RML Utilities – Tools to help database administrators manage the performance of Microsoft SQL Server. (link)
  • DiagManager – Graphical PSSdiag configuration manager. (link)
  • SQLNexus – Tool for PSSDiag data analysis. (link)
  • SQL Sentry Plan Explorer – Query analysis and tuning tool. (link)
  • Microsoft Data Migration Assistant – Tool for database compatibility issues detection. (link)
  • Microsoft Database Experimentation Assistant – Tool for evaluating a targeted version of SQL for a given workload. (link)
Other data platforms
  • DB Browser for SQL Lite – Data Management Tool for SQL Lite (link)
  • pgAdmin – Data Management Tool for PostgreSql. (link)
  • Neo4J – Graph Platform. (link)
Programming
  • git – Version control system. (link)
  • Git Extensions – Graphical User interface for git. (link)
  • TortoiseGit – Windows Shell Interface to Git and based on TortoiseSVN. (link)
  • Microsoft Visual Studio – Integrated Development Environment. (link)
  • Microsoft Visual Studio Code – Lightweight Code Editor. (link)
  • Notepad++ – Code editor. (link)
Frameworks
  • Java – Java Runtime Environment. (link)
  • .Net Framework – (link)
  • Microsoft SQL Server Data-tier Application Framework (DACFx) – (link)
Communication
  • Cisco Webex Meetings – Video conferencing and screen sharing. (link)
  • Skype – Video chat and voice calls. (link)
  • Slack – Collaboration tool. (link)
Miscellaneous – Work
  • 7-zip – Great file archiver. (link)
  • Adobe Acrobat Reader – PDF Reader. (link)
  • KeePass – Passwords manager. (link)
  • Putty – SSH and telnet client for Windows. (link)
  • Total Commander – File manager for Windows. (link)
  • WinMerge – File differencing and merging tool. (link)
  • WinScp – SFTP, SCP, and FTP client for Windows. (link)
  • Microsoft PowerBI Desktop – For creating live, interactive reports. (link)
  • Remote Server Administration Tools – For remote management rolses and features in Windows Server. (link)
  • OneDrive – File hosting service. (link)
  • VirtualBox – General purpose Virtual Machines host. (link)
  • Microsoft Visio – Diagram and charts creation. (link)
Miscellaneous – Entertainment
  • K-Lite Codec Pack – Pack of video and audio codecs. (link)
  • BESTPlayer – Video player with subtitles support. (link)
Miscellaneous – Blogging and presenting
  • GIMP – Photo/Graphic editing program. (link)
  • Pain.NET – Photo/Graphic editing program. (link)
  • ScreenToGif – Screen, webcam and sketchboard recorder with an integrated editor. (link)
  • Sizer – freeware utility that allows you to resize any window to an exact, predefined size. (link)
  • ZoomIt – screen zoom and annotation tool for technical presentations. (link)

Wow… I have much more items on my list than I expected. However, I know this list may be not complete and some very valuable application may be missing here.

Drop a comment if you found this list useful or if you think I should try out some other software you already use for a long time.

-Marek

Share it:
Facebooktwittergoogle_plusredditpinterestlinkedinmailFacebooktwittergoogle_plusredditpinterestlinkedinmail