Introduction
In an earlier post this week, I walked through the process of executing a file level restore of a Microsoft SQL database using NetWorker. The databases had been backed up in the NetWorker module – and the recovery was done in the NetWorker module as well, but rather than recovering to a live, actual database, I recovered to a file, something SQL DBAs like to be able to do.
So, with the SQL server setup, I wanted to go through the same process in Avamar. (This was a good opportunity for me to do some practice on Avamar command line options, too!) In this setup, I’m using Avamar 18.1, writing to a DDVE running DDOS 6.2.
Recovering in the Avamar GUI
With the database successfully backed up in Avamar, I logged into the Avamar HTML5 UI and went to the Backup/Recovery pane:
Within the Backup/Restore pane, I went across to Restore, and browsed down to my /clients so I could select the SQL server, ‘win01’. This allowed me to then select the type of backup I wanted to recover from:
With the SQL backups selected, the next step was to drill down to the database I wanted to recover: TestDB:
Clicking Restore then allowed me to go through the recovery options dialog. First step of course is choosing where I want to recover to:
After choosing the client to recover to, I clicked Next, where I could choose the recovery:
You’ll note in the above that I’ve changed the restore type to “Restore SQL Server backup as files to the file system”, and I’d used the “Choose…” button to browse the client and select a recovery location, E:\Temp.
There’s a “More Options” panel in the recovery dialog, but I didn’t need to change anything there, so I just clicked Next.
With the recovery details confirmed, I could click Finish to initiate the recovery. From that point, it was a case of jumping across to the Activity pane.
There’s the recovery running. (The recovery underneath was for a CLI recovery, which I’ll run through next.)
I monitored the recovery in the activity pane until it was complete:
With the recovery complete, I wanted to check out the restored files:
Now, those restored files may look odd, but if you rename them to having a “.bak” at the end, SQL Management Studio will work with them. They’re basically created from the number of streams you run in the backup:
Recovering from the Command Line
So I wanted to do this from the command line like I’d do on NetWorker. So the first step was to list the backups that I had on the client. In this post, I’m using the MCUser account, but if you’re enabling your SQL DBAs to do these sorts of recoveries, you’ll have setup an account for them to use in Avamar.
C:\Users\Administrator>avtar --backups --id=MCUser --account=/clients/win01
avtar Info <5551>: Command Line: avtar --backups --id=MCUser --account=/clients/win01
avtar Info <7977>: Starting at 2019-04-17 17:08:29 AUS Eastern Standard Time [avtar Jun 13 2018 17:36:50 18.1.100-33 Windows Server 2016 Standard Evaluation Server Edition (No Service Pack) 64-bit-AMD64]
Password:
avtar Info <6555>: Initializing connection
avtar Info <5552>: Connecting to Avamar Server (kronos.turbamentis.int)
avtar Info <5554>: Connecting to one node in each datacenter
avtar Info <5583>: Login User: "MCUser", Domain: "default", Account: "/clients/win01"
avtar Info <5580>: Logging in on connection 0 (server 0)
avtar Info <5582>: Avamar Server login successful
avtar Info <10632>: Using Client-ID='470d5398dd1febe114c90b2d1911378ceb87fed7'
avtar Info <5550>: Successfully logged into Avamar Server [18.1.0-33]
avtar Info <7377>: Backups for /clients/win01 as of 2019-04-17 17:08:33 AUS Eastern Standard Time
Date Time Seq Label Size Plugin Working directory Targets
2019-04-17 14:47:32 9 SQL Backup-1555476266562#0 3184769K SQL \?\C:\Program Files\avs\var
2019-04-17 07:59:26 1 Windows-1555448593268 54588708K Windows C:\Program Files\avs\var
avtar Info <5314>: Command completed (exit code 0: success)
This let me identify the backup I wanted to use – “SQL Backup-1555476266562#0”. The next step is to list that backup so I could confirm the database was inside there:
E:\>avtar --list --id=MCUser --label="SQL Backup-1555476266562#0" --account=/clients/win01
avtar Info <5551>: Command Line: avtar --list --id=MCUser --label="SQL Backup-1555476266562#0" --account=/clients/win01
avtar Info <7977>: Starting at 2019-04-17 19:38:58 AUS Eastern Standard Time [avtar Jun 13 2018 17:36:50 18.1.100-33 Windows Server 2016 Standard Evaluation Server Edition (No Service Pack) 64-bit-AMD64]
Password:
avtar Info <6555>: Initializing connection
avtar Info <5552>: Connecting to Avamar Server (kronos.turbamentis.int)
avtar Info <5554>: Connecting to one node in each datacenter
avtar Info <5583>: Login User: "MCUser", Domain: "default", Account: "/clients/win01"
avtar Info <5580>: Logging in on connection 0 (server 0)
avtar Info <5582>: Avamar Server login successful
avtar Info <10632>: Using Client-ID='470d5398dd1febe114c90b2d1911378ceb87fed7'
avtar Info <5550>: Successfully logged into Avamar Server [18.1.0-33]
avtar Info <8745>: Backup from Windows Server 2016 Standard Evaluation Server Edition (No Service Pack) 64-bit host "/clients/win01" (win01) with plugin 3006 - Windows SQL
avtar Info <5538>: Backup #9 label "SQL Backup-1555476266562#0" timestamp 2019-04-17 14:47:32 AUS Eastern Standard Time, 0 files, 3.037 GB
avtar Info <40113>: Backup #9 created by avtar version 18.1.100-33
(local)\
(local)\TestDB\
(local)\TestDB\f-0.TestDB.stream1
(local)\TestDB\f-0.TestDB.stream0
(local)\ReportServerTempDB\
(local)\ReportServerTempDB\f-0.ReportServerTempDB.stream0
(local)\ReportServer\
(local)\ReportServer\f-0.ReportServer.stream0
(local)\model\
(local)\model\f-0.model.stream0
(local)\msdb\
(local)\msdb\f-0.msdb.stream0
(local)\master\
(local)\master\f-0.master.stream0
avtar Info <5314>: Command completed (exit code 0: success)
Now, the next step was to run the recovery. That’s with the avsql command, which is documented very completely within the Avamar SQL Client guide.
C:\Users\Administrator>avsql --operation=restore --id=MCUser --ap=SuperSecretPassword --logtail=false --account=/clients/win01 --label="SQL Backup-1555476266562#0" --redirecttofile=true --target=C:\temp (local)/TestDB --log=C:\Temp\recovery.log
avsql Info <5008>: Logging to C:\Temp\recovery.log
avsql Info <6673>: CTL listening on port 64004
avsql Info <12592>: Sending adhoc request to the MCS
avsql Info <10684>: Setting ctl message version to 3 (from 1)
avsql Info <16136>: Setting ctl max message size to 268435456
avsql Info <17376>: Working on stand alone environment.
avsql Info <7838>: - Adding user exclude "/tempdb" avsql Info <16262>: Setting up labelnum: (9) for given label: (SQL Backup-1555476266562#0) avsql Info <9552>: Spawning 'C:\Program Files\avs\bin\avtar' (avtar --case_sensitive="false" --max-streams="1" --ctlcallport="64004" --ctlinterface="3006-COD-1555489492579" --check-stdin-path="false" --logfile="C:\Program Files\avs\var\COD-1555489492579#1-3006-SQL.avtar.log" --vardir="C:\Program Files\avs\var" --bindir="C:\Program Files\avs\bin" --sysdir="C:\Program Files\avs\etc" --acnt="/clients/win01" --id="MCUser" --ap="" --server="kronos.turbamentis.int") avsql Info <6686>: Process 5292 (C:\Program Files\avs\bin\avtar) for workorder COD-1555489492579#1 started avsql Info <7732>: Waiting for avtar to finish avtar Info <5008>: Logging to C:\Program Files\avs\var\COD-1555489492579#1-3006-SQL.avtar.log avtar Info <5551>: Command Line: avtar --case_sensitive=false --max-streams=1 --ctlcallport=64004 --ctlinterface=3006-COD-1555489492579 --check-stdin-path=false --logfile="C:\Program Files\avs\var\COD-1555489492579#1-3006-SQL.avtar.log" --vardir="C:\Program Files\avs\var" --bindir="C:\Program Files\avs\bin" --sysdir="C:\Program Files\avs\etc" --account=/clients/win01 --id=MCUser --password=* --server=kronos.turbamentis.int --ctlusessl=true
avtar Info <7977>: Starting at 2019-04-17 18:25:03 AUS Eastern Standard Time [avtar Jun 13 2018 17:36:50 18.1.100-33 Windows Server 2016 Standard Evaluation Server Edition (No Service Pack) 64-bit-AMD64]
avsql Info <10684>: Setting ctl message version to 3 (from 1)
avtar Info <10684>: Setting ctl message version to 3 (from 1)
avsql Info <16136>: Setting ctl max message size to 268435456
avtar Info <16136>: Setting ctl max message size to 268435456
avtar Info <6767>: Successfully connected to 127.0.0.1:64004
avtar Info <5900>: Workorder received:
¨C9C ¨C22C ¨C23C
avtar Info <18776>: Restore target is Win2012 - initializing WSS SIS Manager.
avtar Info <8474>: - Log file path: C:\Program Files\avs\var\COD-1555489492579#1-3006-SQL.avtar.log
avtar Info <6555>: Initializing connection
avtar Info <5552>: Connecting to Avamar Server (kronos.turbamentis.int)
avtar Info <5554>: Connecting to one node in each datacenter
avtar Info <5993>: - Connect: Connected to 192.168.100.39:29000, Priv=0, SSL Cipher=ECDHE-RSA-AES256-GCM-SHA384
avtar Info <5993>: - Datacenter 0 has 1 nodes: Connected to 192.168.100.39:29000, Priv=0, SSL Cipher=ECDHE-RSA-AES256-GCM-SHA384
avtar Info <5583>: Login User: "MCUser", Domain: "default", Account: "/clients/win01"
avtar Info <5580>: Logging in on connection 0 (server 0)
avtar Info <5582>: Avamar Server login successful
avtar Info <5018>: - Session ID: 0
avtar Info <10632>: Using Client-ID='470d5398dd1febe114c90b2d1911378ceb87fed7'
avtar Info <5550>: Successfully logged into Avamar Server [18.1.0-33]
avtar Info <5295>: Starting restore at 2019-04-17 18:25:04 AUS Eastern Standard Time as "WIN01\Administrator" on "win01" (2 CPUs) [18.1.100-33]
avtar Info <19114>: --restoreshortnames flag not specified; 8.3 names (if available in the backup) will not be restored with files/dirs. If the target OS supports 8.3 names, then the OS will create 8.3 names automatically.
avtar Info <10534>: Initializing session on Data Domain: 1, mode:RESTORE, boost:ENABLED, compressed-restore:disabled, max-streams:1, pool-size:67108864, queue-size:6, read-size:131072
avtar Info <10535>: - DDR naming options: Filenames: UNIQUE, ClientID:CID, BackupID:HEX-TIME, Avamar Server Name:DPNID
avtar Info <16684>: - Data Domain Engine (3.4.2.0 build 593989)
avtar Info <40174>: Multi-stream restore via cloning is enabled.
avtar Info <10632>: Using Client-ID='470d5398dd1febe114c90b2d1911378ceb87fed7'
avtar Info <40062>: GSAN connected via: IPv4, retrieved Data Domain IPv4 hostname = singularity.turbamentis.int
avtar Info <10539>: Connecting to Data Domain Server "singularity.turbamentis.int"(1) (LSU: avamar-1516143927, User: "")
avtar Info <10540>: - Resolved Data Domain Server name "singularity.turbamentis.int" to the IP address "192.168.100.249"
avtar Info <41234>: - Connecting to Data Domain Server name "singularity.turbamentis.int" with credentials
avtar Info <19156>: - Establishing a connection to the Data Domain system with encryption (Connection mode: A:3 E:2).
avtar Info <43204>: - Connected to:
Data Domain System: singularity.turbamentis.int
Model: DD VE Version 4.0
DDOS: Data Domain OS 6.2.0.5-608869
Serial Number: AUDV77VJJYAMZU
Capacity
Total: 384,428,408,832 (358.03 GB)
Available: 328,593,309,696 (306.03 GB)
Used: 55,835,099,136 (52.000 GB)
Used logical: 674,935,967,197 (628.58 GB)
avtar Info <41435>: - Data Domain configured in Stand-Alone mode.
avtar Info <40206>: Setting default storage unit to 'avamar-1516143927' for handle 1
avtar Info <41440>: Data Domain handle:1 capabilities:0x0820021B
avtar Info <10543>: Data Domain login to singularity.turbamentis.int successful
avtar Info <18845>: Restore mode - No Data Domain read compression.
avtar Info <19156>: - Establishing a connection to the Data Domain system with encryption (Connection mode: A:3 E:2).
avtar Info <41359>: Backup #9 had been created by avtar version 18.1.100-33 as plugin 3006-SQL
avtar Info <5949>: Backup file system character encoding is 65001 (UTF-8).
avtar Info <8745>: Backup from Windows Server 2016 Standard Evaluation Server Edition (No Service Pack) 64-bit host "/clients/win01" (win01) with plugin 3006 - Windows SQL
avtar Info <5538>: Backup #9 label "SQL Backup-1555476266562#0" timestamp 2019-04-17 14:47:32 AUS Eastern Standard Time, 0 files, 3.037 GB
avtar Info <17991>: Attempting to restore sis.hs file. If successful, then backup was done on a Windows Storage Server with Windows SIS feature enabled.
avtar Warning <17990>: File 'sis.hs' could not be restored. If backup contains SIS links they will not be converted to regular files and will not be accessible. Otherwise, backup was done on a non-SIS enabled system and this warning message can be safely ignored.
avtar Info <5291>: Estimated size for "(local)\TestDB\f-0.TestDB.stream0" is 1.425 GB
avtar Warning <19107>: Unable to determine if there is enough disk space for the specified restore.
avtar Info <7324>: Volume Type for "C:\" is "NTFS", Supports Compression=1, Encryption=1, ACLS=1, DataStreams=1, Reparse=1, Sparse=1, Hardlinks=1
avtar Info <5260>: Restoring files from "(local)\TestDB\f-0.TestDB.stream0" to directory "C:\temp(local)\TestDB".
avtar Info <18076>: Opening DD stored file system container 1 in /cur/470d5398dd1febe114c90b2d1911378ceb87fed7/1D4F4D8AB21902C for restore operation, using pre-fetched ddr_files.xml.
avtar Info <15265>: Opening file system container /cur/470d5398dd1febe114c90b2d1911378ceb87fed7/1D4F4D8AB21902C//cur/470d5398dd1febe114c90b2d1911378ceb87fed7/1D4F4D8AB21902C/container.1.cdsf for restore.
avtar Info <5267>: Restore of "(local)\TestDB\f-0.TestDB.stream0" completed
avtar Info <17991>: Attempting to restore sis.hs file. If successful, then backup was done on a Windows Storage Server with Windows SIS feature enabled.
avtar Warning <17990>: File 'sis.hs' could not be restored. If backup contains SIS links they will not be converted to regular files and will not be accessible. Otherwise, backup was done on a non-SIS enabled system and this warning message can be safely ignored.
avtar Info <5291>: Estimated size for "(local)\TestDB\f-0.TestDB.stream1" is 1.570 GB
avtar Warning <19107>: Unable to determine if there is enough disk space for the specified restore.
avtar Info <5260>: Restoring files from "(local)\TestDB\f-0.TestDB.stream1" to directory "C:\temp(local)\TestDB".
avtar Info <5267>: Restore of "(local)\TestDB\f-0.TestDB.stream1" completed
avtar Info <7925>: Restored 2.995 GB from selection(s) with 2.995 GB in 2 files
avtar Info <6090>: Restored 2.995 GB in 1.64 minutes: 109.8 GB/hour (73 files/hour)
avtar Info <7883>: Finished at 2019-04-17 18:26:40 AUS Eastern Standard Time, Elapsed time: 0000h:01m:38s
avtar Info <40176>: - Multi-stream restore summary (current 0, max active 1, max cloned 0, total cloned 0, ddr handle 1):
avtar Info <40178>: - 1 - /cur/470d5398dd1febe114c90b2d1911378ceb87fed7/1D4F4D8AB21902C/container.1.cdsf
avtar Info <8468>: Sending wrapup message to parent
avtar Info <5314>: Command completed (4 warnings, exit code 0: success)
avsql Info <6688>: Process 5292 (C:\Program Files\avs\bin\avtar) finished (code 0: success)
avsql Info <14302>: Redirected restore of selected database to files on location C:\temp succesfully completed.
avsql Info <42977>: Final summary generated subwork 1, cancelled/aborted 0, promoted to full 0, snapview 1, exitcode 0
avsql Info <6451>: avsql returning with exitcode 0
Now, as I’d mentioned in the GUI version of the recovery, the stream files you recover are effectively SQL Server .bak files, you just need to rename them. I wanted to actually show the import into SQL Server Management Studio, so the next step was to rename the files:
Right! With that done, time to go into SSMS and execute a database recovery. (Don’t forget, we could have recovered directly to a database, but in this case we’re working on the basis of say, the database files being recovered and shipped off to another server that’s not immediately accessible to the environment, or any other purpose the DBAs want.)
When we start a database restore from previously recovered flat files, we’ll change the source to Device so we can add files:
By clicking the “…” option, I could choose the files I wanted to recover from:
Clicking OK showed the chosen files:
The next step was to set the name of the database I wanted to create via the recovery:
SSMS gives you a progress as it’s recovering from those flat files:
And finally, our recovered database:
And that’s it – recovering SQL databases into flat files out of Avamar, from the GUI and from the command line – and even importing the recovered flat files into SQL Server Management Studio.