.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Backups from SQLcmd ??

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server

I am working with SQL Express and found myself surprised when I tried to automate a backup...  No Agent.

So I am trying to create a simple backup script to run from the command line and probably schedule through the scheduled task manager.

here is my problem.  I get the following error :

Msg 2812, Level 16, State 62, Server FIREFLY\SQLEXPRESS, Line 1
Could not find stored procedure 'B'.

Here is the batch file

sqlcmd -i c:\temp\test\DBbackup.sql -o c:\temp\test\output.txt -S FIREFLY\SQLEXPRESS

And here is the sql input file :

BACKUP DATABASE [DNNDEV] TO  DISK = 'C:\temp\test\dnndev.bak' WITH NOFORMAT, NOINIT,  NAME = 'dnndev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

If I put a USE statement in front of my backup statement - the error message changes to

Could not find stored procedure 'U'.

So it would appear to list the first character encountered.

As a sanity check I created a similar process which does a select * from a table and I do not get an error saying :Could not find stored procedure 'S'. - instead all is well and I get my output and no error

If I try the backup command in SQLCMD interactively it works.

so what the heck am I missing ?

Thank you for any help...


View Complete Post

More Related Resource Links

SQLCMD: supress "Changed database context to . . ."

Product:  2008 SQL Server Standard Edition. Shell:     SQLCMD (i.e. sqlcmd -i"test.sql")Script (test.sql):   use Master;   print $(SQLCMDERRORLEVEL)The screen output is always:   Changed database context to 'Master'   0However I want the above output to be (only):   0I have tried calling SQLCMD with the -m command line parameter set to each of the {-m-1, -m 0, -m 1} settings, but none work. I have tried: set SQLCMDERRORLEVEL = each of {-1,0,1,25), but none workAnybody know the secret?Thanks in advance,Jerry

SQLCMD: Changed Database Context messages

Is there a way to suppress the "Changed Database Context to " messages in SQLCMD?  For example, I get the following: 1> use master2> goChanged database context to 'master'.1>I am trying to migrate some legacy batch files that called isql and used the output files.  These extra "Changed Database Context to " messages are causing our scripts to break, and I was hoping to avoid having to write something extra to handle these messages.Thanks.

sqlcmd, cannot connect to server

I have SQL Server installed on a machine called SQLDEV. I log on to that machine and I run the command sqlcmd -s SQLDEV -Q "select * from <db>.<schema>.<some_table> -E This command works and returns a result set.   Next, I go to a different machine. On that machine I can use SQL Mgmt Studio to log on to SQLDEV using window authentication. However, if I run the same command as above, I get some kind of a connection error. The error says, ".... When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections .....   Any suggestions/ideas why I am getting this error?

Can I safely perform full backups without breaking log shipping? Can I do point in time restores if

I'm building a system using SQL Server 2008. I have log shipping set up across our WAN, and that's working fine. I need to perform local backups on the primary server so that I'm not relying on the (slow) WAN if it needs to be recovered from a server crash. Ideally, it would be nice to be able to perform point in time restores. I understand that I can use the transaction log backups, created for log shipping, to restore from. But I still need a full backup to start the restore from - and there seems to be some confusion regarding whether or not performing a regular full backup on the primary server will affect log shipping. Even on these forums, conflicting advice has been given, with some people saying it's fine as long as you don't do transaction log backups, and others saying you must run COPY ONLY backups (though they were talking about Server 2005). From what I understand, you can't do point in time if you use copy-only backups. I'm getting the impression that this used to be a problem under 2005, but under 2008 you can safely perform full backups while log shipping is running. Can anyone confirm my understanding before I make a career-altering error? :)

SQL Server 2005 Log Shipping and subsequent FULL backups that are needed

Afternoon,   I have a few Log Shipped DBs that are working great.   Currently they are set to fire off every 15 minutes 24/7.   My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.   If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?   Is there a special method to accomplish this?   As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?   I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.   Thanks

What type of backups create a LSN No.?

What type of backups create a LSN No.?"SQLSERVER DBA" "INDIA"

messages from sqlcmd

Would someone please explain this behavior? I'm trying to understand why the messages are coming back the way they are in the following examples. Create a simple table to which we will be inserting some values. create table test_table ( col1 int ) Next, create a sql script that inserts a bunch of integers to the above table. insert into test_table values(1) insert into test_table_ values(2) insert into test_table_ values(3) insert into test_table_ values(4) And save this file. I saved it as c:\dba\admin\test_script.sql Next, run this script from a command window using sqlcmd as follows. sqlcmd -S <your_server> -d <your_db> -i c:\dba\admin\test_script.sql   What message do you get back in the command window? I get "(1 rows affected)" for the first insert statement in the script, but nothing about the other 3 inserts. However, when I select from the table, I do see that all 4 insert statments happened. So, why am I seeing an incomplete message? Now, for the heck of it, I put a "GO" between the insert statements in test_script.sql like so. insert into test_table values(1) go insert into test_table values(2) go insert into test_table values(3) go insert into test_table values(4) Now, run the sqlcmd again like so, sqlcmd -S <your_server> -d <your_db> -i c:\dba\admin\test_script.sql What messages do you get? I get 4 lines of

Can connect via SSMS but not SQLCMD/OSQL

I can connect to my SQL 2008 Express Edition server using SSMS.  I cannot connect using OSQL or SQLCMD and my application is having sporadic connection issues.  When I attempt to connect via OSQL, the following error is returned: Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. The following are errors present in the SQL errorlog:  Error: 17806, Severity: 20, State: 2.  SSPI handshake failed with error code 0x8009030c while establishing a connection iwth integrated security; the connection has been closed. [CLIENT: <local machine>] Error: 18452, Severity: 14, State: 1.  Login failed.  The login is from an untrusted domain and cannot be used with windows authentication. I see an error in the application event log: Event 6037, LsaSrv  The program sqlservr.exe, with the assigned process ID 1788, could not authenticate locally by using the target name MSSQLSvc/{srvnm}.{workgrp}.net:{SQLinstancenm}.  The target name used is not valid.  A target anme should refer to one of the local computer names, for example, the DNS host name.  Try a different target name. Has anyone else experienced this issue - what else can I look into?      

sqlcmd - linkedserver - ALL queries fails after FIRST error from remote server - ANONYMOUS LOGON

Hello, I have very strange problem with sqlcmd , it stop working after first error returned from remote sql server (like permission denied), lets try show what I'm doing, configuration is quite simple C- client (windows 2003 x64, standalone sqlcmd client sp3) S1 - server1(windows 2003 x64, sql 2005 sp3) S2 - server2 (windows 2003 x64, sql 2005 sp3) I need connect via middle Linked Server (S1) from C to to S2, each machines have the same LOCAL Windows account C\LSUser, S1\LSUser, S2\LSUser with the same password, LSUser is not domain account, linked server is configured for Windows Account Impersonation (checked Impersonate) Example, query executed from client C: 1>exec [LS1].[DB].[dbo ].[ListTab1]; - works well, no problem 1>exec [LS1].[DB].[dbo ].[ListTab2]; - works well, no problem ok , now try modify permission on S2, I explicitly deny execute procedure [DB].[dbo ].[ListTab1] on S2 to S2\LSUser, 1>exec [LS1].[DB].[dbo ].[ListTab1]; Msg 229, Level 14, State 5, Server S2, Procedure ListTab1, Line 1 The EXECUTE permission was denied on the object 'ListTab1', database 'DB', schema 'dbo'. expected message :), but look now: 1> exec [LS1].[DB].[dbo ].[ListTab2]; Msg 18456, Level 14, State 1, Server S2, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. all remote queries STOP working from client C 1> select top 10 * from [L

Large transaction log files after backups

I have noticed that the size of the transaction logs on my databases have rocketed lately, I do have full and differential backups in place for the databases in question, any reason for the sudden increase in size and is there anything that I can do to mitigate it. Some of the databases have a simple recovery model on, in which case transaction logs shouldn't be maintained, but I note that the sizes are still huge and this only started when I began taking full backups and differentials. Thanks.

VBNET2008 SQLCMD.Parameters

Hi Good Guys, I need your help with sample coding. Please help me. I am trying to insert a new record into SQL SERVER 2000 TBLSales table using SQL Command Parameters but encounter this error message: Incorrect syntax near the Keyword 'Where' This coding that generate the above error message    strSqlInsert &= "  Where  (SalesID <> @SalesId ) " ------------------------------------------------------------------ Here is the over all Coding that I need your help. Private Sub FSaveNewData() Dim strSqlInsert As String = Nothing Try sqlconn = New SqlConnection(connstr) sqlconn.Open() strSqlInsert &= " Insert into TblSales ( SalesID, CustomerName, SaleQty, SalePrice ) " strSqlInsert &= " Values ( @SalesID, @CustName, @SalesQty, @SalesPrice ) " strSqlInsert &= " Where (SalesID <> @SalesId ) " sqlcmd = New SqlCommand(strSqlInsert, sqlconn) With sqlcmd.Parameters .AddWithValue("@SalesId", SqlDbType.Int).Value = Convert.ToInt32(intSalesId) .AddWithValue("@CustName", SqlDbType.NVarChar).Value = CType(Me.txtCustomerName.Text, String) .AddWithValue("@SalesQty", SqlDbType.Int).Value = Convert.ToInt32(Ctype(me.txtSalesQty.text, integer) .AddWithValue("@SalePrice", SqlDbType.Money).Value = Convert.ToDouble(CType(Me.txtSalesPric

SQL Backups

I have been thrown into doing some SQL work which i am not a pro at. I have a script setup to backup the database to a file called Backup.bak on a network location. Everything works fine BUT it keeps adding the backups to the Backup.bak file so it keeps growing larger and larger. I only want to keep 7 ays worth of backups for this particular script. My question is how can i use the script to keep only 7 days worht of backups and keep the backup.bak file from growing and growing. Right now i have to go in and delete the file every 7 days for it to start over, if i dont the backup file gets really really big.

Expire backups to prevent them from being restored

I have a client with a requirement that states data can only be retained for a certain amount of time.  After the time expires, the data is deleted from the database and cannot be retained or available.  While the data is no longer in the database, it would still reside in a backup taken prior to the expiration.  Is there any way to prevent a backup from being used to restore a database after a point in time.  Using the Expiration Date or Days Retention, only specify the backup can be overwritten.  Is there any way at all to invalidate the backup after a period of time so that it can no longer be restored?

making backups expire after 3 days automatically



I have a maintenance plan that backups a bunch of databases every morning. I need to keep these backups for three days and then they are to be deleted to prevent the disk filling up. Ive ticked the box saying 'Backup set will expire after 3 days', but after three days they are still there..

Anyone know how to remove them automatically?

SQLCMD results in a hexadecimal value for a SELECT FORXML AUTO

I have a stored procedure, that uses FORXML AUTO to reutrn an XML document.
When I call it from SQLCMD, it results in a hexadecimal value (0x....) instead of an XML text.
When I used to call it from OSQL, it resulted in a correct XML text.
When I call it from SQL Mgmt Studi or Query Analyser, the reult is also correct, an XML text.

The way I call SQLCMD is:
sqlcmd -E -S"SQLCLU01\INST01" -d"SFA" -Q"exec ReportGetDataAsXML 6760910"

The result is:

Any suggestions?

How to find the sequence of backups from msdb.dbo.backupset


I am a bit confused  with the first_lsn, last_lsn, differential_base_lsn in the msdb.dbo.backupset.I have  a sql server 2005 database in full recovery mode. My backup jobs are – nightly full backup, dfifferential backup every six hrs and log backups every hr. In addition to that I do do adhoc backups with copy_only.  Usually full bcakup takes 15 minutes, differential backup 5 minutes and  log backup under one minute.

Because they are scheduled to run on top of the hour, it is quite possible all three jobs are triggered at the same time. ( Full backups do not block log backups, Not sure if same is tru with diff backups and log backup). In such a situation I want to see a complete sequence of backups what should be the ordering criteria?

 I am using below query to get the backup sequence. Should I order based on backup_start_date, F.family_sequence

exec sqlcmd and bat files in a single bat fiel . How can I do it?


Hi all

I cannot seem to work out how to put it together I get all sorts of errors

 I would like to create a single bat file that execute 3 bat files and runs 10 sql scripts.

Any examples? help thanks a lot

Thanks for your help
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend