locked
Problem with Syntax error RRS feed

  • Question

  • I am trying to set up a scheduled backup for my contact management program.  According to their Knowledge base, I need to set up 2 files.

    The first is called SqlBackup.sql, and they say it should read:

     

    BACKUP DATABASE <database name> TO DISK = <backup name> WITH

    INIT

    Where <database name> is the name of your Maximizer SQL database and

    <backup name> is the filename and location of your backup.

    In my case, <database name> is Data.  For < backup name>  I created a folder on the desktop called Data.bak.  From that I made a file to read:

     

    BACKUP DATABASE Data TO DISK = C:\Data.bak WITH INIT

    When I run it under SQL Server Mgt Studio Express to try it out, I get

     

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'C:'.

    Msg 319, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

     

    The 2nd file, called SqlBackup.bat, should read:

     

     

    Sqlcmd -S <sql server name> -U MASTER -P <sql user password> -i <sql script

    name>

    Where <sql server name> is the name of your SQL Server, <sql user password> is

    the password of the MASTER User, and <sql script name> is the name and path

    of the file you created in step 1.

     

    In my case, <sql server mane> is David-PC\Maximizer, the <sql user password> is blank in sql but there is one in Maximizer (although it doesn't work either with or without the password), and<sql script name> is located under C:\Users\David\Documents\SqlBackup.sql.  So the file reads:

     

    Sqlcmd -S DAVID-PC\MAXIMIZER -U MASTER -P -i C:\Users\David\Documents\SqlBackup.sql

     

    When I try that file, the error message is:

     

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'S'.

     

    I do not unerstand Syntax.  According to the software co, this setup should work.  For some reason its not working for me.  Can you please help me?  Thank you.

    Saturday, September 6, 2008 4:24 PM

Answers

  • At the first query...

    you need to put path in single quote

    Code Snippet

     

    BACKUP DATABASE Data TO DISK = 'C:\Data.bak' WITH INIT

     

     

    Saturday, September 6, 2008 4:34 PM
  • Hi,

    Your backup has been created on the C:\ with file name as Data.bak.  And not on the Desktop folder you created with name Data.bak

     

    For creating a Backup you don't need to have folder called Data.bak.  .bak is the extension of Sql server's database backup file.  It's Not a folder.

    You can create the backup with any name. Also on any folder in System for which user has rights.

     

    To clarify you more on this

    The part of document you have pasted here is talks about scheduling the Backup on regular basis.

    By reading that with my understanding it says....

     

    First create a sqlbackup.sql file.

    Opne notepad copy paste your command

    BACKUP DATABASE Data TO DISK = 'Path of the folder where you want to create backup file\Data.bak' WITH INIT

     

    Here 'Path of the folder where you want to create backup file can be nay thing

    e.g. C: \Backup Folder  or any folder path

     

     save the file as sqlbakcup.sql.

    Now script in this file will create a backup.

    But now you want to schedule it as per your requirements.

     

    Second part Maximizer application server talks about creating .bat file which can be used to schedule your backup script.

     

    Hopefully I cleared some points.

    If you are still in doubt you are always welcome.

     

    Regards

     

     

    Monday, September 8, 2008 5:27 AM
  • I think I'm getting closer!!

    I found the Data.bak file listed under C:\, with the proper size.  From that, it is showing me the Sqlbackup.bat file is working. 

    If I create a folder on C:\ called Data backups, and make the scripts read:

     

    BACKUP DATABASE Data TO DISK = 'C:\Data backups\Data.bak' WITH INIT

    it is still sending the backup to C:\, not to the C:\Data backups folder.  Am I still missing something?  Thanks for your patience.

    Monday, September 8, 2008 2:48 PM
  •  

    Ok,  I think you doing everything right just litle confused with what is happening.

    Now I'll try make to clear t more.

    Create a sqlbackup.sql file with CREATE DATABASE BACKUP command in which path of the data.bak will be, C:\Database Backup\Data.bak

     

    Now you need to create a .bat file which will execute the sqlbackup.sql file

    And then finally you will schedule the .bat file.

     

    Just make sure you follow the steps.

     

    To make everything is happening correctly after running the .bat file see the Modified date of the data.bak file.

     

     

    Regards

    Monday, September 8, 2008 5:45 PM

All replies

  • At the first query...

    you need to put path in single quote

    Code Snippet

     

    BACKUP DATABASE Data TO DISK = 'C:\Data.bak' WITH INIT

     

     

    Saturday, September 6, 2008 4:34 PM
  • Other thing your second query you are supposed to run it in Command prompt

     

    See this

    Running Transact-SQL Script Files by Using sqlcmd

     

     

    Regards

    Saturday, September 6, 2008 4:48 PM
  • The first query seemed to run OK with the quotes included.

    The second query I tried running in a command prompt, and I get the following response:

     

    C:\Users\David>Sqlcmd -S DAVID-PC\MAXIMIZER -U MASTER -P CONTROL -i C:\SqlBackup
    .sql
    Processed 4432 pages for database 'Data', file 'Data' on file 1.
    Processed 1 pages for database 'Data', file 'Data_log' on file 1.
    BACKUP DATABASE successfully processed 4433 pages in 6.717 seconds (5.406 MB/sec
    ).

    After getting that response, I checked the Data.bak file, and it is still empty.  It appears that something is happening, I just know what or where it is ending up.  Thank you.
    Sunday, September 7, 2008 12:24 AM
  • Hi,

    What do you mean by data.bak file is empty?

    Other thing is You have already created the backup with 1st statement.

     

    And the 2nd query ...

    It executes the sql statement stored in the file sqlbackup.sql.

    There -i stands for input file.

    What is the query you have stored in sqlbakcup.sql?

     

    One more question if you are already created the backup using the 1 statement why you are trying to re-do with sqlcmd?

     

    Like to add to that, tell us what you are trying to do?

    May be we can guide you to achive that in better way.

    Sunday, September 7, 2008 7:45 AM
  • I was originally told that the backup needed a folder to backup into, ie Data.bak, which is on the desktop.  After trying to run the batch file, the data.bak file is still empty, with no backup file in it.  When I first purchased the program, and asked about this situation, and the tech had also added a file, data.bak, although he also had problems setting it up. 

     

    You say I have already created the backup in the first statement.  Where would it be?

    Ultimately, I am simply trying to set up a scheduled backup for my database, that I had named Data.

    Below are the instructions that Maximizer gives to set up the scheduled backup, based on their Knowledge Base:

     

     

    Setting Up an Automated Backup - Maximizer CRM 10 Group Edition

    SQL Express 2005 is used by Maximizer CRM 10 Group Edition as a database engine.

    The SQL Agent, which is used to facilitate the automated process of database backups, is

    not included with SQL Express 2005. However, it is still possible to facilitate an

    automated backup by running a SQL script in Windows Task Scheduler as follows:

    1) On the Maximizer application server, use a text editor to create a file called

    SqlBackup.sql, adding the following line:

    BACKUP DATABASE <database name> TO DISK = <backup name> WITH

    INIT

    Where <database name> is the name of your Maximizer SQL database and

    <backup name> is the filename and location of your backup.

    For example, BACKUP DATABASE EsconaTutorial TO DISK =

    “D:\Escona.bak” WITH INIT.

    2) On the Maximizer application server, use a text editor to create a file called

    SqlBackup.bat, adding the following line:

    Sqlcmd -S <sql server name> -U MASTER -P <sql user password> -i <sql script

    name>

    Where <sql server name> is the name of your SQL Server, <sql user password> is

    the password of the MASTER User, and <sql script name> is the name and path

    of the file you created in step 1.

    For example, Sqlcmd -S MYSQLSERVER\MAXIMIZER -U MASTER -P

    CONTROL -i C:\SqlBackup.sql.

    3) On the Maximizer application server, open the Control Panel and perform the

    following steps:

    i) Double-click Scheduled Tasks.

    ii) Double-click Add Scheduled Task.

    iii) Click the Next button.

    iv) Click the Browse button.

    v) Browse to the SqlBackup.bat file you created, select the file, and click

    Open.

    vi) Choose how often you would like the backup to occur. For example,

    choose Daily and click Next.

    vii) Select the time you want the Backup to start. For example, select

    11:00 PM and click Next.

    viii) In the Enter the user name field, enter <Machine

    Name>\Administrator, specify the Administrator’s password, and click

    Next.

    ix) Click Finish to schedule the backup and close the Scheduled Task

    Wizard.

    Using the above example, a backup of your Maximizer database will occur at 11:00 PM

    on a daily basis.

    This is a simple example and the process will accomplish an automated backup. If you

    require additional functionality, see the Transact SQL documentation for details on

    adding functionality to the SQL script.

    On the first query, apparently, the " was in fact supposed to only be '.

    Hopefully, this will give you the info you needed.

    Thanks

    Sunday, September 7, 2008 7:53 PM
  • Hi,

    Your backup has been created on the C:\ with file name as Data.bak.  And not on the Desktop folder you created with name Data.bak

     

    For creating a Backup you don't need to have folder called Data.bak.  .bak is the extension of Sql server's database backup file.  It's Not a folder.

    You can create the backup with any name. Also on any folder in System for which user has rights.

     

    To clarify you more on this

    The part of document you have pasted here is talks about scheduling the Backup on regular basis.

    By reading that with my understanding it says....

     

    First create a sqlbackup.sql file.

    Opne notepad copy paste your command

    BACKUP DATABASE Data TO DISK = 'Path of the folder where you want to create backup file\Data.bak' WITH INIT

     

    Here 'Path of the folder where you want to create backup file can be nay thing

    e.g. C: \Backup Folder  or any folder path

     

     save the file as sqlbakcup.sql.

    Now script in this file will create a backup.

    But now you want to schedule it as per your requirements.

     

    Second part Maximizer application server talks about creating .bat file which can be used to schedule your backup script.

     

    Hopefully I cleared some points.

    If you are still in doubt you are always welcome.

     

    Regards

     

     

    Monday, September 8, 2008 5:27 AM
  • I think I'm getting closer!!

    I found the Data.bak file listed under C:\, with the proper size.  From that, it is showing me the Sqlbackup.bat file is working. 

    If I create a folder on C:\ called Data backups, and make the scripts read:

     

    BACKUP DATABASE Data TO DISK = 'C:\Data backups\Data.bak' WITH INIT

    it is still sending the backup to C:\, not to the C:\Data backups folder.  Am I still missing something?  Thanks for your patience.

    Monday, September 8, 2008 2:48 PM
  •  

    Ok, this new script you put into sqlbakup.sql file?

    And you run the .bat file right?

    You got any error?  What output you received?

    First create a folder call Data backups on C drive.

    Monday, September 8, 2008 2:58 PM
  • If I query the Sqlbackup.sql file, my backup ends up where it is supposed to be, in the Data backups folder.

    If I run the Sqlbackup.bat file, my backup ends up under C:\ instead.

    I don't show any errors.  The messages show the files have processed, and I am able to find them, with the proper backup size.  Just not in the same places, being the Data backup folder, where I want them.

     

    Monday, September 8, 2008 5:32 PM
  •  

    Ok,  I think you doing everything right just litle confused with what is happening.

    Now I'll try make to clear t more.

    Create a sqlbackup.sql file with CREATE DATABASE BACKUP command in which path of the data.bak will be, C:\Database Backup\Data.bak

     

    Now you need to create a .bat file which will execute the sqlbackup.sql file

    And then finally you will schedule the .bat file.

     

    Just make sure you follow the steps.

     

    To make everything is happening correctly after running the .bat file see the Modified date of the data.bak file.

     

     

    Regards

    Monday, September 8, 2008 5:45 PM
  •  

    I finally figured out my problem

     

    The Sqlbackup.sql file was running fine.  It backs up right to the Data backup file I created.

    The problem was the .bat file.  It turns out, I had 2 instances of Sqlbackup.bat on my pc.  One under C:\, and the other under C:\Users\David\Documents

    I have been making all the changes to the second file, not even realizing the first one was there.  The problem was, the scheduler kept pointing to the first file.  That file was setting up the Data.bak file in C:\, just as you said.  The second file works like a charm.

    I have rechecked all the settings, and everything is finally on track.  Thank you for your help.

    Monday, September 8, 2008 7:11 PM