Hi,
I need to perform backup and save it in network drive. I have given all permissions to the backup drives and mapped in source server as well. I successfully executed below command in sql.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
EXEC xp_cmdshell 'net use Z: \\Backupserver\l'
EXEC xp_cmdshell 'net use Y: \\Backupserver\m'
Also if I run below command i can see the output as well.
EXEC xp_cmdshell 'Dir Y:' (Means without any error i can see the disk details in output)
output
Volume in drive Y is Data2
Volume Serial Number is 902A-F0FE
NULL
Directory of Y:\
NULL
12/01/2021 09:42 PM <DIR> Program Files (x86)
0 File(s) 0 bytes
1 Dir(s) 664,076,779,520 bytes free
NULL
I Refreshed the sql instance and tried backup the database in network drive. Though above all commands executed successfully why i am not able to see Z,Y disks while backup in SSMS.
I disconnected and re mapped disks, and tried with other letters. All possibilities I tried but not able to see network disks in GUI Backup window. As it is a Prod server i can not restart the instance.
Any perticular reason why i am able to see disk details in output window by executing EXEC xp_cmdshell 'Dir Y:' but not visible in backup GUI ?
Please help me . I have only 5 hrs time to initiate the backup.
Thanks,
Jo
May 18, 2022 at 5:01 pm
As a hunch, the session created by xp_cmdshell is not persistent. If you want to use mapped drives, create those directly in Windows. Instead of mapping a drive, try using the full path in the backup statement. The service account or machine account if a VSA (myserver$) needs permission on the Backupserver folder to succeed.
Backup database MyDB to disk '\\Backupserver\myDB.bak'
--Will
Edit: What they said.
It may be that the drive is mapped for your user, but not the SQL Service account, or whatever account the GUI uses.
Have you tried writing a script rather than using the GUI? either using the mapped drive or the UNC Path.
BACKUP DATABASE DBName TO DISK = '\\SERVER\PATH\dbname.bak'
BACKUP DATABASE DBName TO DISK = 'Z:\backups\dbname\dnname.bak'
Do you need to backup the database to two drives? or create multiple files split between the two drives?
BACKUP DATABASE DBName TO DISK = 'Z:\backups\dnname1.bak', 'Y:\backups\dbname2.bak'
This is an example from Adventure works with some of the options I did not include.
BACKUP DATABASE [AdventureWorks2017] TO
DISK = N'C:\Backups\AdventureWorks2017_Backup_1.bak',
DISK = N'C:\Backups\AdventureWorks2017_Backup_2.bak',
DISK = N'C:\Backups\AdventureWorks2017_Backup_3.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
May 18, 2022 at 5:28 pm
Hi Ed B,
Thanks Allot. As per your suggestion it worked with command.
Somehow I missed the small logic to try with command.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy