July 28, 2008 at 12:14 pm
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
July 28, 2008 at 1:02 pm
/*
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
NOTE:
you can change your command prompt user by running:
runas /noprofile /user:DOMAIN\UserNameWithSQLAccess cmd
t-sql:
*/
SET NOCOUNT ON
-- 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
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
DECLARE @cName CHAR(1)
CREATE TABLE ##tbl_contents
(
ServerNameVARCHAR(20) DEFAULT (@@SERVERNAME)
,FilesVARCHAR(200)
)
CREATE TABLE #tbl_drives
(
DriveCHAR(1)
,FreeINT
)
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
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('
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
END
CLOSE file_cursor
DEALLOCATE file_cursor
DELETE FROM ##tbl_contents
WHERE Files IS NULL
SELECT * FROM ##tbl_contents
DROP TABLE #tbl_drives
DROP TABLE ##tbl_contents
SET NOCOUNT OFF
July 28, 2008 at 1:27 pm
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.
July 29, 2008 at 3:11 am
If you are search for backup files then also give .dmp in your search criteria..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply