SQL Server Performance

How to Run query from batch file and get the output into batch variable

Discussion in 'SQL Server 2005 General DBA Questions' started by shabnyc, Jan 31, 2008.

  1. shabnyc Member

    Hi guys, I really need help on this because I couldnt find any info on this issue online. I need to run the following 2 queries from within a batch file and return the output from these queris into variables within the batch file itself so I can use them to run restore process.
    here are the queriesdeclare @SmoDefaultFile nvarchar(512)
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N
    'DefaultData', @SmoDefaultFile OUTPUTdeclare @SmoDefaultLog nvarchar(512)
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer',
    N'DefaultLog', @SmoDefaultLog OUTPUT
    so within the batch file I would write this
    set batchvariable = @SmoDefaultFile
    rem run restore db
    OSQL -Q"restore DB from .... with move 'datafile' to '%batchvariable%datafileName.mdf, move ......
    Any help is so appreciated,
    Thanks
  2. satya Moderator

    Can you please explain what you are trying to achieve with this script?
  3. Adriaan New Member

    You already have a variable, @SmoDefaultFile.
    You can only do the OSQL call through xp_cmdshell - just prepare the whole OSQL call as a string, concatenating @SmoDefaultFile into it.
    Also remember to double-up the single quotes around datafile and the new file name inside the statement.
  4. shabnyc Member

    <P mce_keep="true"><IMG style="BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-RIGHT-WIDTH: 0px" alt="" src="http://sql-server-performance.com/Community/Themes/default/images/user_IsOffline.gif">thanks <A href="http://sql-server-performance.com/Community/members/satya.aspx">satya</A>&nbsp;, I am trying to use a batch file to run from command prompt that will restore DBs automatically based on backup_file&nbsp;stored in the server, so in command prompt I will type </P><P mce_keep="true">C:atfile_Restore g:DB_bakupfile.bak Desired_DBName. </P><P mce_keep="true">what I need is query the default location of data and log files ex(f:Data , H:Log) and use them to restore the new DB to the default location. I hope this makes it more clear. my problem is that I could not use the variable mentioned in the above query (@SmoDefaultFile) to use it within the restore command.</P><P mce_keep="true">thanks Adrian, here is the batch file to refer to&nbsp;with what I described in this reply</P><P mce_keep="true">Thanks all&nbsp;Satya,&nbsp;Adrian for your help&nbsp;&nbsp;</P><P mce_keep="true">@echo off</P><P mce_keep="true">REM </P><P mce_keep="true">:default<BR>REM Name of the instance or default local<BR>SET SERVER=(local)</P><P mce_keep="true">SET RESTORE_DIR=C: emp</P><P mce_keep="true">REM <BR>setlocal ENABLEEXTENSIONS</P><P mce_keep="true">SET FILENAME=%RESTORE_DIR%%1</P><P mce_keep="true"><BR>echo Restoring Database - %2 ....<BR>REM 'Restoring Database'</P><P mce_keep="true"><BR>&nbsp;osql -E -d master -S %SERVER% -h-1 -Q "SET NOCOUNT ON; SET QUOTED_IDENTIFIER ON ;&nbsp; create table FileDirs(Files nvarchar(9)); Declare @SmoDefaultFile nvarchar(8); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT; Insert Into FileDirs SELECT @SmoDefaultFile" -w 200 &gt;&gt; restore.log</P><P mce_keep="true"><BR>&nbsp;osql -E -d master -S %SERVER% -h-1 -Q "SET NOCOUNT ON; SET QUOTED_IDENTIFIER ON ; Declare @SmoDefaultFile nvarchar(8); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultLog', @SmoDefaultFile OUTPUT; Insert Into FileDirs SELECT @SmoDefaultFile" -w 200 &gt;&gt; restore.log</P><P mce_keep="true"><BR>&nbsp;SET SQL_QUERY= Declare @dataDIr nvarchar(9), @LogDir nvarchar(9); set @dataDir = (select Files from FileDirs where Files like ''Data%''; set @LogDir = (select Files from FileDirs where Files like ''Logs%''; Restore Database %2 From Disk = %1 With move 'dbname_Data' to @DataDir+%2_Data.mdf', move 'dbname_Log' to @LogDir+%2_Log.Ldf'</P><P mce_keep="true">osql -E -d master -S %SERVER% -Q "%SQL_QUERY%" -w 140 &gt;&gt; Restore.log</P><P mce_keep="true">Rem do some permissions codes</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">Rem if not exist %FILENAME% goto error</P><P mce_keep="true">REM SET SQL_QUERY=SET NOCOUNT ON ; SET QUOTED_IDENTIFIER ON ;select 'Restore Database: ' + '%2' <BR>echo Restore done.<BR>goto end</P><P mce_keep="true"><BR>:error<BR>echo _______________________________________________________________________<BR>echo some error messages<BR>echo _______________________________________________________________________<BR>goto end</P><P mce_keep="true"><BR>:end<BR>SET %PATH%=%OLDPATH%</P><P mce_keep="true">&nbsp;</P>
  5. Adriaan New Member

    Huh? You don't need to know the default locations. There should be an option to "Force restore over existing database", which takes care of that already. Or am I missing something?
  6. shabnyc Member

    Adrian, the problem is that we have to delete the Databases before we restore it again, so option to "Force restore over existing database" is not applicable
  7. Adriaan New Member

    Restoring a full backup over an existing database overwrites the entire database. Any particular reason why you would need to delete the files first?
  8. shabnyc Member

    thanks adrian, the reason is the backup files come from different servers and must change location of data and log files.
  9. Adriaan New Member

    Yes, that's why there is a "restore over existing database" option to begin with - so you don't have to worry about the file locations.
    Within the RESTORE DATABASE syntax, the keyword for this is REPLACE.
  10. shabnyc Member

    Adriaan, management dont want restore over existing DBs for some reasons.
  11. Adriaan New Member

    Instead of the series of partial scripts, why not create a single T-SQL script that starts off by collecting the file names per dbname, storing it in a temp table, then drops the various databases, then loops through the temp table and restores the databases accordingly.
    What arguments do management give for that requirement?
  12. shabnyc Member

    it is just needed to delete database, restore using .bak from another server, using batch file with 2 parameters
    .
  13. shabnyc Member

    also user will provide a new DB name as parameter,
  14. shabnyc Member

    Any hope On this, can someone helps

Share This Page