Solved

sqlcmd is not exiting with capital -Q option

Posted on 2011-03-15
6
781 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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