none
SQLSERVER Backup RRS feed

  • Question

  • Sir why these codes do not work

    declare @db1 varchar(30)='at20';
    declare @flname varchar(30)='e:\hourly\at20\x';
    declare @path1 varchar(50);
    select CONCAT(@flname,'\',@db1,'.bak')as path1
    select @path1
    
    
    backup database @db1 to disk= '@path1'

    No error occurs but also no backup.

    Please help


    tqmd

    Sunday, September 1, 2019 11:43 AM

All replies

  • Try

    declare @db1 varchar(30)='at20';
    declare @flname varchar(30)='e:\hourly\at20\x';
    declare @path1 varchar(50);
    select @path1=CONCAT(@flname,'\',@db1,'.bak')
    select @path1
    
    
    backup database @db1 to disk= @path1


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by AV111 Sunday, September 1, 2019 2:26 PM
    Sunday, September 1, 2019 12:02 PM
    Moderator
  • Hi Tariq Mehmood,

    As Uri Dimant said, the mentioned code works fine and taking backup fine.(Rewrites the previous backup file for every backup run)

    DECLARE @db1 VARCHAR(30)='at20';
    DECLARE @flname VARCHAR(30)='e:\hourly\at20\x';
    DECLARE @path1 VARCHAR(50);
    SELECT @path1=CONCAT(@flname,'\',@db1,'.bak');
    SELECT @path1;
    
    BACKUP DATABASE @db1 TO DISK= @path1;
    
    --(No column name)
    --e:\hourly\at20\x\at20.bak
    
    --(1 row affected)
    --Processed 9568 pages for database 'at20', file 'at20' on file 2.
    --Processed 2 pages for database 'at20', file 'at20_log' on file 2.
    --BACKUP DATABASE successfully processed xxxx pages in x.xxx seconds (xx.xxx MB/sec).

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi


    • Edited by Arulmouzhi Sunday, September 1, 2019 5:12 PM Edited for more clarification
    Sunday, September 1, 2019 5:10 PM
  • Because the @path is NULL, you never assigned the value for the backup path to this variable. Hence the error. See the other replies for how to do it.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, September 2, 2019 10:32 AM
  • Thanks everyone for helping, problem solved, now there is an other issue

    this line works fine 

    SELECT TABLE_NAME FROM at20.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'

    but it this line shows nothing

    SELECT TABLE_NAME FROM concat('at20','.',INFORMATION_SCHEMA.Tables) WHERE TABLE_TYPE = 'BASE TABLE'

     I am just trying to use concat.

    Please


    tqmd

    Monday, September 2, 2019 11:30 AM
  • Hi Tariq Mehmood,

    Hope the below examples will helps you!

    --YOUR METHOD 1 (Simple SELECT Query)
    SELECT TABLE_NAME FROM TESTDB.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'; --18 ROWS
    
    --YOUR METHOD 2 (Dynamic Query)
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL= N' SELECT TABLE_NAME FROM ' + CONCAT('TESTDB','.','INFORMATION_SCHEMA.Tables') + ' WHERE TABLE_TYPE = ''BASE TABLE'' ';
    EXEC(@SQL);
    

    Note: Suggested one of the many ways that is easy to understand and implement.
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, September 2, 2019 11:55 AM