bloginfo

SQL Server : cold backup of master, msdb, model databases

Mar 3rd, 2019 (edited)
212
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Clear-Host
  2. #  Invoke-Sqlcmd is running the SQL request in Set-Location context.
  3. Set-Location SQLSERVER:\SQL\CHEZNOI\DSFC
  4. [String]$sql="
  5.    SELECT
  6.        physical_name as path
  7.    FROM
  8.        sys.databases AS db
  9.        INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id
  10.    WHERE
  11.        db.name IN ('master','msdb','model')
  12. "
  13. $files=Invoke-Sqlcmd -Query $sql
  14. #Stop MSSQL Services to do consistent copy of database files
  15. [String]$sqlagent='SQLAgent$DSFC'
  16. [String]$mssql='MSSQL$DSFC'
  17. Stop-Service -Name $sqlagent -Force:$true
  18. Stop-Service -Name $mssql -Force:$true
  19. #Backup database files
  20. [String]$destination='d:\sauve'
  21. $files|Export-Csv -Path "$destination\emplacements.csv" -Force:$true -NoTypeInformation
  22. foreach($file in $files)
  23. {
  24.     Copy-Item -Path ($file).path -Destination $destination -Force:$true
  25. }
  26. #Redémarrage des services
  27. Start-Service -Name $mssql
  28. Start-Service -Name $sqlagent
  29. #Clean PowerShell objects
  30. $file=$null
  31. $files=$null
Add Comment
Please, Sign In to add comment