Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sqlcmd is not exiting with capital -Q option

Posted on 2011-03-15
6
Medium Priority
?
793 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

885 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