In an ironic twist of fate I adapted my ‘Drop all temp-tables’ script, to drop all tables beginning with an underscore.
Yes, it is ironic, because it uses a temp-table to store the working list of tables to be deleted. Whilst my original script used a real table to store a list of temp-tables.
Well … ok then
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
WHERE [name] LIKE '/_%' ESCAPE '/'
DECLARE @table VARCHAR(200), @cmd VARCHAR(500)
WHILE (SELECT COUNT(*) FROM #tables) > 0
SET @table = (SELECT TOP(1) [name] FROM #tables)
SET @cmd = 'drop table ' + @table
DELETE FROM #tables WHERE [name] = @table
(In case you were wondering why I created these underscore-tables in the first place. I was refactoring a stored-procedure that took over an hour to run, and had a large number of temp-tables. I wanted to persist those temp-tables for another day, and not have to start from scratch.)
Normally I would not bother, but when CHECKDB runs on TempDB it cannot use a snapshot so has to lock all the temp tables. This script will wait for exclusive access for up to a minute.
DECLARE @outcome VARCHAR(50) = 'TempDB is currently too busy for CHECHDB',
@endtime DATETIME = DATEADD(mi,1,GETDATE())
WHILE GETDATE() < @endtime
IF NOT EXISTS (SELECT 1 FROM sys.dm_tran_locks WHERE request_mode = 'X' AND resource_database_id = 2)
DBCC CheckDB([tempdb]) WITH NO_INFOMSGS;
SET @outcome = 'success'
WAITFOR DELAY '00:00:01';
Post migration, I wanted to make sure an encrypted database on a new SQL 2014 Enterprise edition server could be restored.
I installed SQL 2014 Developer edition on a second machine and initially got the expected error …
Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint.
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally
1. I checked if the target server was enabled for TDE …
SELECT * FROM sys.symmetric_keys
This returned one row, I checked against the source server and that returned two rows. I concluded that the target server was NOT yet TDE enabled.
2. To enable TDE on the target …
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = '[SomePwIJustMadeUp]';
3. Next I copied over the two files (*.cer and *.pvk) from the backup location to the target server and installed the certificate into Master …
CREATE CERTIFICATE [NameOfTheDatabase]_Cert2
FROM FILE = '[PathAndNameOfLocalCopyOfCertFile].cer'
WITH PRIVATE KEY (FILE = N'[PathAndNameOfLocalCopyOfPvkFile].pvk',
DECRYPTION BY PASSWORD ='[TheSourcePwIGotFromKeepAss]');
4. After which I was able to restore the database as normal.