About 20 minutes ago, I made a nice update of the status of every single record in a table because I didn’t highlight the criteria portion of a SQL statement. So, after I got over the sinking realization of what I had just done, and finished swearing, I realized I’d better pull down a backup and get to work. However, I only updated a single column in a single table, So I didn’t want to roll over the work of an entire business day, just recover the single column from a table that’s 99% read-only for most people. So, how do you do that?
In the old days, I would just do a force restore over a blank DB using the user interface. But now, the Azure stuff is so nice to do backups, I didn’t realize it didn’t do restores so nicely!
So, Here’s what I did: https://stackoverflow.com/questions/6267273/how-to-restore-to-a-different-database-in-sql-server Check out: Konstantin Chernov That guy is a life saver.
DECLARE @pathToBackup VARCHAR(1000) = 'https://mycoolbackupcontainer.blob.core.windows.net/db-backups/mybackupfile.bak';
DECLARE @pathToRestoreFolder VARCHAR(1000) ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\' ;
DECLARE @newDBName VARCHAR(1000) = 'TempCopyOfMyDB';
DECLARE @fileListTable TABLE (
[LogicalName] NVARCHAR(128),
[PhysicalName] NVARCHAR(260),
[Type] CHAR(1),
[FileGroupName] NVARCHAR(128),
[Size] NUMERIC(20,0),
[MaxSize] NUMERIC(20,0),
[FileID] BIGINT,
[CreateLSN] NUMERIC(25,0),
[DropLSN] NUMERIC(25,0),
[UniqueID] UNIQUEIDENTIFIER,
[ReadOnlyLSN] NUMERIC(25,0),
[ReadWriteLSN] NUMERIC(25,0),
[BackupSizeInBytes] BIGINT,
[SourceBlockSize] INT,
[FileGroupID] INT,
[LogGroupGUID] UNIQUEIDENTIFIER,
[DifferentialBaseLSN] NUMERIC(25,0),
[DifferentialBaseGUID] UNIQUEIDENTIFIER,
[IsReadOnly] BIT,
[IsPresent] BIT,
[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM URL ='''+ @pathToBackup+''' WITH CREDENTIAL = ''myCredentialThatIMadeEarlier''');
DECLARE @restoreDatabaseFilePath NVARCHAR(500)
DECLARE @restoreLogFilePath NVARCHAR(500)
DECLARE @databaseLogicName NVARCHAR(500)
DECLARE @logLogicName NVARCHAR(500)
DECLARE @pathSalt uniqueidentifier = NEWID()
SET @databaseLogicName = (SELECT LogicalName FROM @fileListTable WHERE [Type]='D')
SET @logLogicName = (SELECT LogicalName FROM @fileListTable WHERE [Type]='L')
SET @restoreDatabaseFilePath= @pathToRestoreFolder + @databaseLogicName + convert(nvarchar(50), @pathSalt) + '.mdf'
SET @restoreLogFilePath= @pathToRestoreFolder + @logLogicName + convert(nvarchar(50), @pathSalt) + '.ldf'
RESTORE DATABASE @newDBName FROM URL=@pathToBackup
WITH
CREDENTIAL = 'myCredentialThatIMadeEarlier',
MOVE @databaseLogicName TO @restoreDatabaseFilePath,
MOVE @logLogicName TO @restoreLogFilePath
SET NOCOUNT OFF
That ran, got a copy of what all the files were for the DB in the backup, then created a new database, told the system to put the data/log files in a new place and then I had my copy. It was awesome.
