SSIS error while project deployment from the local machine to the server.

While doing a SSIS project deployment on my staging server from my desktop I ran into the following error

"Please create a master key in the database or open the master key in the session before performing this operation, ( Microsoft SQL server, Error:15581)"

The staging SSISDB database was a restore copy of the production .

I tried to alter the master key with the pwd from the prod
USE [SSISDB]
OPEN master Key decryption by password ='password' --my prod password
ALTER Master Key ADD encryption by Service Master Key

But again got the following error -

Msg 15313, Level 16, State 1, Line 2
The key is not encrypted using the specified decryptor.
Msg 15581, Level 16, State 7, Line 3
Please create a master key in the database or open the master key in the session before performing this operation.

Tried to create a master key -
Msg 15578, Level 16, State 1, Line 1
There is already a master key in the database. Please drop it before performing this statement.

tried to drop the master key -
use ssisdb
GO
drop master key

Got the following error
Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate 'MS_Cert_Proj_1' is encrypted by it.


Solution :
I backed up the SSIS Key on prod from the following

use ssisdb
GO

BACKUP MASTER KEY TO FILE = 'v:\backupkeys\SSISDBKey1
    ENCRYPTION BY PASSWORD = 'password' 

Then I copied the key file over to the test server in v:\backupkeys_staging\SSISDBKey1
use ssisdb
GO
RESTORE MASTER KEY FROM FILE = 'v:\backupkeys_staging\SSISDBKey1'
    DECRYPTION BY PASSWORD = 'password'  --prod password used to backup the key
    ENCRYPTION BY PASSWORD = 'newpassword' 
    FORCE  

Create and restore database using backup files from the network

I had a project where I had to create and restore the database from the backup files in a network.
I achieved it by following these steps.

• I moved all the backup files from the network to the local server in a backup  folder.
• Created a table BackupFileList to store the current database name and the backup file names using the command shell script
• Executed a dynamic SQL script to create all the databases.
• Executed the dynamic script to restore all the databases I made use of the created table to build a dynamic query to find the database name and the backup file.

( Note I had to use the MOVE command in the restore script since the physical path of the data file and the log file were different in the local server than the other server from where the backup was done.)


First of all need to make sure that the 'xp_cmdshell' is enabled.


EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

I ran  the following query to see the  physical path of the back up sets that was moved over from the network.

restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 1
go

[In SQL Server 2008 and later, you need to have CREATE DATABASE permission to get this information

 

 
If there are multiple backup sets on the backup device, specify the backup set number using the “WITH FILE = n” option.

 To specify the second backup set on the device run this code:

restore filelistonly from disk V:\SQL\Backup\Daily\DB1_backup_2015_06_22_194001_8960340.bak'
with file = 2
go ]


Script 1 :
Important!!!!
BEFORE RUNNING MAKE SURE THAT THE SERVICE ACCOUNT HAS PERMISSION IN THE PATH DIR.

--To create a backupfiles list and populate a table in the work db

--delete from HCFS_WORKDB..DBBackUpFilesList

CREATE TABLE #DirectoryList (
  line  VARCHAR(512))
DECLARE @f  table (Fname varchar(100))
DECLARE @Path varchar(256) = 'dir V:\SQL\Backup\Daily\'
DECLARE @Command varchar(1024) =  @Path+  '/b /a-d  /s *.bak ' --' /s /b /o:gn' --/a:d /s /b | sort'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL

delete from #DirectoryList where ( line not like ('%2015_06_24%')) --keep the recent backup file

delete from #DirectoryList where line like '%system%'
delete from #DirectoryList where line like '%\Diffferntial%'
delete from #DirectoryList where line not like '%\client_%'
--select line from #DirectoryList
insert into  @f
select SUBSTRING(line,charindex('\client_',line,1)+1,512 ) from #DirectoryList
--capture the database name and the filename in the table
insert into DB_WORKDB..DBBackUpFilesList (DBname,BackupfileName)
select rtrim(left(SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ),charindex('_backup',SUBSTRING(fname ,charindex('\client_',fname,1)+2,100 ),1))),SUBSTRING(fname ,charindex('\client_',fname,1)+1,100 ) from @f
--select substring(SUBSTRING(line,charindex('\client_',line,1)+1,512 ),charindex('\',line,1)+1,512)  from #DirectoryList
DROP TABLE #DirectoryList
GO

 


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

Script 2 : Create multiple databases using a cursor


declare @curClientCode as cursor ,@db varchar(100),
@sqlCreateDb nvarchar(max),
@dbName varchar(100)
SET @curClientCode = CURSOR FOR
--crete db for arm clients too
select name from sys.databases
 where name like 'client_%'

OPEN   @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = cast(@db as varchar)
SET @sqlCreateDb = 'USE  master' + CHAR(13) +
--'DROP DATABASE  ' +  @dbName + CHAR(13) +
' CREATE DATABASE ' + @dbName +  CHAR(13) +
' ON (NAME = ' + @dbName + '_data, FILENAME = ''E:\SQL\Data\' + @dbName +'.mdf'', Size = 100MB,MAXSIZE=unlimited, FILEGROWTH = 10)' + char(13) +
' LOG ON (NAME = ' + @dbName +  '_log, FILENAME = ''L:\SQL\Data\' + @dbName +'.ldf'', Size = 5MB,MAXSIZE=500MB, FILEGROWTH = 10); '
   

print @sqlCreateDb
exec sp_executesql @sqlCreateDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode


---------------------------------------------------------
Script 3 : Restore the database with MOVE command from the file
The backup files name were found from the table in the DB_workdb that was used earlier to capture the database name and the backup files


 use master
Go
declare @curClientCode as cursor ,@db varchar(10),
@sqlRestoreDb nvarchar(max),
@dbName varchar(20),
@backupFile varchar(100)
SET @curClientCode = CURSOR FOR
select name from sys.databases
 where name like 'client_%'
OPEN   @curClientCode
FETCH NEXT
FROM @curClientCode into @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbName = @db
SELECT  @backupFile = BackupFileName from HCFS_WORKDB..dbBackUpFilesList where dbname = @dbName
SET @sqlRestoreDb = 'ALTER DATABASE ' +  @dbName + char(13) +
' SET SINGLE_USER WITH  ROLLBACK IMMEDIATE ' + char(13)  + char(13)
SET @sqlRestoreDb  = @sqlRestoreDb + ' RESTORE DATABASE ' +  @dbName + char(13) +
' FROM DISK = ''V:\SQL\Backup\Daily\' + @dbName + '\'+  + @backupFile  + ''''+ char(13)+
 ' WITH  MOVE N''' + @dbName + ''' TO  N''E:\SQL\Data\' + @dbName + '.mdf'','  + char(13)+
 ' MOVE N''' + @dbName + '_log'' TO  N''L:\SQL\Data\' + @dbName + '.ldf'','  + char(13)+
 ' REPLACE, STATS = 10 ' +  char(13)
 
SET @sqlRestoreDb  = @sqlRestoreDb  + 'ALTER DATABASE ' +  @dbName + char(13) +
' SET MULTI_USER WITH  ROLLBACK IMMEDIATE ' + char(13)
 

print @sqlRestoreDb
exec sp_executesql @sqlRestoreDb;
FETCH NEXT
FROM @curClientCode into @db
END
CLOSE @curClientCode
DEALLOCATE @curClientCode

GO
      

restore msdb database

USE master
GO
--just to verify if the below command succeeds the restore should succeed too
RESTORE VERIFYONLY
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
--restore the database
RESTORE DATABASE [msdb]
FROM DISK = N'V:\SANSQL01Backups\Daily\msdb\msdb_backup_2014_10_05_194001_4668459.bak'
WITH REPLACE
GO
--msdb database was curripted and when to suspect mode. So fixed it by restoring a copy of a backup

Restore database

use master
GO

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE


GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO

Alter Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC sp_updatestats

I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE




Then after successfully restoring it I changed it to the multi user mode.
GO