Forum Discussion
Jonas_B2235
Mar 09, 2021Copper Contributor
Suddenly unable to backup a new database, os path error 3
Hi, I have two MS SQL Server 2016 Std Service Pack 2 instance running on two outsourced OS machine. One Windows Server 2016 and one Windows Server 2012 R2. Then I recently added a new database and mo...
Jonas_B2235
Mar 10, 2021Copper Contributor
When I look at the history log and choose to View T-SQL done by MS SQL Mgmt Studio then I found a lot of errors in it. When I manually fix that code the backup are working ...
The MS SQL Mgmt Studio version is 18.6.
Here is the automatically created WRONG/Not working code (extra space and dots):
EXECUTE master.dbo.xp_create_subdir N''F:\DBBackup\DW_ARKIV ''
GO
BACKUP DATABASE [DW_ARKIV ] TO DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH NOFORMAT, NOINIT, NAME = N''DW_ARKIV _backup_2021_03_10_134006_2704819'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''DW_ARKIV '' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''DW_ARKIV '' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''DW_ARKIV '''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
_____________
and here are the simple code I test/corrected to and that works
EXECUTE master.dbo.xp_create_subdir N'F:\DBBackup\DW_ARKIV'
GO
BACKUP DATABASE [DW_ARKIV ] TO DISK = N'F:\DBBackup\DW_ARKIV\DW_ARKIV_backup_2021_03_10_134006_2704819.bak' WITH NOFORMAT, INIT, NAME = N'DW_ARKIV_backup_2021_03_10_134006_2704819', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
Is it a bug in the Studio? I know there are newer ones and I have to try them out.
The MS SQL Mgmt Studio version is 18.6.
Here is the automatically created WRONG/Not working code (extra space and dots):
EXECUTE master.dbo.xp_create_subdir N''F:\DBBackup\DW_ARKIV ''
GO
BACKUP DATABASE [DW_ARKIV ] TO DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH NOFORMAT, NOINIT, NAME = N''DW_ARKIV _backup_2021_03_10_134006_2704819'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''DW_ARKIV '' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''DW_ARKIV '' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''DW_ARKIV '''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''F:\DBBackup\DW_ARKIV \DW_ARKIV _backup_2021_03_10_134006_2704819.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
_____________
and here are the simple code I test/corrected to and that works
EXECUTE master.dbo.xp_create_subdir N'F:\DBBackup\DW_ARKIV'
GO
BACKUP DATABASE [DW_ARKIV ] TO DISK = N'F:\DBBackup\DW_ARKIV\DW_ARKIV_backup_2021_03_10_134006_2704819.bak' WITH NOFORMAT, INIT, NAME = N'DW_ARKIV_backup_2021_03_10_134006_2704819', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
Is it a bug in the Studio? I know there are newer ones and I have to try them out.
- Jonas_B2235Mar 10, 2021Copper Contributor18.8, the latest MS SQL Mgmt Studio did not work either. It was the 18.6 which create the database-name with an extra space on the end. Now you think I'm crazy and did it my self ... oh yes but then that I should do this exact same misstake on two different instances which I do not believe.
I have been working with MS SQL Mgmt Studio for over 20 years and never had this kind of bug, which I beleive it must be.
Case closed.- Jonas_B2235Mar 10, 2021Copper ContributorNothing new ... find this afterwards
https://www.sqlservercentral.com/forums/topic/is-this-a-bug-in-sql-server-a-trailing-space-in-a-database-name-backups-fail
and this
https://www.sqlservercentral.com/forums/topic/space-given-in-db-name-and-backup-failure