Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

sqlcmd is not exiting with capital -Q option

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
AdminTSC
Asked:
AdminTSC
  • 4
1 Solution
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
AdminTSCAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
AdminTSCAuthor Commented:
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
 
AdminTSCAuthor Commented:
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
 
AdminTSCAuthor Commented:
It's an acceptable workaround I figured out myself before I got an answer from others.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now