none
trouble making .bak file RRS feed

  • Question

  • Hi All,

    I am trying to make backups of the two databases used by my Visual Studio 2017 application that reside in the App_Data folder of the application, so that I can upload them to my web host MSSQL Server via Plesk. The host tech support prefers that I import a database dump (ZIP archive), but from .bak should also work. My databases are very small (4Mb). In Visual Studio 2017 Server Explorer I can successfully make a .bak file of the database that contains product data, etc., using this T-SQL:

    BACKUP DATABASE [C:\Users\Rob\Documents\Visual Studio 2013\Projects\LabEle3\LabEle3\App_Data\LabEle3.mdf] TO  DISK = N'C:\Users\Rob\Dropbox\labElephant\Website\DB_backups\LabEle3_DB_Backup_20190211.bak' WITH NOFORMAT, INIT,  NAME = N'LabEle3-Full Database Backup', SKIP

    GO

    However, when I try to make a .bak file of the default database used by “ASP.NET identity” that contains user login information, I get this error:

    Msg 911, Level 16, State 11, Line 1

    Database 'C:\Users\Rob\Documents\Visual Studio 2013\Projects\LabEle3\LabEle3\App_Data\aspnet-LabEle3-20150324023533.mdf' does not exist. Make sure that the name is entered correctly.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP DATABASE is terminating abnormally.

    The T-SQL was identical to the one that worked, except that the file name LabEle3.mdf was replaced by the filename of the default database aspnet-LabEle3-20150324023533.mdf:

    BACKUP DATABASE [C:\Users\Rob\Documents\Visual Studio 2013\Projects\LabEle3\LabEle3\App_Data\aspnet-LabEle3-20150324023533.mdf] TO  DISK = N'C:\Users\Rob\Dropbox\labElephant\Website\DB_backups\aspnet-LabEle3-20150324023533.mdf_DB_Backup_20190211.bak' WITH NOFORMAT, INIT,  NAME = N'aspnet-LabEle3-20150324023533-Full Database Backup', SKIP

    I don’t understand why this did not work. Please advise!

    Monday, February 11, 2019 5:07 PM

Answers

  • Hi  RobH18,

     

    >>Msg 102, Level 15, State 1, Line 1

         Incorrect syntax near '-'.

         Msg 319, Level 15, State 1, Line 3

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

     

    Would you please try the following command:

     

    BACKUP DATABASE [aspnet-LabEle3-20150324023533]  
    TO  DISK N'C:\Users\Rob\Dropbox\labElephant\Website\DB_backups\aspnet-LabEle3-20150324023533.bak'
    WITH NOFORMAT, INIT,  NAME = N'aspnet-LabEle3-20150324023533-Full Database Backup', SKIP

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by RobH18 Tuesday, February 12, 2019 1:58 PM
    Tuesday, February 12, 2019 6:26 AM

All replies

  • Where are you running this backup command against? SQL Server Management Studio? 

    Your backup command states the MDF file path after the BACKUP DATABASE T-SQL, use the name of the database that needs to be backed up instead of the MDF path. 

    Something like below:

    --Assuming "aspnet-LabEle3-20150324023533" is the database name
    
    BACKUP DATABASE aspnet-LabEle3-20150324023533 
    TO  DISK = N'C:\Users\Rob\Dropbox\labElephant\Website\DB_backups\aspnet-LabEle3-20150324023533.bak' 
    WITH NOFORMAT, INIT,  NAME = N'aspnet-LabEle3-20150324023533-Full Database Backup', SKIP


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, February 11, 2019 5:24 PM
  • Database backup/restores are run from the SERVER context.  You cannot backup/restore from your local drive to a server.  You need to copy the file to a location the server has access.

    Monday, February 11, 2019 5:41 PM
    Moderator
  • Thanks for the input Mohsin.

    I run the query in Visual Studio 2017, in the Server Explorer, where my databases for my application are listed. I do have SSMS (downloaded yesterday), but have not yet figured out how to get it to see my databases. But it seems like I can make .bak files in Visual Studio 2017, so do I need SSMS?

    When I run your query in Visual Studio 2017 I get two errors:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '-'.
    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Rob

    Monday, February 11, 2019 5:48 PM
  • Thanks Tom. My problem seems to be that, according to the website hosts, I cannot simply copy my database files to the server context of the website, I have to import a database dump (ZIP archive), into a new database in that I first create in Plesk. I just tried to see if that worked with the .bak file that I did succeed in creating, but got an error stating that the file was not recognized as a dump file. So I guess my question comes down to how to make a dump file from my databases (I can see in Windows Explorer and in the Visual Studio 2017 Server Explorer, but not in SSMS). Is that something that could be done in SSMS, if I can get my databases visible in SSMS? When SSMS opens, I can connect to XPS-DESKTOP\SQLEXPRESS, but cannot go to their location.

    Rob

    Monday, February 11, 2019 6:37 PM
  • A "dump file" is a MySQL thing, which includes all the commands to create and load the data into MySQL.

    MS SQL Server does not provide any mechanism to create a "dump file".  You would have to write something to put the data into a format which works with your vendor.

    Monday, February 11, 2019 6:56 PM
    Moderator
  • When I run your query in Visual Studio 2017 I get two errors:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '-'.
    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    This is because an identifier which includes a non-identifier such as a hyphens needs to be quotes either in double quotes or in brackets as you had in your original post.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, February 11, 2019 10:24 PM
  • Thanks Tom. It is helpful to know that I do not need (or can even make) a dump file. As it turns out, I was in fact able to upload my .bak file in Plesk, using "Import Dump" after the new database was added, rather than under using "Import Dump" using Backup Manager, which seemed to require an actual dump file. So this brings me back to how to make the SQL code work for the aspnet-LabEle3-20150324023533.mdf file in the same way that it did for the LabEle3.mdf file, to make a .bak file.

    Rob

    Tuesday, February 12, 2019 1:20 AM
  • Thanks Erland,

    I'm not sure I understand. How, then, is my original code not correct? If you could show me how the code should look, I would greatly appreciate that, and mark it as the answer, if it works!

    Rob

    Tuesday, February 12, 2019 1:24 AM
  • Hi  RobH18,

     

    >>Msg 102, Level 15, State 1, Line 1

         Incorrect syntax near '-'.

         Msg 319, Level 15, State 1, Line 3

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

     

    Would you please try the following command:

     

    BACKUP DATABASE [aspnet-LabEle3-20150324023533]  
    TO  DISK N'C:\Users\Rob\Dropbox\labElephant\Website\DB_backups\aspnet-LabEle3-20150324023533.bak'
    WITH NOFORMAT, INIT,  NAME = N'aspnet-LabEle3-20150324023533-Full Database Backup', SKIP

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by RobH18 Tuesday, February 12, 2019 1:58 PM
    Tuesday, February 12, 2019 6:26 AM
  • You don't specify a file name for the database in the backup command, you specify the database name. It is really really strange to name the database exactly the same as the mdf file for the database. Anyhow, if that is what you really did, then you, as Erland already pointed out, need to enclose the database name in square brackes (as you did in the working backup command) or double quotes.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, February 12, 2019 7:57 AM
    Moderator
  • I'm not sure I understand. How, then, is my original code not correct? If you could show me how the code should look, I would greatly appreciate that, and mark it as the answer, if it works!

    In your original code in the top of the thread, you had specified the name of the data file where you should have the data file.

    In the post I replied to, you had made the mistake of using Moshin's code without thinking and made any attempts to understand what was wrong before you posted. I pointed out what correction you needed to make to his post.

    Yes, I don't really like spoonfeed people answers. I sort of have the idea that I want to help people to understand what they are actually doing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, February 12, 2019 8:19 AM
  • Thanks Dedmon! this worked, after putting all the code on one line, which eliminated the second error message. Thanks to everyone for their input. Apologies for my ignorance.

    Rob

    Tuesday, February 12, 2019 2:07 PM