none
Local db Backup problem RRS feed

  • Question

  • Hi 

    i have a project and try to backup my database by winform...there are too many samples but none of them is about LOCAL DB..

    i created a procedure in my database for backup..all procedures are working but backup procedure is not...it says THERE İS NO DATABASE...

    USE [mydb]
    GO
    /****** Object:  StoredProcedure [dbo].[db]    Script Date: 13.12.2019 16:46:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[db]

    @name VARCHAR(MAX) = 'mydb' -- DB NAME TO CREATE BACKUP
    AS
    BEGIN

    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'D:\yedek Db\'

    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    BEGIN
    ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    ALTER DATABASE ForVet SET MULTI_USER WITH ROLLBACK IMMEDIATE
    END
    END

    --When i switched it  to Single Mod  it gives PERMİTİON ERROR..İf i dont switch it says DATABASE  doesnt exist.. 



    Saturday, December 14, 2019 9:23 AM

All replies

  • Try using EXEC for ALTER:

    BEGIN
    
    DECLARE @name VARCHAR(MAX) = 'TestDatabase' -- DB NAME TO CREATE BACKUP
    
    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name
    
    SET @path = 'C:\SQLBackups\'
    
    
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    
    BEGIN
    EXEC('ALTER DATABASE ' + @name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    EXEC('ALTER DATABASE ' + @name + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE')
    END
    END



    • Edited by SethWH Saturday, December 14, 2019 11:14 AM Typo
    Saturday, December 14, 2019 11:10 AM
  • The stored procedure in your question alters database ForVet but then backs up database MyDb. I don't think that is your intent. You'll get an error if the ForVet database does not exist.

    Backup can be done online so there is no need to switch to SINGLE_USER for general backup purposes.

    Below is and example that will backup the database containing the proc by default, or a different database when a name is specified. This is not specific to localdb.

    ALTER PROCEDURE [dbo].[db]
    	@name sysname = NULL
    AS
    IF @name IS NULL SET @name = DB_NAME(); --backup database containing this proc by default
    
    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name
    
    SET @path = 'D:\yedek Db\'
    
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    
    BACKUP DATABASE @name TO DISK = @fileName
    GO
    
    --backup current database
    EXEC dbo.db;
    --backup a different database
    EXEC dbo.db @name = N'SomeOtherDatabase';
    GO

     

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, December 14, 2019 11:11 AM
  • Hi,

    This code (without the Single Mod part) should work well in SQL Server database but I did not tested it on LocalDB

    check if the following example solve your needs:

    https://stackoverflow.com/questions/35788119/how-to-backup-a-sql-server-2014-express-localdb-mdf-file-programmatically


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, December 14, 2019 11:20 AM
    Moderator
  • This code (without the Single Mod part) should work well in SQL Server database but I did not tested it on LocalDB

    I tested with SQL Server Express localdb to verify it works.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, December 14, 2019 11:22 AM
  • sorry , when i was writing here in this page  i forgot to change my database name as "mydb" in that line , instead of  "ForVet" 

    in my actual code its "ForVet" i just wanted to change it as "mydb " just to be clear..

    and now i ll correct  it :)

    Monday, December 16, 2019 12:43 AM
  • which code ? Mr Dan Guzman s code works or the one in the link...i hope it s Guzman 's :)  cause i tried the one in link but  there were errors in syntax  i corrected all of them excep   this one ... eb.GetConnectionString() };... what is eb :)
    Monday, December 16, 2019 12:54 AM
  • which code ? Mr Dan Guzman s code works or the one in the link...i hope it s Guzman 's :)  cause i tried the one in link but  there were errors in syntax  i corrected all of them excep   this one ... eb.GetConnectionString() };... what is eb :)

    I meant that the original code that Challenger47 posted is working well on SQL Server if you remove the "ALTER DATABASE" which is not needed.

    The code in the link I did not tested or read in deep, but only gave it a glance.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, December 16, 2019 1:30 AM
    Moderator
  • No sir it didnt work it s my code already :( ii ll try Mr Dan s code today..
    Monday, December 16, 2019 8:35 AM
  • if my connection string is connected to .\sql server........ it works ..but if it s local db it says there is no database :(

    IF @name IS NULL SET @name = DB_NAME();....in here if i write 
    IF @name IS NULL SET @name = mydb();...it gives error , doesnt accept "();"

     
    Monday, December 16, 2019 8:57 AM
  • Hi Challenger47,

    Please check if your proc is in a different database, and the permissions, database context, server..

    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    Alter PROCEDURE DBbackup
    
    @name VARCHAR(MAX) = '' -- DB NAME TO CREATE BACKUP
    AS
    BEGIN
    
    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name
    
    SET @path = 'D:\Backup\'
    
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    
    BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    END
    END
    GO
    
    --Likewise just pass names of db’s to stored procedure to create backup.
    EXEC [DBbackup] 'Database1'
    EXEC [DBbackup] 'Database2'
    
    

    Best Regards,

    Lily


    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

    Monday, December 16, 2019 10:35 AM
  • i tried it didnt work :(... what am i doing wrong :( could you please connect by teamviewer :( 
    Monday, December 16, 2019 11:58 AM
  • Hi Lily...this is my steps ..

    1) i created the procedure in managament studio..named db

    2)  detached it from management studio and added in to my project..

    3)  arranged the permitions as everybody and full control in properties (security option) 

    4) rebuilding my project and starting in visual std 2013

    5)  arranged a button on my main form for backup end in to event i wrote my sqlcommand as exec db

    it says there is no database ..i even wrote the whole address but still cant find it ...

    i tried to cpy files ..i stop the sql services by button ..but they dont stop..i stoped by manuel way but still says file is being used although i stoped it.which service is LOCAL DB using ? isnt it sql server service ?i tried everything i dont know what i miss 

    Monday, December 16, 2019 12:55 PM
  • just imagine a clients computer..when they are using the prgram they ll press the backup button and it ll backup database.. there ll not be sql server or management studio..it s LOCAL DB there ll be only one database in it..
    Monday, December 16, 2019 1:40 PM
  • Hi,

    I tested your code myself in practical and it works well on SQL Server 2019 (not localdb) and Dan Guzman confirmed that it is working on localdb as well

    The only changes that I did was:

    1. After removing the part that alter the database

    2. Instead of using "ALTER" I used "CREATE OR ALTER" which is not relevant

    3. Obviously I changed the path where the backup file will be stored

    here is your code which works well for me on SQL Server:

    USE [UTF8DB]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE OR ALTER PROCEDURE [dbo].[db]
    
    @name VARCHAR(MAX) = 'UTF8DB' -- DB NAME TO CREATE BACKUP
    AS
    BEGIN
    
    DECLARE @path VARCHAR(256) -- path of backup files
    DECLARE @fileName VARCHAR(256) -- filename for backup
    DECLARE @fileDate VARCHAR(20) -- used for file name
    
    SET @path = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\'
    
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    
    BEGIN
    --ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    --ALTER DATABASE ForVet SET MULTI_USER WITH ROLLBACK IMMEDIATE
    END
    END


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, December 16, 2019 5:01 PM
    Moderator
  • So, now we can think what might be the issue in your case :-)

    1. Please test your code using Azure Data Studio instead of SSMS

    https://docs.microsoft.com/en-us/sql/azure-data-studio/download?WT.mc_id=DP-MVP-5001699

    You don't need to install this app. You can download the ZIP file and extract in anywhere including in external disk like disk-on-key

    2. Please try to find the error log and provide the exact error information

    3. Please provide screenshot of the error


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, December 16, 2019 5:09 PM
    Moderator
  • you execute the procedure in sql managwment studio ..i execute it in my winform project...in sql it works already but when i called the procedure in csharp project..it doesnt work
    Monday, December 16, 2019 6:20 PM
  • you execute the procedure in sql managwment studio ..i execute it in my winform project...in sql it works already but when i called the procedure in csharp project..it doesnt work

    Hi,

    SSMS is not related to SQL Server. It is external application exactly like any application which you develop. If you can use SSMS and not your application then the issue is not related to SQL Server but to your application or the configuration.

    saying "in sql it works" have no meaning here and I assume you mean that in SSMS it works, since SQL Server does not have human interface. It is simply a set of services which we connect using external tools (like you application or Microsoft SQL Server Management Studio or Microsoft Azure Data Studio, and so on)

    As I said, the query is OK :-)

    Now we need to understand what is no working well in the application.

    1. Can you use SQL Server instead of SQL Server LocalDB temporarily for the sake of solving the issue?

    2. Are other queries execute OK for you? can you create new table, insert data using your webform app?

    If not, is it possible that the issue related to the connection string?
    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/creating-a-connection-string


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Proposed as answer by Lily Lii Tuesday, December 17, 2019 9:48 AM
    Monday, December 16, 2019 8:22 PM
    Moderator
  • i tried to cpy files ..i stop the sql services by button ..but they dont stop..i stoped by manuel way but still says file is being used although i stoped it.which service is LOCAL DB using ? isnt it sql server service ?i tried everything i dont know what i miss 

    What we can conclude from the problem is that there is no problem with backing up a database from localdb. So it sounds like you have a problem in your application code. Of which we have seen absolutely nothing at all.


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

    Monday, December 16, 2019 10:56 PM