How to restore a SQL backup to .BAK via the command line if the source SQL Server system / client node is down (DataCenter 8.x). The SQL database after being restored to .BAK can be restored easily using SQL Management Studio (SSMS) on a new or restored SQL Server system that matches the same major and minor version of SQL Server that performed the backup originally. Note: The NovaBACKUP Granular Restore app can also be utilized with the SQL backup to .BAK file, to perform a granular restore of one table for instance to a live SQL Server. If the source SQL Server system client node is down you will not be able to restore the SQL backups via the Restore GUI area, as the node needs to be up and listening in order to even list the SQL backups that node was involved in. Without the SQL Server node being up and listening you will need to use an alternate method of restore which involves doing a pipe restore via the command line hiback.exe app. In that case you will need to obtain the information about the following:
- Backip id ( = timestamp)
- Backup device ( = device string)
You can gather this information from the Hiback\log\account.log on the backup server, and if you have access to the Restore > MS SQL area of the DataCenter Management Console (Java GUI) then you can use that in combination with the account.log file to gather the needed information. In most cases that folder, due to it being the default installation folder, is 'C:\Program Files\NovaStor\DataCenter\Hiback\log\account.log'. But keep in mind DataCenter allows you to custom install on any drive and path, so you will have to know what that path is. You can load regedit on the backup server node in question, in this case ours is the Command Server 8.x node, and it will tell you what the DataCenter program path is, as seen here:
The Command Server 8.x node in this example is our backup server node where the SQL backups stored to, but that is not always the case so you will need to first know what backup server node your SQL backups stored to in order to continue here. Note: The registry key stated as "NovaStor DataCenter CmdSrv" will look like "NovaStor DataCenter Client" if your backup server is not on a Command Server node. Once the backup server node where your SQL backups utilized is identified, which can be seen in the Storage Management area of the GUI, by looking at the Disk Pool Details for your SQL disk pool, or looking at your Tape Library Management > Pools tab, then you can know what folder to look for the account.log file that was mentioned above, to track down the backup id, which is the timestamp, and the backup device, which is the device string, that is required here to perform the command line restore of SQL database to .BAK.
Search for the name or the IP address of the backup client (the SQL Server that the SQL backups came from).
The last lines contain the latest backups.
Look for the backup job name in there.
Find the correct DB full backup of that DB in that file.
You will see the timestamp in the right hand part of the respective line.
That is the backup timestamp you will use in te command line restore.
Somewhere inside the line you will see the backup device and if ist is a disc backup, you will see the savefilename.
If it is a tape mediapool backup, you will see the barcode of the tape. It is important to determine whether the tape is inside the Tape library.
You can use it to obtain the tape sequence number within the media pool which is important address the tape directly.
Now you have the information for the commandline restore on any system, like the backup server itself or the new SQL Server.
Start Command Prompt (cmd.exe) as Admin on the Command Server system.
Change directory to Hiback directory, the path was noted above.
Start
Hiback.exe -cross *
Type
$stamp=TheTimeStampYouDeterminedForThatBackup (this might as well be %savefilename if it is a disk mediapool backup)
Co,pip,wai (this will be a pipe restore)
Restore
„The device string with a $ sign in front“
“C:\program files\novastor\datacenter\hiback\tools\pipedump.exe“ -file:C:\restore\dbname_full.bak -skip:1 -func:ur
N (this must be keep old files question)
You will obtain C:\restore\dbname_full.bak as the restore of the SQL DB in BAK format, which can be used by SQL Server restore itself.
How-to instruction steps and parameters to feed to the hiback.exe:
1. Identify the Command Server 8.x system.
2. Start an Administrator Command Prompt on that Command Server 8.x system.
3. Run the following commands in order:
Microsoft Windows [Version 10.0.17763.2061]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Windows\system32>cd\program files\novastor\datacenter\hiback
C:\Program Files\NovaStor\DataCenter\Hiback>hiback.exe -cross *
╔═════════════════════════════════════════════════════════════════════════════╗
║ ║
║ HIBACK/NT ║
║ ║
║ (C) 1990-2020 NovaStor GmbH ║
║ ║
╚═════════════════════════════════════════════════════════════════════════════╝
HIBACK/NT ( 09.08.02.0674 ) (C) 1990-2020 NovaStor GmbH 07/19/2021 13:38:48
Uptime 4 days 10:46:59 (384419s)
Hiback ( 9.8.2 ) Errorlog started : 19.07.2021 13:38:48
Options, cross systems=*, cross systems=*
using utf8-encoding for input/configuration/files
Sysinfo: W2012/DC-R2 (6.3.9600 SP 0)
Running on HLMN_PRIMARYDC as Administrator
Privileges: backup, restore, take ownership, admin,
SystemID: HLMN_PRI / HLMN_PRIMARYDC
Homedir : C:\Program Files\NovaStor\DataCenter\Hiback
Workdir : C:\Program Files\NovaStor\DataCenter\Hiback
Condev : C:\Program Files\NovaStor\DataCenter\Hiback\CONDEV
Vssdir : C:\Program Files\NovaStor\DataCenter\Hiback\VSS
VssSnap : C:\Program Files\NovaStor\DataCenter\Hiback\VSS\snap
VssSyst : C:\Program Files\NovaStor\DataCenter\Hiback\VSS\systemstate
Type HE or HELP for HELP!
Which Fileset: $stamp=001626309080
Function . . : Co,pip,wai
-------------------- STORE options --------------------
(*) SEA: Searching (SEA=S/X/M) : eXtended
( ) BPS: Bypass Search
(*) LIS: Filelisting (LIS=C/S/L) : short Listing
(*) WAI: Wait for device (*) DAT: Filedate :
( ) INC: Incremental ( ) SHA: Sharing
(*) COM: Compression=LZ4 (*) DON: exclude Dontstor-content
( ) SPL: Split job 0 ( ) BWL: Bandwidth-limit KiB/s : NONE
( ) CLA: Clear Arcbit ( ) CDI: complete directory incremental
( ) ERRLIM: Limit file open errors to 0 ( ) ERRDIE: abort if error limit is reac
hed
Threads: 4
Buffers: maximum: 536870912, file: 8388608, read: 2097152
-------------------- RESTORE options --------------------
( ) FIL: Restore files only ( ) ARO ACL restore only
( ) HID: Reset hidden flag ( ) RESTORE_FALLBACK: Fallback (predecessor) mode
( ) DST: Destination : NONE
( ) SRC: Source : NONE
( ) CRO: Cross-system : NONE
( ) NOD: Nodename : HLMN_PRIMARYDC
Threads: 4
Buffers: maximum: 536870912
-------------------- MISCELLANEOUS options --------------------
( ) OUT: Filename : NONE
( ) VOL: Volumename : NONE
( ) TPD: Tapedirectory : NONE
( ) CRP: Encryption : key.bin
( ) AES: AES Encryption: 0
( ) DBA: Databaseagent : NONE
( ) DBI: DBA-meta : NONE:NONE
( ) DBS: DBA-source : NONE
( ) DBD: DBA-dest : NONE
( ) DBO: DBA-opts : NONE
( ) ACO: DBA-agent-opts: NONE
( ) REG: Registry/AD : C:\Windows\REPAIR
( ) SYS: Systemstate : C:\Program Files\NovaStor\DataCenter\Hiback\VSS\systems
tate
( ) VSP: VSS Provider : automatic
(*) MNT: Mountpoint skip ( ) VSS: (K/D) Volumeshadow:NONE
( ) NAM: Namespace (*) PIP: (C/U/R) data piped
(*) ACL: Access control ( ) RAW: raw device
( ) ULA: upd last access ( ) OSP: ignore offline-bit
( ) VSV: Verify restore ( ) CKS: Checksum
( ) DEF: Defaults ( ) WRN: Warnings
(-) INF: Show options ( ) DBG: Debug=0
------------------------------------------------------------
Function . . : restore
Device . . . : $hlmn_primarydc*disc|rdx.0010
backupserver supports Savefiles > 2/4Tb / 1MB blocks / DiscSV-tpd
support for 64bit offsets: yes
ready for PIP/RAW backup/restore
Savefile G9.8.2 from node/server WKC1Server/WKC1SERVER!
Encrypted Savefile, option CRP activated!
Found 1 matches!
----------------------------------------
Disc : 21071426.SV
Savefile : DISC01 (1)
Storedate : 21/07/14
Stamp : 001626309080
Version : HIBACK/NT G9.8.2
Fileset : 3f83be6a-7eb4-4a26-96cc-34a2c8e1040c.DATA_WKC1SERVER_COMPLIANCEONE_
WKFS_RiskIDOFACCheck.dba
Nodename : WKC1Server
Server/User: WKC1SERVER/Administrator
Backuptype : DBA
----------------------------------------
Found 1 Savefile(s)
pipe restore command: "C:\program files\novastor\datacenter\hiback\tools\pipedum
p.exe" -file:C:\restore\WKFS_RiskIDOFACCheck.bak -skip:1 -func:ur
keep old files (y/n): N
using (1Mb) Bigblocks!
starting pipe restore . .
Timeout 300s for pipe \\.\PIPE\HIPIPE
creating \\.\PIPE\HIPIPE
waiting for client \\.\PIPE\HIPIPE .. connected
writing . . .
finished writing
Pipe restore done, total 3297 KiB
close \\.\PIPE\HIPIPE
-------------------- Show file --------------------
-> C:\Program Files\NovaStor\DataCenter\Hiback\tmp\PIP0.LOG
C:\Program Files\NovaStor\DataCenter\Hiback>"C:\program files\novastor\datacente
r\hiback\tools\pipedump.exe" -file:C:\restore\WKFS_RiskIDOFACCheck.bak -skip:1 -
func:ur
Pipedump utility 1.06 (C) 2020 Novastor GmbH, Hamburg!
using pipe: \\.\pipe\HIPIPE
Reading from \\.\pipe\HIPIPE
Writing to C:\restore\WKFS_RiskIDOFACCheck.bak
skipping 1 Kb
total read 3376128
Elapsed 17s
Done!
-------------------- EOF --------------------
------------------------------------------------------------
Files restored: 1
KiByte read : 1025
KiByte write : 3297
Time elapsed : 00:00:17
------------------------------------------------------------
Which Fileset:
4. When the first SQL backup to .bak restore completes successfully it will show the "Done!" and EOF statements as seen in the example above, where a single SQL database was restored to .bak successfully, you will be returned back to a "Which Fileset" prompt. It is asking you to perform more operations, which unless you have more than one SQL database to restore you won't be concerned to do, and at that point you can press "CTRL-C" on your keyboard to break out of the hiback.exe command that is running and you will be returned to the Admin Command Prompt, and then you can exit out of that Command Prompt. If however you need to restore more than one SQL database, then you will need to press "CTRL-C" to break out of the hiback.exe command and then press the up arrow on your keyboard to run the same "hiback.exe -cross *" command again and then provide all of the same parameters to the command again when prompted at each question as seen in the example output above, and repeat that "CTRL-C" and run the app again to get all databases restored to .bak. After all of that you can take a look at folder you told the command to utilize for where to restore the .bak file(s) to, in our example that was the 'C:\restore\' folder, where those .bak files were told to store to, to reference the size of the database in the backup log, to the size of the .bak file, it will not be exact but close. You will need to repeat the above operation again per each SQL database that needs to be restored, which will require you to CTRL-C break out at this point and re-run the hiback command again to process the next database. Note: If you have additional SQL databases to restore, you will need to break out of this hiback command to be able to process any additional databases. To cancel the hiback command, press CTRL-C to break out of it, after the Done and EOF is received when done processing one database, where it asks for Which Fileset: again, otherwise the original commands won't work to process the next database in the order to restore to .bak.
5. You can then utilize SQL Management Studio (SSMS) on the new or restored SQL Server, which has to very closely match the SQL Server that originally performed the backups, to Restore the .BAK files as databases on that SQL Server.