SSMS's GUI and dbatools produce different scripts for restoring databases in full recovery mode, what practical differences do they have?
0
votes
1
answer
105
views
I have a database in full recovery mode without log backups. Don't say it: _I know_. It fortunately isn't in an environment that I care about.
Today, disaster struck and I tried to use dbatools to generate a script to restore it. At the time, I failed. I therefore turned to SSMS's GUI and walked away with this:
BACKUP LOG [MyDb] TO DISK = N'D:\SQL\Backups\MyDb_LogBackup_Timestamp.bak'
WITH NOFORMAT, NOINIT, NAME = N'D:\SQL\Backups\MyDb_LogBackup_Timestamp.bak',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 5
RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_yesterday_timestamp.bak'
WITH FILE = 1, NORECOVERY , NOUNLOAD, STATS = 5
RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_this_morning_timestamp.dif'
WITH FILE = 1, NOUNLOAD, STATS = 5
This worked great. While waiting on the restore, I got dbatools to work
Get-DbaBackupInformation -SqlInstance 'MyBox' -Path 'X:\Backups\Full\' -DirectoryRecruse -DatabaseName 'MyDb' |
Restore-DbaDatabase -OutputScriptOnly -SqlInstance 'MyBox' -WithReplace
and it produced this
RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_yesterday_timestamp.bak'
WITH FILE = 1,
MOVE N'MyDb' TO N'D:\SQL\Data\MyDb_1_.mdf',
MOVE N'MyDb_log' TO N'D:\SQL\Logs\MyDb_log1_.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [MyDb] FROM DISK = N'X:\Backups\Full\MyDb_backup_this_morning_timestamp.dif'
WITH FILE = 1,
MOVE N'MyDb' TO N'D:\SQL\Data\MyDb_1_.mdf',
MOVE N'MyDb_log' TO N'D:\SQL\Logs\MyDb_log1_.ldf',
NOUNLOAD, REPLACE, STATS = 10
From this, it is clear that dbatools and SSMS's GUI give very different scripts for completing an identical task. **In terms of the final result produced by running both, how do they differ?** As far as I can tell, STATS = 10
is the only part that gives a meaningful difference.
These options are largely explained in [the](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-ver16) [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver16) , but there is so much information in the RESTORE DATABASE
docs that I am sure to have overlooked something important. Also, you can frankly never be too careful when it comes to backups. It also surprises me that SSMS generated an entire extra command that dbatools did not and that dbatools added all of the MOVE
stuff.
Asked by J. Mini
(1237 rep)
Sep 30, 2024, 10:49 PM
Last activity: Oct 1, 2024, 08:11 PM
Last activity: Oct 1, 2024, 08:11 PM