SQL Server 相關

NAS 備份
exec master..xp_cmdshell 'net use z: \\192.168.1.11\backuper 2gaujrgr /user:backuper'

[透過指令備份]

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',
description N'組態選項的描述'
FROM sys.configurations
WHERE name in('show advanced options','xp_cmdshell')

DECLARE @DBPath nvarchar(120)

–指定磁碟機Z的路徑為\\192.168.8.201\SQLBackupLeon

exec master..xp_cmdshell 'net use z: \\192.168.1.11\backuper 2gaujrgr /user:backuper'

–指定備份路徑檔名

SET @DBPath = 'Z:\' + 'ReikoTEST' + '_' + Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','') + '.bak'

–DATENAME(Weekday,GETDATE())=>會顯示為"星期N"

–SET @DBPath = 'Z:\' + 'Leon' + '_' + DATENAME(Weekday,GETDATE()) + '.bak'

–備份資料庫ReikoTEST到路徑檔名

BACKUP DATABASE MISP TO DISK = @DBPath

–刪除磁碟機Z

exec master..xp_cmdshell 'net use Z: /delete'

GO

——————————————————————-

EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

SELECT name N'組態選項的名稱', value N'針對這個選項所設定的值', value_in_use N'這個選項目前有效的執行值',
description N'組態選項的描述'
FROM sys.configurations
WHERE name in('show advanced options','xp_cmdshell')