Emergency Help Needed

  • I got an urgent requirement as below:

    We need to search all .bkp and .bak files throughout at least 100 SQL Boxes in all drives. I need to automate that ASAP :crazy:

    I have an basic idea as below:

    To craete a job (where I will link other servers)which will inturn call search function of windows for all .bak and .bkp in all available drives and return all data in a table

    However I am not sure how to call the serach function of windows from SQL Server/batch file (Even a batch file also work I guess!!)

    If any one has script/any resolution.. could you please share with me?

    Many thanks in advance

  • /*

    you actually have quite a few options. I would probably do something like this: (as it seems that you do not have linked servers) note: you will need to run this from a cmd session that

    has access to all of the servers.

    test the script out below, then save it to your disk. then get a list of the servers you wish to run this on and put them in a text file (osql -L might help)

    then modify the dos cmd below and execute it. once it has completed just run the copy file cmd and then you can open it with excel or notepad or whatever.

    if you have any issues let me know and I will see if I can help.

    Dos cmd:

    for /F %i in (C:\FileWithServerNames.txt) do osql -E -S%i -iC:\SavedTsqlScriptbelow.sql -oC:\%i.CSV -n -h-1 -w800 -s","

    (note the integrated security, you might want to change this to a SQL security account(need sysadmin to execute xp_cmdshell))

    copy cmd:

    copy *.CSV C:\serverbackups.csv


    you can change your command prompt user by running:

    runas /noprofile /user:DOMAIN\UserNameWithSQLAccess cmd




    -- this feature may be disabled on your machines, hence the script that enables it here. you might want to disable it once you have finished again.

    sp_configure 'show advanced options', 1




    sp_configure 'xp_cmdshell', 1



    DECLARE @cName CHAR(1)

    CREATE TABLE ##tbl_contents





    CREATE TABLE #tbl_drives





    INSERT INTO #tbl_drives

    EXEC master.dbo.xp_fixeddrives

    DECLARE file_cursor CURSOR FOR

    SELECT Drive FROM #tbl_drives

    ORDER BY Drive

    OPEN file_cursor

    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

    FETCH NEXT FROM file_cursor

    INTO @cName

    --.bkp and .bak




    INSERT INTO ##tbl_contents (Files)

    EXEC master.dbo.xp_cmdshell ''DIR '+@cName+':\*.bak /b /s''

    INSERT INTO ##tbl_contents (Files)

    EXEC master.dbo.xp_cmdshell ''DIR '+@cName+':\*.bkp /b /s''


    FETCH NEXT FROM file_cursor

    INTO @cName


    CLOSE file_cursor

    DEALLOCATE file_cursor

    DELETE FROM ##tbl_contents


    SELECT * FROM ##tbl_contents

    DROP TABLE #tbl_drives

    DROP TABLE ##tbl_contents


  • I somewhat like the solution above, but honestly this is something you'll run periodically to check on space, files, sizes, etc.

    I'd schedule this to run on each server, make it a part of your standard install, store the data in a flat file or on a small database on the server, and then pull it all together with scripts to a central server. Linked servers aren't really needed, but as servers come and go, scripts like this can have issues and then you have to debug it each time. If each server handles it's own data collection, you can always bring that stuff together in a central environment and you don't "lose" data because server 5 of 10 stops working and it takes you a day to find time to debug it.

  • Milllions of thanks to you both. It really helped me a lot..

    Have a nice evening 😀

  • If you are search for backup files then also give .dmp in your search criteria..

  • Thanks.. But is not .dmp will have dump infos from Server? Could you please tell me the reason why should I add .dmp in the monitoring systems?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply