daily pastebin goal
49%
SHARE
TWEET

Untitled

a guest Nov 19th, 2018 117 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # Run SELECT @@servername in SQL Server Management Studio to get the value for $targetSqlserver
  2.  
  3. Import-Module sqlserver
  4.  
  5.  
  6.  
  7. # User defined global Variables for all databases
  8. # BEGIN - USER INPUT
  9. $targetSqlserver = "XXX\XXX"
  10. $databaseDataPath = "G:\SQL Server\Data"
  11. $databaseLogPath = "G:\SQL Server\Log"
  12. $dbUsername = "User_Website_Login"
  13. $dbPassword = "Password"
  14. # END - USER INPUT
  15.  
  16. $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($targetSqlserver)
  17. function CreateLoginUser() {
  18.     # Check if login user exists. If not, then create the login user.
  19.     $log = $server.Logins["$dbUsername"]
  20.     if (!$log){
  21.         $NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $dbUsername)
  22.         $NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
  23.         $NewLogin.Create($dbPassword)
  24.     }
  25.    
  26. }
  27.  
  28. #function RestoreDatabase ($databaseName, $backupFile, $logicalDataName, $logicalLogName) {
  29. function RestoreDatabase ($databaseName, $backupFile) {
  30.  
  31.  
  32.     # $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logicalDataName, "$databaseDataPath\$databaseName.mdf")
  33.     # $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logicalLogName, "$databaseLogPath\$databaseName.ldf")
  34.  
  35.     $relocateFiles = @()
  36.  
  37.     $rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
  38.     $bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($backupFile, 'File')
  39.     $rs.Devices.Add($bdi)
  40.     $fl = $rs.ReadFileList($server)
  41.     #$fl | ForEach-Object {echo $_.LogicalName  $_.Type }
  42.     $fl | ForEach-Object {
  43.         $extension = [System.IO.Path]::GetExtension($_.PhysicalName)
  44.         $fileLo =  $databaseLogPath+"\"+$databaseName+"_"+$_.LogicalName+$extension
  45.         $relocateFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($_.LogicalName, $fileLo)
  46.         $relocateFiles += $relocateFile
  47.     }
  48.  
  49.    
  50.    
  51.     # If the database we wish to restore already exists we need to drop it first.
  52.     if(![string]::IsNullOrEmpty($server.Databases["$databaseName"]))
  53.     {
  54.         echo "Existing database '$databaseName' found. Dropping database!"
  55.         Invoke-SqlCmd -ServerInstance $targetSqlserver "USE [master]; ALTER DATABASE [$databaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$databaseName]"
  56.     }
  57.  
  58.     # Restore database.
  59.     echo "Restoring database '$databaseName'."
  60.     Restore-SqlDatabase -ServerInstance $targetSqlserver -Database $databaseName -BackupFile $backupFile -RelocateFile $relocateFiles
  61.        
  62.     # # When restoring a database from an other server the database users are usually included.
  63.     # # Because these users does not exists on the target server as login users we need to delete them.
  64.     $database = $server.Databases["$databaseName"]
  65.     $users = $database.Users;
  66.     $test = @()
  67.     foreach($user in $users)
  68.     {
  69.         if($user.Name.Contains($dbUsername))
  70.         {
  71.             $test += $user.Name
  72.         }
  73.     }
  74.  
  75.     foreach($user in $test)
  76.     {
  77.         echo "Deleting existing database user!"
  78.         echo $user
  79.         $database.Users[$user].Drop();
  80.     }
  81.  
  82.     # Create a new database user for the login user
  83.     echo "Adding login user '$dbUsername' as db_owner."
  84.     $NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($database, $dbUsername)
  85.     $NewUser.Login = $dbUsername
  86.     $NewUser.Create()
  87.     $NewUser.AddToRole("db_owner")
  88.  
  89. }
  90.  
  91. CreateLoginUser
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top