SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!

Let us see how DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.

Here is the basic script for Backup and Restore

Note: name of my database server is touch and I am connecting it with windows authentication.


BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'


RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'

Here is the basic script for Backup and Restore using SQLCMD


C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"


C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"

Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.

Watch a 60 second video on this subject

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Fix Visual Studio Error : Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL

In one of the virtual environment while I was trying to add SQL Server Database (.mdf) file to asp.net project I encountered following error:

Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: 

For a long time I am using SQL Server 2012 but this error was a bit interesting to me. I realize that there should not be any need of the SQL Server 2005 installation. I quickly figured out that I can remove this error if I do as mentioned below:

  • Open Microsoft Visual Studio
  • Select Tools >> Options >> Database Tools >> Data Connections
  • Enter the name of an installed instance in “SQL Server Instance Name” field.
  • Click OK

If you do not know the instance name, you can follow either of the options.

1) Use the command line sqlcmd utility

2) Using SQL Server Management Studio

Is there any other way to resolve this error?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Connecting to Server Using Windows Authentication by SQLCMD

Recently I got a call from an old friend I used to call “DJ”. Here is the exact conversation we had:

DJ: Pinal, your SQL Server does not like me.
Pinal: Possible, no one likes you!
DJ: I am serious…
Pinal: I am too…
DJ: No really – be serious.
Pinal: Well, you started it, tell me your problem.
DJ: This new SQLCMD thingy does not work. Everytime I am using my username and password it does not log me in.
Pinal: Have you tried with SSMS?
DJ: No, but it works fine. I just logged in my machine using it.
Pinal: Oh, are you trying to login using your Windows Authentication Username and Password?
DJ: Does it matter? I am the admin on my box.
Pinal: It does matter. Tell me your script.
DJ: sqlcmd -S .\sqlent1 -U username -P password. And then I’d been given “error 18456 – login failed for user.”
Pinal: Try sqlcmd -S .\sqlent1 -E
(After a while)
DJ: It works! So you still know SQL Server, what’d you say?
Pinal: I say  you still do not know SQL Server (evil laugh)
DJ: (extends the evil laugh)

Well, it was simple. He was trying to connect to SQL Server which was installed on his local box, where he was the admin. In this case, he can use a simpler script. This is a very specific situation and it was not the production server’s fault. Here is a copy of the scenario on my personal laptop.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Watch the video on the same subject:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

I had previously written two articles about an intriguing observation of triggers online.

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.

Let us recreate the scenario first and attempt to solve the problem.

/* Create Audit Database */
USE AuditDb
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
/* Create Logon Trigger */
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
USE master

After the database is dropped and we try to login again the following error will be displayed.

Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This error could have been evaded if the Trigger was dropped prior to dropping database.

USE master

Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC  Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.

Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.

C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
2> GO

I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network

I recently had requirement to create list of all the SQL Server on local network. I remembered that I had written similar script a year ago SQL SERVER – Script to Find SQL Server on Network. When I looked at it, I realize that I had written it for SQL Server 2000 and used “isql” utility, which is deprecated now. I quickly wrote down updated script using “sqlcmd”. Command “osql” still works in SQL Server 2008.

Go to command prompt and type in “osql -L” or “sqlcmd -L”.

Note one change between osql and sqlcmd is that osql has additional server “(local)” listed in the servers list which is in fact same as “SQL” in my case. While “sqlcmd” gives accurate result.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.


Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

A day before I wrote article SQL SERVER – sqlcmd vs osql – Basic Comparison. Today while I was displaying how sqlcmd can be used instead of osql to one of my companies team leader, I found another neat feature of SSMS Query Editor. sqlcmd can be used from Query Editor but it has to be enabled first.

Following image display how sqlcmd can be enabled in Query Editor. Go to SSMS Menu >> Query >> (click on ) SQLCMD Mode.

Now on Query Editor will support sqlcmd mode. Let us see following example where script contains operating system commands as well SQL commands.

SELECT @@Version ServerVersion
!! Dir "C:\Documents and Settings"
!! Date /T

Once above command has ran following output is displayed in Result Window.

Interesting Observation:
What really is interesting is that out of complete batch all the operating system commands runs first and those are followed by SQL commands. The order of SQL and operating system does not matter in resultset.

I want to throw my above observation to all of you and want to get your feedback about that. Have you faced this situation or scenario? What is the solution some we want to execute SQL and OS commands in order?

Watch the video on the same subject:

Reference : Pinal Dave (http://blog.SQLAuthority.com)