Backup to drive is easy ..
Just
backup database [DbName] to DISK=’D:\\Backup\Dbname.bak’
or All database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'C:\Backup\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor |
but to network drive you need to do the following
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'advanced', 1 RECONFIGURE WITH override GO EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH override GO EXEC xp_cmdshell 'NET USE I: \\NetworkLocaltion /USER:Domain\Username password /PERSISTENT:yes' GO EXEC xp_cmdshell N'NET USE I: \\192.168.10.137\Backup /USER:Mhamudul\Administrators 123456 /PERSISTENT:yes' GO |
then you will see the network location on that backup drive location