December 22, 2016 at 1:32 pm
I have a sub folder on my 2008 sql server listing backup files. All I want to do is store the file names in a temporary database set up in tempdb. I've tried many permutations using xp_cmdshell to list the folders in the sub-directory.
a) I set up a bat file in the C root using a CD dos command to access the subfolder and a DIR command with a b and od option to list the files. Received 'C:\ListBatFiles.bat' is not recognized as an internal or external command when I tried to call the bat file using xp_cmdshell.
b)tried using a single string with carriage return and line feed commands to accomplish the same thing in a declared varchar variable. once again xp_cmdshell was not happy.
Tried double quotes, single within double and parentheses without joy.
And yes I did activate xp_cmdshell in the sql server.
Should I consider early retirement? Or perhaps there is an easier way to do this? Btw, this hardship is called within a stored procedure.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 22, 2016 at 2:09 pm
fizzleme (12/22/2016)
...a) I set up a bat file in the C root using a CD dos command to access the subfolder and a DIR command with a b and od option to list the files. Received 'C:\ListBatFiles.bat' is not recognized as an internal or external command when I tried to call the bat file using xp_cmdshell.
b)tried using a single string with carriage return and line feed commands to accomplish the same thing in a declared varchar variable. once again xp_cmdshell was not happy.
You may not need the batch file. the DIR command can take the directory to list, even if it's by UNC name on a network share:
EXEC xp_cmdshell 'DIR C:\Temp /B /OD'
If you wanted to put more than one command in the same line, you can use the & character:
EXEC xp_cmdshell 'C: & CD \Temp & DIR /B /OD'
If the folder name has a space in it, then maybe something like this:
EXEC xp_cmdshell 'DIR "C:\Program Files" /B /OD'
December 22, 2016 at 4:40 pm
The last option worked. Thank you.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 27, 2016 at 4:10 pm
Part 2. We have 2 2008 r2 servers with 2008 r2 sql server installed on both.
Our goal is to copy the full sql server backups from server A to server B so they can be restored on server b.
Tried using XP_CMDSHELL as follows
exec master..xp_cmdshell 'copy \\server_a\D$\Program Files\Microsoft SQL Server\server_a_BACKUPS\test_data1.bak \\server_b\D$\Program Files\Microsoft SQL Server\server_b_BACKUPS\test_data2.bak'
Error msg said "The system cannot find the file specified."
Also tried
exec master..xp_cmdshell 'copy "\\server_a\D$\Program Files\Microsoft SQL Server\server_a_BACKUPS\test_data1.bak \\server_b\D$\Program Files\Microsoft SQL Server\server_b_BACKUPS\test_data2.bak"'
Same error message.
I'd like to use wildcards for the server_a backups because the backup file names are date and time stamped. I looked at the file system object in bids but I when I tried the wildcard characters it was not content. Fundamentally this task is easy but after a few hours one gets tired and cranky.
And help would be much appreciated.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 27, 2016 at 4:43 pm
Please post new questions in a new thread in the future.
In this case, you would have to perform some more debugging. One common cause is that the account running xp_cmdshell (usually the service account or the Agent account) can't see the files. You would need to verify that the system sees the files. I would do this by copying a single file or just a dir with the specific file name listed.
I tend to use xcopy or Robocopy by default, as I think this works better with network copies. I don't have a fundamental reason, but this has seemed to work better.
If you are trying to copy all files, then why not just use xcopy \\folder\subfolder\*.* \ewfolder\subfolder\*.*
December 27, 2016 at 4:44 pm
Also note there are lots of scripts on this site that people have shared to do this: http://www.sqlservercentral.com/search/?q=copy+backup+files&t=s&sort=relevance
December 28, 2016 at 6:57 am
fizzleme (12/27/2016)
Part 2. We have 2 2008 r2 servers with 2008 r2 sql server installed on both.Our goal is to copy the full sql server backups from server A to server B so they can be restored on server b.
Tried using XP_CMDSHELL as follows
exec master..xp_cmdshell 'copy \\server_a\D$\Program Files\Microsoft SQL Server\server_a_BACKUPS\test_data1.bak \\server_b\D$\Program Files\Microsoft SQL Server\server_b_BACKUPS\test_data2.bak'
Error msg said "The system cannot find the file specified."
Also tried
exec master..xp_cmdshell 'copy "\\server_a\D$\Program Files\Microsoft SQL Server\server_a_BACKUPS\test_data1.bak \\server_b\D$\Program Files\Microsoft SQL Server\server_b_BACKUPS\test_data2.bak"'
Same error message.
I'd like to use wildcards for the server_a backups because the backup file names are date and time stamped. I looked at the file system object in bids but I when I tried the wildcard characters it was not content. Fundamentally this task is easy but after a few hours one gets tired and cranky.
And help would be much appreciated.
I notice you're using the administrative drive root shares D$ in your copy statement. If the account that xp_cmdshell is running as is not an administrator on both machines I don't think that will work. It might make more sense to setup a regular network share on the server_b destination folder and give read/write/modify permissions to the domain login doing the copy.
If not done already, you probably want to specify explicitly what domain login that xp_cmdshell will run as. Here's info on setting up a proxy account for that:
https://msdn.microsoft.com/en-us/library/ms190359.aspx
Also, most people use XCOPY:
https://technet.microsoft.com/en-us/library/cc771254(v=ws.11).aspx
or ROBOCOPY these days instead of regular COPY:
December 28, 2016 at 7:13 am
You need double quotes for each file path that contains a space, not just one pair for the whole lot - something like this:
exec master..xp_cm dshell 'copy "\\server_a\D$\Program Files\Microsoft SQL Server\server_a_BACKUPS\test_data1.bak" "\\server_b\D$\Program Files\Microsoft SQL Server\server_b_BACKUPS\test_data2.bak"'
John
December 30, 2016 at 1:33 pm
Yes, the service acct did not have adequate permissions. I changed it and this caused Idera Diagnostic Mgr to go south for that server. I generated a script to fix the SPN issue but the documentation said you need a Windows Domain acct to run it. So for now this is on the back burner.
But I digress. After much hardship I used Robocopy to copy the database backups to the server and used "Move" in xp_cmdshell to archive the backups. This was only challenging until I discovered when to use single and when to use double quotes. I'll take a second look at SSIS when time permits.
Bonne Annee!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply