Why NTBackup write some info to MSDB?

Perhaps if you stop the SQL Server VSS Writer service?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"anxcomp" wrote in message
news:245A3C9F-2EF1-4608-9717-117DB81936CA@microsoft.com...
> Hello,
>
> I use NTBackup (Windows 2003) to backup my SQL 2005 servers. I backup all
> files (disk C, D, E ....) + system state to one file .bkf (no to tape). I
> excluded from backup all *.mdf files and *.ldf files.
>
> Unfortunately I noticed strange behaviour, when I tried restore backup from
> GUI, I got error:
>
> ------
> System.Data.SqlClient.SqlError: Cannot open backup device
> '{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'.
> Operating system error 2(error not found). (Microsoft.SqlServer.Smo)
>
>
> restore script:
>
> RESTORE DATABASE [db1]
> FROM TAPE = N'{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'
> WITH FILE = 1, NOUNLOAD, STATS = 10
> GO
>
> additional info from msdb:
>
> device_type:
> 7
> -----
>
> Only one way to restore database is restore from TSQL.
>
> Why NTBackup writes this information to MSDB database?
>
> Could somebody tell me how tell NTBackup don't log any info to MSDB?
>
> Thanks in advance.
>
> --
> Regards,
> anxcomp

"anxcomp" wrote:

> Hello,
>
> I use NTBackup (Windows 2003) to backup my SQL 2005 servers. I backup all
> files (disk C, D, E ....) + system state to one file .bkf (no to tape). I
> excluded from backup all *.mdf files and *.ldf files.
>
> Unfortunately I noticed strange behaviour, when I tried restore backup from
> GUI, I got error:
>
> ------
> System.Data.SqlClient.SqlError: Cannot open backup device
> '{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'.
> Operating system error 2(error not found). (Microsoft.SqlServer.Smo)
>
>
> restore script:
>
> RESTORE DATABASE [db1]
> FROM TAPE = N'{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'
> WITH FILE = 1, NOUNLOAD, STATS = 10
> GO
>
> additional info from msdb:
>
> device_type:
> 7
> -----
>
> Only one way to restore database is restore from TSQL.
>
> Why NTBackup writes this information to MSDB database?
>
> Could somebody tell me how tell NTBackup don't log any info to MSDB?
>
> Thanks in advance.
>
> --
> Regards,
> anxcomp

Hi

Are you saying that you are trying to restore why the NTBackup is in
progress? In which case the file system files associated with the device you
are restoring from are probably locked by the backup and therefore they can
not be used during this period to restore from. Once the files have been
backed up it should not lock the file and you can then restore the database
from them. Locking files whilst they are being backed is common for backup
programs and not specific to NTBackup. NTBackup itself is not writing to MSDB.

John

I am unclear on what the actual steps are that were taken. Here is
what it sounds like to me.

1) NTBackup to save all files EXCEPT the database files. Backup made
to a file, not to a tape.

2) SQL Server RESTORE DATABASE against something with a really long
strange name is failing because it can not open whatever it is.

As far as I know the only file SQL Server's RESTORE command can read
is one written to by the SQL Server BACKUP command. You do not
mention ever running the SQL Server BACKUP command.

Perhaps you can describe the problem again.

Roy Harvey
Beacon Falls, CT

On Mon, 28 Apr 2008 13:59:01 -0700, anxcomp
wrote:

>Hello,
>
>I use NTBackup (Windows 2003) to backup my SQL 2005 servers. I backup all
>files (disk C, D, E ....) + system state to one file .bkf (no to tape). I
>excluded from backup all *.mdf files and *.ldf files.
>
>Unfortunately I noticed strange behaviour, when I tried restore backup from
>GUI, I got error:
>
>------
>System.Data.SqlClient.SqlError: Cannot open backup device
>'{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'.
>Operating system error 2(error not found). (Microsoft.SqlServer.Smo)
>
>
>restore script:
>
>RESTORE DATABASE [db1]
>FROM TAPE = N'{A870BE5A-247E-4C35-858A-72AB85D0D7FE}9'
>WITH FILE = 1, NOUNLOAD, STATS = 10
>GO
>
>additional info from msdb:
>
>device_type:
>7
>-----
>
>Only one way to restore database is restore from TSQL.
>
>Why NTBackup writes this information to MSDB database?
>
>Could somebody tell me how tell NTBackup don't log any info to MSDB?
>
>Thanks in advance.

Hello,

Thanks All, but Tibor perfect!!!

When I stopped VSS Writer Service and problem gone :-)

>1) NTBackup to save all files EXCEPT the database files. Backup made
>to a file, not to a tape.

Exactly, all files except .mdf and .ldf

>2) SQL Server RESTORE DATABASE against something with a really long
>strange name is failing because it can not open whatever it is.

Yes, exactly.

I show you simplest scenario, I assuming that VSS Writer Service is working:

------------
create database db1
go

backup database db1
to disk = 'c:\temp\db1.bak'

use msdb
go

select media_set_id, physical_device_name
from backupmediafamily
order by media_set_id desc


media_set_id physical_device_name
------------ ---------------------
127 c:\temp\db1.bak

!!! run ntbackup (systemstate + all SQl Server files EXCEPT files *.mdf and
*ldf + what you want....)

select media_set_id, physical_device_name
from backupmediafamily
order by media_set_id desc

media_set_id physical_device_name
------------ -----------------------------------------
140 {9D60D745-97E2-477A-A436-DD6DD142C816}12
139 {9D60D745-97E2-477A-A436-DD6DD142C816}9
138 {9D60D745-97E2-477A-A436-DD6DD142C816}8
137 {9D60D745-97E2-477A-A436-DD6DD142C816}11
136 {9D60D745-97E2-477A-A436-DD6DD142C816}6
135 {9D60D745-97E2-477A-A436-DD6DD142C816}4
134 {9D60D745-97E2-477A-A436-DD6DD142C816}1
133 {9D60D745-97E2-477A-A436-DD6DD142C816}5
132 {9D60D745-97E2-477A-A436-DD6DD142C816}7
131 {9D60D745-97E2-477A-A436-DD6DD142C816}10
130 {9D60D745-97E2-477A-A436-DD6DD142C816}2
129 {9D60D745-97E2-477A-A436-DD6DD142C816}3
128 {9D60D745-97E2-477A-A436-DD6DD142C816}13
127 c:\temp\db1.bak


When you'll tray restore SQl give you error:

Restore failed for Server 'srv'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError:
Cannot open backup device '{9D60D745-97E2-477A-A436-DD6DD142C816}12'.
Operating system error 2(error not found). (Microsoft.SqlServer.Smo)

Restore Script:
RESTORE DATABASE [db1] FROM
TAPE = N'{9D60D745-97E2-477A-A436-DD6DD142C816}12'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO
------------

When we stop VSS Writer , NTbackup won't log this strange records do MSDB.

Thanks Tibor , but what I lose from DBA perspective when I stop VSS Writer?

--
Regards,
anxcomp

NT Backup apparently interacts with SQL Server through this service and tell SQL Server that it is
performing a backup. This is a snapshot backup, and SQL Server considers it as a database backup.
I've never used this, so I can't say how you would restore etc using such a backup. Stopping the SQL
VSS service stops this interaction so that NT backup and your traditional SQL Server backups do not
"step on each others toes".

What you lose *is* the ability for NT Backup to include your databases in its snapshot backup. And,
btw, now that you stopped the service, you probably want to exclude the SQL Server database files
from your NT Backup.

I suggest you search Books Online for "VDI", which should lead to you more info...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"anxcomp" wrote in message
news:3430A7AE-0E38-46F3-81CC-4D98EFEA24BD@microsoft.com...
> Hello,
>
> Thanks All, but Tibor perfect!!!
>
> When I stopped VSS Writer Service and problem gone :-)
>
>>1) NTBackup to save all files EXCEPT the database files. Backup made
>>to a file, not to a tape.
>
> Exactly, all files except .mdf and .ldf
>
>>2) SQL Server RESTORE DATABASE against something with a really long
>>strange name is failing because it can not open whatever it is.
>
> Yes, exactly.
>
> I show you simplest scenario, I assuming that VSS Writer Service is working:
>
> ------------
> create database db1
> go
>
> backup database db1
> to disk = 'c:\temp\db1.bak'
>
> use msdb
> go
>
> select media_set_id, physical_device_name
> from backupmediafamily
> order by media_set_id desc
>
>
> media_set_id physical_device_name
> ------------ ---------------------
> 127 c:\temp\db1.bak
>
> !!! run ntbackup (systemstate + all SQl Server files EXCEPT files *.mdf and
> *ldf + what you want....)
>
> select media_set_id, physical_device_name
> from backupmediafamily
> order by media_set_id desc
>
> media_set_id physical_device_name
> ------------ -----------------------------------------
> 140 {9D60D745-97E2-477A-A436-DD6DD142C816}12
> 139 {9D60D745-97E2-477A-A436-DD6DD142C816}9
> 138 {9D60D745-97E2-477A-A436-DD6DD142C816}8
> 137 {9D60D745-97E2-477A-A436-DD6DD142C816}11
> 136 {9D60D745-97E2-477A-A436-DD6DD142C816}6
> 135 {9D60D745-97E2-477A-A436-DD6DD142C816}4
> 134 {9D60D745-97E2-477A-A436-DD6DD142C816}1
> 133 {9D60D745-97E2-477A-A436-DD6DD142C816}5
> 132 {9D60D745-97E2-477A-A436-DD6DD142C816}7
> 131 {9D60D745-97E2-477A-A436-DD6DD142C816}10
> 130 {9D60D745-97E2-477A-A436-DD6DD142C816}2
> 129 {9D60D745-97E2-477A-A436-DD6DD142C816}3
> 128 {9D60D745-97E2-477A-A436-DD6DD142C816}13
> 127 c:\temp\db1.bak
>
>
> When you'll tray restore SQl give you error:
>
> Restore failed for Server 'srv'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError:
> Cannot open backup device '{9D60D745-97E2-477A-A436-DD6DD142C816}12'.
> Operating system error 2(error not found). (Microsoft.SqlServer.Smo)
>
> Restore Script:
> RESTORE DATABASE [db1] FROM
> TAPE = N'{9D60D745-97E2-477A-A436-DD6DD142C816}12'
> WITH FILE = 1, NOUNLOAD, STATS = 10
> GO
> ------------
>
> When we stop VSS Writer , NTbackup won't log this strange records do MSDB.
>
> Thanks Tibor , but what I lose from DBA perspective when I stop VSS Writer?
>
> --
> Regards,
> anxcomp

Hi,

Thanks for answer and explanation. I don't backup my databases with
NTBackup. I use NTBackup to backup only my operating systems and always
exclude database files and logs (mdf, ldf) as a mentioned above. For
databases I use native mechanism included in SQL Server (BACKUP DATABASE....)

ps.
I think "NTBackup and SQL Server" it would be good topic on your blog ;-)

--
Regards,
anxcomp

Hello,

I am also having this problem.

SQL Server Management Studio is telling me that it cannot locate the
backup: iMedia_Corp_00__3552af94_39b5_453a_b590_5aa0d7fc0f2f_

WHen I query the family sets, this one is indeed present.

However the following script to restore is NOT working for me:

RESTORE DATABASE [db1] FROM
TAPE = N'iMedia_Corp_00__3552af94_39b5_453a_b590_5aa0d7fc0f2f_'
WITH FILE = 1, NOUNLOAD, STATS = 10
GO

Please help!!

thanks,

Saleek

On Apr 30, 7:24*am, "Tibor Karaszi"
wrote:
> NT Backup apparently interacts with SQL Server through this service and tell SQL Server that it is
> performing a backup. This is a snapshot backup, and SQL Server considers it as a database backup.
> I've never used this, so I can't say how you would restore etc using such a backup. Stopping the SQL
> VSS service stops this interaction so that NT backup and your traditional SQL Server backups do not
> "step on each others toes".
>
> What you lose *is* the ability for NT Backup to include your databases in its snapshot backup. And,
> btw, now that you stopped the service, you probably want to exclude the SQL Server database files
> from your NT Backup.
>
> I suggest you search Books Online for "VDI", which should lead to you moreinfo...
>
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> "anxcomp" wrote in message
>
> news:3430A7AE-0E38-46F3-81CC-4D98EFEA24BD@microsoft.com...
>
>
>
> > Hello,
>
> > Thanks All, but Tibor perfect!!!
>
> > When I stopped *VSS Writer Service and problem gone :-)
>
> >>1) NTBackup to save all files EXCEPT the database files. Backup made
> >>to a file, not to a tape.
>
> > Exactly, all files except .mdf and .ldf
>
> >>2) SQL Server RESTORE DATABASE against something with a really long
> >>strange name is failing because it can not open whatever it is.
>
> > Yes, exactly.
>
> > I show you simplest scenario, I assuming that VSS Writer Service is working:
>
> > ------------
> > create database db1
> > go
>
> > backup database db1
> > to disk = 'c:\temp\db1.bak'
>
> > use msdb
> > go
>
> > select media_set_id, physical_device_name
> > from backupmediafamily
> > order by media_set_id desc
>
> > media_set_id physical_device_name
> > ------------ ---------------------
> > 127 * * * * *c:\temp\db1.bak
>
> > !!! run ntbackup (systemstate + all SQl Server files EXCEPT files *.mdf and
> > *ldf + what you want....)
>
> > select media_set_id, physical_device_name
> > from backupmediafamily
> > order by media_set_id desc
>
> > media_set_id physical_device_name
> > ------------ -----------------------------------------
> > 140 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}12
> > 139 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}9
> > 138 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}8
> > 137 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}11
> > 136 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}6
> > 135 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}4
> > 134 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}1
> > 133 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}5
> > 132 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}7
> > 131 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}10
> > 130 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}2
> > 129 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}3
> > 128 * * * * *{9D60D745-97E2-477A-A436-DD6DD142C816}13
> > 127 * * * * *c:\temp\db1.bak
>
> > When you'll tray restore SQl give you error:
>
> > Restore failed for Server 'srv'. *(Microsoft.SqlServer.Smo)
> > System.Data.SqlClient.SqlError:
> > Cannot open backup device '{9D60D745-97E2-477A-A436-DD6DD142C816}12'.
> > Operating system error 2(error not found). (Microsoft.SqlServer.Smo)
>
> > Restore Script:
> > RESTORE DATABASE [db1] FROM
> > TAPE = N'{9D60D745-97E2-477A-A436-DD6DD142C816}12'
> > WITH *FILE = 1, *NOUNLOAD, *STATS = 10
> > GO
> > ------------
>
> > When we stop VSS Writer , NTbackup *won't *log this strange *records do MSDB.
>
> > Thanks Tibor , but what I *lose from DBA perspective when I stop VSS Writer?
>
> > --
> > Regards,
> > anxcomp- Hide quoted text -
>
> - Show quoted text -