參考MSDN網址
http://support.microsoft.com/kb/2019698/en-us
依網址實作是OK，不過滿好奇為什麼範例的不統一用一樣的名稱來呈現，
抓圖時間是20140412 10:39
使用Oracle VM VirtualBox4.3.4版，建立一個XP SP3的VM，安裝SQL Server 2008 R2 Express with Advanced Services。
步驟1.先COPY在MSDN上的stored procedure代碼，用SQL Server Management Studio → 新增查詢 → 執行，會在master產生一個預存程序sp_BackupDatabases。
(這段沒問題，XP目前還是用ADMINISTRATOR帳號)
步驟2. 再COPY 在MSDN上的Sqlbackup.bat代碼，sqlcmd -S .\SQLEXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"
(XP有切換帳號，又改有電腦名稱)
這段出現了問題
連線問題
SQLServer的組態管理 TCP/IP要啟用，同時裡面的IP位址→IPALL的動態通訊埠改成1433
權限問題
*因為要測工作排程，用另一組帳號去備，所以XP有新增一個ADMIN的帳號，但當初安裝SQL時，沒有新增ADMIN這個帳號，要注意執行stored procedure的帳號權限問題
*還有備份資料夾也要注意，要先去建立c:\sql\
*不知那邊copy錯，有個 " - " 符號讓我卡關，最後用一段一段去key，才測出問題，老師講的真好，真的不能一直用copy，這個最重要，不要只會COPY
sqlcmd -S vmtest01 -E -Q "EXEC sp_BackupDatabases @backupLocation='c:\sql\', @backupType='F'"
-S 伺服器名稱 -E 信任連接 -Q "命令行查詢" 並結束
後來備份成功後發現沒備份到Northwind，不過一開始沒認真看MSDN上的stored procedure代碼，又沒指定備份那個資料庫，以為資料庫會全部備份，後來再回頭看stored procedure代碼，原來有一段是指定不備份的語法，把Northwind拿掉，再傳要備份Northwind的參數就能單一備份Northwind了
就這段註記下面
-- Filter out databases which do not need to backed up
-- 篩選出哪些不需要備份的數據庫 (GOOGLE翻譯)
單一資料庫的備份AT，有先建另一個stored procedure
sqlcmd -S vmtest01 -E -Q "EXEC sp_BackupNorthwindDatabases @DatabaseName='Northwind', @backupLocation='c:\sql\', @backupType='F'"
步驟3.設定工作排程，為了先測試OK，先切換用ADMINISTRATOR帳號，新增一個工程排程去執行Sqlbackup.bat，測試也OK。
至於其他帳號備份的部份有空再測
========= 下面是新增 StoredProcedure 語法 ===========================
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] Script Date: 02/26/2016 09:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
@databaseName sysname = null,
@backupType CHAR(1),
@backupLocation nvarchar(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE
(
ID int IDENTITY PRIMARY KEY,
DBNAME nvarchar(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
AND name=@DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name
-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('model','msdb','tempdb','ReportServer$SQLEXPRESS','ReportServer$SQLEXPRESSTempDB','Northwind','pubs','AdventureWorks')
END
ELSE IF @backupType='D'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE IF @backupType='L'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE
BEGIN
RETURN
END
-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
-- Execute the generated SQL command
EXEC(@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
END
GO
========= 下面是新增 StoredProcedure 語法 結束 =======================
沒有留言:
張貼留言