PowerShell script to backup/restore procedures for Migrating each database

 

Below is the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :

#region Load Assemblies and Global Error Handling

[System.Reflection.Assembly]::LoadWithPartialName( `

      "Microsoft.SqlServer.SMO") | Out-Null;

[System.Reflection.Assembly]::LoadWithPartialName( `

      "Microsoft.SqlServer.SMOExtended") | Out-Null;

 

# Simple global exception handling to see SQL Server errors

trap {

            $Exc = $_.Exception;

            while ( $Exc.InnerException )

            {

                  $Exc = $Exc.InnerException;

                  Write-Warning ("(generic trap) " + $Exc.Message);

            };

            break;

};

#endregion

 

#region Event Handlers

$PercentCompleteHandler = `

      [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] `

      {

            Write-Host ([string]$_.Percent + " percent processed.");

      };

$CompleteHandler = `

      [Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] `

      {

            Write-Host $_.Error.Message;

      };

#endregion

 

#region Functions definition

function Get-SQLInstance($InstanceName, $Login, $Password)

{

      $SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" `

                                            $InstanceName;

      if ($Login -eq $null) {

            $SQLInstance.ConnectionContext.LoginSecure = $true;

      }

      else {

            $SQLInstance.ConnectionContext.LoginSecure = $false;

            $SQLInstance.ConnectionContext.Login = $Login;

            $SQLInstance.ConnectionContext.Password = $Password;

      };

      # Force connection to get an early error message

      $SQLInstance.ConnectionContext.Connect();

      return $SQLInstance;

};

function DatabaseFullBackup ($SQLInstance, $DatabaseName, $BackupPath)

{

      $Backup = New-Object "Microsoft.SqlServer.Management.Smo.Backup";

      $Backup.Action = "Database";

      $Backup.Database = $DatabaseName;

      $Backup.Initialize = $true;

      $Backup.CopyOnly = $true;

      $Backup.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak" `

                                                , "File");

 

      $Backup.add_PercentComplete($PercentCompleteHandler);

      $Backup.add_Complete($CompleteHandler);

 

      $Backup.SqlBackup($SQLInstance)

};

function RestoreDatabaseFromFullBackup ($DestinationSQLInstance, $DatabaseName `

                                                , $BackupPath, $DataFilesPath, $LogFilesPath)

{

      $Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore";

      $Restore.FileNumber = 1;

      $Restore.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak"`

                                                , "File");

 

      foreach ($File in $Restore.ReadFileList($DestinationSQLInstance))

      {

            $NewFile = New-Object "Microsoft.SqlServer.Management.Smo.relocatefile";

            $NewFile.LogicalFileName = $File.LogicalName;

           

            #Primary Data File

            if    ($File.FileID -eq 1 -and $DataFilesPath -ne "")

            {    

                  $NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName `

                                                                  + "_" + $File.LogicalName + ".mdf");

            }

            #Secondary Data File

            elseif      ($File.Type -eq "D" -and $DataFilesPath -ne "")

            {       

                  $NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName`

                                                                  + "_" + $File.LogicalName + ".ndf");

            }

            #Log File

            elseif      ($File.Type -eq "L" -and $LogFilesPath -ne "")

            {

                  $NewFile.PhysicalFileName = ($LogFilesPath + "\" + $DatabaseName `

                                                                  + "_" + $File.LogicalName + ".ldf");

            };

 

            if ($NewFile.PhysicalFileName -ne $null) {

                  [void]$Restore.RelocateFiles.add($Newfile);

            };

      };

     

      $Restore.Database = $DatabaseName;

      $Restore.ReplaceDatabase = $true;

      $Restore.NoRecovery = $false;

      $Restore.Action = "Database";

 

      $Restore.add_PercentComplete($PercentCompleteHandler);

      $Restore.add_Complete($CompleteHandler);

 

      $Restore.SqlRestore($DestinationSQLInstance);

};

#endregion

 

#region Main

#Parameters

$SourceSQLInstanceName = "DESKTOP-02JIB76";                    # Mention your Source Instance Name

$DestinationSQLInstanceName = "DESKTOP-02JIB76\INS17";         # Mention your Destination InstanceName

 

$BackupPath = "E:\Backup\INS17\";                              # Mention your Backup Path which can be your Local Path or Shared Path

 

$DataFilesPath = "D:\INS17\DATA";                              # Mention your Destination DATA File Path

$LogFilesPath = "D:\INS17\LOG";                                # Mention your Destination LOG File Path

 

$DatabaseNames = "BLOB","panda","dbareports";                  # Mention your Databases Names which are needed to Migrate

 

#Main

$SourceSQLInstance = Get-SQLInstance $SourceSQLInstanceName;

$DestinationSQLInstance = Get-SQLInstance $DestinationSQLInstanceName;

 

foreach ($DatabaseName in $DatabaseNames)

{

      Write-Host ("`nBackup database [" + $DatabaseName + "]");

      DatabaseFullBackup      $SourceSQLInstance $DatabaseName $BackupPath;

 

      Write-Host ("`nRestore database [" + $DatabaseName + "]");

      RestoreDatabaseFromFullBackup       $DestinationSQLInstance $DatabaseName `

                                                      $BackupPath $DataFilesPath $LogFilesPath;

 

};

#endregion

Script to check the status of Encryption

****** Query to find the status of encryption *****

SELECT DB_NAME (e.database_id) AS DatabaseName, e.database_id, e.encryption_state,

CASE e.encryption_state

     WHEN 0 THEN 'No database encryption key present, no encryption'

     WHEN 1 THEN 'Unencrypted'

     WHEN 2 THEN 'Encryption in progress'

     WHEN 3 THEN 'Encrypted'

     WHEN 4 THEN 'Key change in progress'

     WHEN 5 THEN 'Decryption in progress'

END AS encryption_state_desc, c.name, e.percent_complete

FROM sys.dm_database_encryption_keys AS e

LEFT JOIN master.sys.certificates AS c

ON e.encryptor_thumbprint = c.thumbprint

Script to Get Security Information of SQL Server 2012 and Above

Script to find server level roles assigned to Server level logins \ roles

SELECT a.name as Name,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName, 

SUSER_NAME(b.role_principal_id) AS AssociatedServerRole 

FROM sys.server_principals a  JOIN sys.server_role_members b ON a.principal_id=b.member_principal_id 

WHERE a.is_fixed_role <> 1 AND a.name NOT LIKE '##%' AND a.name NOT LIKE 'NT%'

AND a.name <> 'public' ORDER BY Name, LoginType 

Script to find Server level permissions assigned to Server level logins \ roles

SELECT a.name AS Name, a.type_desc AS LoginType,b.class_desc AS ClassDesc 

,b.permission_name AS ServerLevelPermission,b.state_desc AS PermissionState 

FROM sys.server_principals a JOIN sys.server_permissions b 

ON a.principal_id = b.grantee_principal_id WHERE a.is_fixed_role <> 1 

AND a.name NOT LIKE '##%' AND a.name NOT LIKE 'NT%' AND a.name <> 'public'  ORDER BY Name 

Script to find all user database level roles assigned to database users \ roles

DECLARE @DBuser_sql VARCHAR(4000) 

DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), Authentication_type VARCHAR(250),AssociatedRole VARCHAR(200)) 

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType, CASE a.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END As AuthenticationType, 

USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a 

RIGHT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id 

WHERE a.sid IS NOT NULL AND a.type NOT IN (''C'') 

AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND a.name NOT LIKE ''NT%'' 

AND a.name NOT IN (''public'',''dbo'',''guest'') 

AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

INSERT @DBuser_table 

EXEC sp_MSforeachdb @command1=@dbuser_sql 

SELECT * FROM @DBuser_table ORDER BY DBName 

Script to find all user database level permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000) 

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), Permission VARCHAR(500), PermissionState VARCHAR(200)) 

SET @Obj_sql='SELECT ''?'' AS DBName,a.name AS UserName,CASE a.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END As AuthenticationType, 

b.permission_name AS Permission,b.state_desc AS PermissionState 

FROM ?.sys.database_principals a join 

?.sys.database_permissions  b on a.principal_id=b.grantee_principal_id 

WHERE a.name not in (''public'',''guest'',''dbo'') AND b.class <> 1 

AND a.name NOT LIKE ''NT%'' AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')'

INSERT @Obj_table 

EXEC sp_msforeachdb @command1=@Obj_sql 

SELECT * FROM @Obj_table ORDER BY DBName 

Script to find all user database level object permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000) 

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200)) 

SET @Obj_sql='SELECT ''?'' AS DBName,U.name as username,  CASE S.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END AS AuthenticationType, 

O.name as object,  permission_name AS permission from ?.sys.database_permissions 

JOIN ?.sys.sysusers U ON grantee_principal_id = uid 

JOIN ?.sys.database_principals S ON s.principal_id=U.uid 

JOIN ?.sys.sysobjects O ON major_id = id 

WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')'

INSERT @Obj_table 

EXEC sp_msforeachdb @command1=@Obj_sql 

SELECT * FROM @Obj_table ORDER BY DBName 

Script to get Security Information of SQL Server 2005,2008,2008R2

Script to find ALL server level logins and role assigned

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,

CASE WHEN b.sysadmin = 1 THEN 'sysadmin'

          WHEN b.securityadmin=1 THEN 'securityadmin'

          WHEN b.serveradmin=1 THEN 'serveradmin'

          WHEN b.setupadmin=1 THEN 'setupadmin'

          WHEN b.processadmin=1 THEN 'processadmin'

          WHEN b.diskadmin=1 THEN 'diskadmin'

          WHEN b.dbcreator=1 THEN 'dbcreator'

          WHEN b.bulkadmin=1 THEN 'bulkadmin'

          ELSE 'Public' END AS 'ServerRole'

FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'




Script to find ALL database users and roles assigned




DECLARE @DBuser_sql VARCHAR(4000)

DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a

LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id

WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'

INSERT @DBuser_table

EXEC sp_MSforeachdb @command1=@dbuser_sql

SELECT * FROM @DBuser_table ORDER BY DBName




Script to find Object level permission for ALL user databases




DECLARE @Obj_sql VARCHAR(2000)

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))

SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions

join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '

INSERT @Obj_table

EXEC sp_msforeachdb @command1=@Obj_sql

SELECT * FROM @Obj_table