Solved

sqlcmd is not exiting with capital -Q option

Posted on 2011-03-15
6
755 Views
Last Modified: 2012-05-11
I have the same problem with a restore:

sqlcmd -Q "Restore Database ABC From Disk = 'C:\Backup\ABCData_Backup_201103150200.bak' With Replace" -o C:\Backup\RestoreCBRS_20110315.log

This restores the db but does not exit when run manually or using the task manager and stays open with the >1 prompt.  Other operations on the same server work .  I have to scipt it do get the current date and embed it in the filename.   It's used to replicate the db on a test server from a production server I am not allowed to connect to.

Suggestions?

Joe
0
Comment
Question by:AdminTSC
  • 4
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35143058
I just tried it with my copy of SQLCMD and connected to a local SQL 2008 server and it performed the command and then quit, as it should. Can you confirm the version of both the client tools (SQLCMD) and the SQL Server you're connecting to?
0
 

Author Comment

by:AdminTSC
ID: 35158072
Hi Ryan,

I'm on SQL 2008 R2.  With SQLCMD, I'm not don't know how to confirm the version outside of the exe date stamp.  I have one dated 2/10/07 in C:\Program Files\Microsoft SQL Server\90\Tools\Binn and one dated 4/3/10 in C:\Program Files\Microsoft SQL Server\100\Tools\Binn.  I renamed the older one to confirm I'm using the newer and searched the server's drive thoroughly.  My ID is both a SQL and Win admin, so I'm sure I can see everything.

I may look into using a stored procedure, which execute and exit okay.  I only need to figure out how to embed the system date into the BAK file name.  It just seems odd I can do other things with SQLCMD with an automatic exit like EXEC and BACKUP, but not RESTORE.  Maybe it's another undocumented Microsoft feature protecting us from ourselves even when we know what we're doing.

Joe
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35167269
Hi, you need to run sqlcmd with date variable? Please create batch file and insert following code:
 
SET stamp=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%
SET newdate=%date:~0,4%%date:~5,2%%date:~8,2%

sqlcmd -Q "Restore Database ABC From Disk = 'C:\Backup\ABCData_Backup_%stamp%.bak' With Replace" -o "C:\Backup\RestoreCBRS_%newdate%.log"

Open in new window

You can schedule this to run as scheduled task, you can use sql server jobs.

Attention: I've set date using yyyy-mm-dd and hh-mm-ss date and time formats if it is different in your system you can easily fix it or let me know I'll send you proper code.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AdminTSC
ID: 35168281
Thanks, Dan, but I already have that.  The issue is not with the appending the date to the sqlcmd statement.  It is I cannot get it to exit from the sqlcmd prompt using the -Q option, which when used with the capital letter Q instead of lower case, is supposed to exit when done, which it is not.

The alternative, which you might know, is to append the date to the backup file name on the fly in a stored procedure, which sqlcmd works okay on and exits.
0
 

Accepted Solution

by:
AdminTSC earned 0 total points
ID: 35168869
Never mind.  I set up a stored procedure with

Declare @BAKName NVarChar(255)
Set  @BAKName = 'C:\Backup\ABCData_Backup_'+Replace(Convert(VarChar(10),getdate(),111),'/','')+'0200.bak'
Restore Database ABC From Disk =  @BAKName With Replace
 
Where I run the stored procedure with the RESTORE command, it works.  There must be something keepking sqlcmd from running it directly.
0
 

Author Closing Comment

by:AdminTSC
ID: 35196742
It's an acceptable workaround I figured out myself before I got an answer from others.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now