在 powershell 執行以下的指令

# List of SES identities

$identities = @(

    "wztech.com.tw",

    "s3.com.tw",

    "jdcard.com.tw",

    "mskcable.com",

    "uwinfo.com.tw",

    "bike.idv.tw",

    "richwave.com.tw",

    "ctcn.com.tw",

    "jcard.com.tw",

    "bike@bike.idv.tw",

    "ee@ier.org.tw"

)



foreach ($identity in $identities) {

    # Convert identity to a valid topic name by replacing '@' and '.' with '_'

    $safeIdentity = $identity -replace "@", "_" -replace "\.", "_"

    $topicName = "SES_NOTIFY_$safeIdentity"

    $endpoint = "https://working.uwinfo.com.tw/aws/api/sns/receive?topic=$topicName"



    # Validate topic name format

    if ($topicName -notmatch '^[a-zA-Z0-9_\-\$]+$') {

        Write-Host "❌ Invalid topic name: $topicName"

        continue

    }



    # 1. Create SNS topic

    $topicArn = aws sns create-topic `

        --name $topicName `

        --query 'TopicArn' `

        --output text



    Write-Host "✔ Created topic: $topicArn"



    # 2. Subscribe webhook

    aws sns subscribe `

        --topic-arn $topicArn `

        --protocol https `

        --notification-endpoint $endpoint



    Write-Host "✔ Subscribed webhook: $endpoint"



    # 3. Link SES notifications

    foreach ($type in @("Delivery", "Bounce", "Complaint")) {

        aws ses set-identity-notification-topic `

            --identity $identity `

            --notification-type $type `

            --sns-topic $topicArn

        Write-Host "✔ $type linked to $topicName"

    }



    Write-Host "✅ Setup complete for $identity\n"

}



Write-Host "🎉 All identities processed."

收到的 log 會放在這裡: E:\WebBackup\195\ASP.NET Project\working\Data\Log\sns

 
 

在 powershell 執行以下的指令

# List of SES identities
$identities = @(
    "wztech.com.tw",
    "s3.com.tw",
    "jdcard.com.tw",
    "mskcable.com",
    "uwinfo.com.tw",
    "bike.idv.tw",
    "richwave.com.tw",
    "ctcn.com.tw",
    "jcard.com.tw",
    "bike@bike.idv.tw",
    "ee@ier.org.tw"
)

foreach ($identity in $identities) {
    # Convert identity to a valid topic name by replacing '@' and '.' with '_'
    $safeIdentity = $identity -replace "@", "_" -replace "\.", "_"
    $topicName = "SES_NOTIFY_$safeIdentity"
    $endpoint = "https://working.uwinfo.com.tw/aws/api/sns/receive?topic=$topicName"

    # Validate topic name format
    if ($topicName -notmatch '^[a-zA-Z0-9_\-\$]+$') {
        Write-Host "❌ Invalid topic name: $topicName"
        continue
    }

    # 1. Create SNS topic
    $topicArn = aws sns create-topic `
        --name $topicName `
        --query 'TopicArn' `
        --output text

    Write-Host "✔ Created topic: $topicArn"

    # 2. Subscribe webhook
    aws sns subscribe `
        --topic-arn $topicArn `
        --protocol https `
        --notification-endpoint $endpoint

    Write-Host "✔ Subscribed webhook: $endpoint"

    # 3. Link SES notifications
    foreach ($type in @("Delivery", "Bounce", "Complaint")) {
        aws ses set-identity-notification-topic `
            --identity $identity `
            --notification-type $type `
            --sns-topic $topicArn
        Write-Host "✔ $type linked to $topicName"
    }

    Write-Host "✅ Setup complete for $identity\n"
}

Write-Host "🎉 All identities processed."

--

收到的 log 會放在這裡: E:\WebBackup\195\ASP.NET Project\working\Data\Log\sns

 
 

create database booking (可以用 Workbench, root 或 sa 登入)

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

mysqldump -u root -p booking < d:\MySQLBackup\yours.bak

mysql -u sa -p booking < d:\MySQLBackup\yours.bak

建立使用者 (可以用 Workbench, root 或 sa 登入)

grant all privileges on booking.* to 'booking'@'%'; (可以用 Workbench, root 或 sa 登入)

FtpWebRequest類別沒有提供移動檔案的命令，但是可以透過WebRequestMethods.Ftp.Rename達成相同效果的功能。

    FtpWebRequest ftp_Request = null/* TODO Change to default(_) if this is not a reference type */;
    FtpWebResponse ftp_Response = null/* TODO Change to default(_) if this is not a reference type */;
    string str_FtpAct = "XXX";
    string str_FtpPwd = "XXX";

    // Try to create backup folder. If folder already created, resume to backup. 
    try
    {
        ftp_Request = (FtpWebRequest)WebRequest.Create("D:/testfolder/backup");
        ftp_Request.Credentials = new NetworkCredential(str_FtpAct, str_FtpPwd);
        ftp_Request.Method = WebRequestMethods.Ftp.MakeDirectory;
        ftp_Response = ftp_Request.GetResponse();
    }
    catch (WebException ex_Web)
    {
        FtpWebResponse ftp_ResponseEx = ex_Web.Response;
        int int_ErrCode = ftp_ResponseEx.StatusCode;
        // 550: Access is denied, means directory already exist
        if (int_ErrCode != 550)
            Console.WriteLine("Create Folder Failed!");
    }

    try
    {
        ftp_Request = (FtpWebRequest)WebRequest.Create("D:/testfolder/test.txt");
        ftp_Request.Credentials = new NetworkCredential(str_FtpAct, str_FtpPwd);
        ftp_Request.Method = WebRequestMethods.Ftp.Rename;
        ftp_Request.RenameTo = "../testfolder/backup/test.txt";
        ftp_Response = (FtpWebResponse)ftp_Request.GetResponse();
    }
    catch (WebException webex)
    {
        // Get FTP Error code and exception status
        FtpWebResponse ftp_ResponseEx = webex.Response;
        int int_FtpCode = ftp_ResponseEx.StatusCode;
         // Do something
    }


    finally
    {
        if (ftp_Response != null)
            ftp_Response.Close();
    }

由於工作排程往往有數十個甚至百來個 因此需要一個方法能快速備份及移轉到其他 server 的方法
查了一下，好像也只能這樣做

---- 使用指令把全部排程匯出 ----
參考網址
https://msdn.microsoft.com/en-us/library/windows/desktop/bb736357%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396

schtasks /query /XML > all_tasks.xml  
schtasks /query /FO CSV /V >sched_tasks.csv

** xml 比較有用,可以用來匯入到其他server排程，但還要額外處理才能匯入
** csv 只是用來看看目前有哪些排程 可以用來做報表看看
** 這些是全部排程，還要特別處理把 Microsoft 及其他軟體建的排程移除

--------------------------------------------------------------

----如何匯入工作排程到其他 server --------

** 需再寫程式把 all_tasks.xml 拆解成所有排程的單一 xml
   並且生成指令 bat

** 注意 xml 必須是 UTF-16 (unicode) 
   因為 "schtasks /query /XML > all_tasks.xml"產生 xml 是 ansi 

參考網址
https://serverfault.com/questions/325569/how-do-i-import-multiple-tasks-from-a-xml-in-windows-server-2008
** /TN "排程名稱" --- 排程名稱可以是路徑名 
D:\>schtasks.exe /create /TN "\2016JOB\Global\CountryMonthlyStats" /XML "D:\one_task.xml"
** 下面程式碼只是下面程式碼只是參考 還沒實作過
=====================================

var taskXML = new XmlDocument();
taskXML.Load(@"d:\temp\schedtasksBackup.xml");
var batbody = new StringBuilder();

XmlNodeList tasks = taskXML.DocumentElement.GetElementsByTagName("Task");

string strFileName = "d:\\temp\\Task";

for (int i = 0; i < tasks.Count; i++) {
    string onetaskXML = tasks[i].OuterXml;

    //Create the New File. With a little more extra effort
    //you can get the name from a comment above the task -> 應該把註解裡的名稱抓出
    XmlWriter xw = XmlWriter.Create(strFileName + "_" + (i+1) + ".xml");
    batbody.AppendLine(string.Format("schtasks.exe /create /TN \"{0}\" /XML \"{1}\"", "Task " + (i+1) + " Name", strFileName + "_" + (i+1) + ".xml"));

    //Write the XML
    xw.WriteRaw(onetaskXML.ToString());
    xw.Close();

    // Write a bat to import all the tasks
    var batfile = new System.IO.StreamWriter("d:\\temp\\importAllTasks.bat");
    batfile.WriteLine(batbody.ToString());
    batfile.Close();

}
=====================================

#微軟的排程備份真是有夠爛沒有辦法用介面一鍵搞定

SQL備份出現錯誤訊息：
Cannot open backup device 'D:\xxxxxxx.bak'
Operationg system error 5(存取被拒。)


排除方法：

1. 不要使用 Network Services 來啟動，改使用別的帳號啟動，這樣以後備份匯出就沒有問題。
    
    
2. 修改要匯出資料夾的權限，讓 Network Services 有寫入的權限，這樣就可以正常備份了。
    
    

參考：http://rojerchen.blogspot.tw/2013/07/mssql5.html

想要讓特定 user 可以備份某個 DB ，但又不想把該 user 加入 sysadmin 時，可以用的。

use DBName
GRANT BACKUP DATABASE TO uasername

接下來該使用者的帳戶就可以執行
​
BACKUP DATABASE XXX TO DISK = 'D:\SQLBackup\XXXXX.bak'

不同主機，windows登入帳號不同時，需先指定帳密才可備份。

-- To allow advanced options to be changed.開啟進階選項

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.執行動作

RECONFIGURE

GO

-- To enable the feature.開啟xp_cmdshell功能

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.執行動作

RECONFIGURE

GO





DECLARE @DBPath nvarchar(120)

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

exec master..xp_cmdshell 'net use z: \\192.168.8.201\SQLBackupLeon 密碼 /user:帳號'

--指定備份路徑檔名

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 ReikoTEST TO DISK = @DBPath

--刪除磁碟機Z

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

GO





-- To enable the feature.關閉xp_cmdshell功能

EXEC sp_configure 'xp_cmdshell', 0

GO

-- To update the currently configured value for this feature.執行動作

RECONFIGURE

GO

-- To allow advanced options to be changed.關閉進階選項

EXEC sp_configure 'show advanced options', 0

GO

-- To update the currently configured value for advanced options.執行動作

RECONFIGURE

GO

※ xp_cmdshell不要常開著，有風險，用完要關掉。
※ 網路磁碟機路徑要開啟安全性設定，設定為：\\[IP]\[資料夾]，不可用\\[IP]\D$\[資料夾]
※ net use 指定資料夾後方，不可加 \
參考資料：http://ithelp.ithome.com.tw/question/10086406

如下，請試試，我在 96 上面執行後，Log 檔的大小從 56 G 降到 1.x G:

BACKUP LOG [ShopUNT] TO  DISK = N'D:\UNTLog.bak'

DBCC SHRINKFILE(ShopUNT_Log, 1)

如果沒有跑 BACKUP LOG，DBCC SHRINKFILE 無法縮小 Log 檔的大小。最好先跑一下 Backup DB。

1. ＂有疑問＂的 資料庫的修復:

ALTER DATABASE MyDB SET EMERGENCY;
DBCC checkdb('MyDB');
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckDB ('MyDB', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE MyDB SET MULTI_USER;

其中 "DBCC CheckDB ('MyDB', REPAIR_ALLOW_DATA_LOSS)" 可能要多執行幾次。

2. Restore Differential Backup 用的 SQL:
​
RESTORE DATABASE [MyDB] FROM  DISK = N'D:\BACKUP_DB\MyDB_backup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10,
MOVE 'MyDB_Data' TO 'D:\SQLDB2008\MyDB.mdf', 
MOVE 'MyDB_Log' TO 'D:\SQLDB2008\MyDB.ldf'

RESTORE DATABASE [MyDB] FROM  DISK = N'D:\BACKUP_DIFF\MyDB_backup_2013_02_25_12.bak' WITH  FILE = 1,  NOUNLOAD, NORECOVERY,  STATS = 10

RESTORE DATABASE [MyDB] WITH RECOVERY (正在還原的資料庫也可以用這個指令來恢復成可使用的狀態)