Solved

sqlcmd is not exiting with capital -Q option

Posted on 2011-03-15
6
762 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
export sql results to csv 6 36
MS SQL / SQL Server Native Client -- how to prevent seeing other servers? 2 25
Loops and updating in SQL Query 9 30
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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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