Error in SQL Server Enterprise Manager

Hi Frndz,

I am using SQL Server 7.0, I will regularly take backup daily using sql

server enterprise manager. During backup running, i had cancelled

abnormally, when next time i take backup it shows the following error
"Microsoft SQL-DMO(ODBC SQLState:42000)"

"Backup,

CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as

CREATE FILE) operations on A database must be serialized. Reissue the

statement after the current backup. CHECKALLOC or file manipulation

operation is completed.
Backup or restore operation terminating abnormally"


How to solve this problem? send reply in detail.

With Regards,
Arul

Wrong forum. Try the disaster recovery forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=744&SiteID=1sql

Error in SQL 2005 Maintenance Plan

I'm trying to setup a nightly backup to a remote computer using an SQL
Server 2005 Maintenance Plan. It keeps giving an error. I've narrowed
the cause down to an execute command...
EXECUTE master.dbo.xp_create_subdir
N'\\\\10.2.32.15\\Backup_SQL2005\\\\IOC'
which is giving the error below...
Msg 22048, Level 16, State 1, Line 0
xp_create_subdir() returned error 123, 'The filename, directory name,
or volume label syntax is incorrect.'
I am signed on as administrator on both computers. I'm able to map a
drive though the Windows Explorer and create a subdirectory on the
remote machine.
I have also tried...
EXECUTE master.dbo.xp_create_subdir N'\\10.2.32.15\Backup_SQL2005\IOC'
EXECUTE master.dbo.xp_create_subdir N'\\GOMB-BACKUP\Backup_SQL2005\IOC'
EXECUTE master.dbo.xp_create_subdir N'G:\IOC' (after mapping a drive)
This works...
EXECUTE master.dbo.xp_create_subdir N'C:\IOC'
...but of course it's on the local computer.
Any ideas why this doesn't work?
Thanks,
CThe stuff you do in Explorer uses the Windows account *you* have logged in w
ith. The stuff that SQL
Server does uses the Service account for the SQL Server service. Verify that
the service account has
proper permissions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1140218704.513963.300930@.g44g2000cwa.googlegroups.com...
> I'm trying to setup a nightly backup to a remote computer using an SQL
> Server 2005 Maintenance Plan. It keeps giving an error. I've narrowed
> the cause down to an execute command...
> EXECUTE master.dbo.xp_create_subdir
> N'\\\\10.2.32.15\\Backup_SQL2005\\\\IOC'
> which is giving the error below...
> Msg 22048, Level 16, State 1, Line 0
> xp_create_subdir() returned error 123, 'The filename, directory name,
> or volume label syntax is incorrect.'
> I am signed on as administrator on both computers. I'm able to map a
> drive though the Windows Explorer and create a subdirectory on the
> remote machine.
> I have also tried...
> EXECUTE master.dbo.xp_create_subdir N'\\10.2.32.15\Backup_SQL2005\IOC'
> EXECUTE master.dbo.xp_create_subdir N'\\GOMB-BACKUP\Backup_SQL2005\IOC'
> EXECUTE master.dbo.xp_create_subdir N'G:\IOC' (after mapping a drive)
> This works...
> EXECUTE master.dbo.xp_create_subdir N'C:\IOC'
> ...but of course it's on the local computer.
> Any ideas why this doesn't work?
> Thanks,
> C
>|||Tibor, thanks for the replay.
How do I give the Service account access to create remote
subdirectories?
C|||The Cornjerker (addoty@.gmail.com) writes:
> Tibor, thanks for the replay.
> How do I give the Service account access to create remote
> subdirectories?
As you would to for any other Windows user. (Which if you don't know is
a question for a Windows newsgroup. I hardly know it myself.)
Note howver, that you cannot do this if SQL Server is running under
Local System. In this case you need to change which account under which
SQL Server is running. To do this, right-click My Computer, select Manager,
find Services, and there find the SQL Server service. Double-click, and
log-on information is on the Log On tab.
If you are using SQL 2005, you should use the SQL Computer Manager instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Thanks for the replay. Are there any divantages to running the
MSSQLSERVER service under the Administrator account?
C

Error in scheduled back up

I've been pulling my hair over this message in my daily backup log...
I found an article for this error message in knowledge base (274743), and it
has something to do with SMS database. But we do not use SMS. So, I don't
know how to solve this problem...
This error moves around every day... sometimes Master doesn't get backed up
, sometimes model...
I'm not 100% sure, but maybe it started after I started to run optimization
job...
I'd appreciate any help on this... Thank you!
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3023: [Microsoft]&#
91;ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SEL
ECT INTO, and file manipulation (such as CREATE FILE) operations on a databa
se must be serialized. Reissue the statemen
t after the current backup, CHECKALLOC, or file manipulation operation is co
mpleted.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore
operation terminating abnormally.The error is from some other operation such as those listed,
was running when you executed the backup.
The optimizations option does have some activities that
would prohibit a backup at the same time as when that part
of the plan is run - e.g. the option to remove unused space
just does a dbcc shrinkdatabase and you can't run this and a
backup at the same time.
The best way to solve it is to just write your own jobs to
perform your maintenance. This allows a lot more control,
flexibility with your database maintenance.
-Sue
On Mon, 29 Mar 2004 15:26:08 -0800, "Amy Miller"
<amiller1@.sonomacourt.org> wrote:

>I've been pulling my hair over this message in my daily backup log...
>I found an article for this error message in knowledge base (274743), and i
t has something to do with SMS database. But we do not use SMS. So, I don'
t know how to solve this problem...
>This error moves around every day... sometimes Master doesn't get backed u
p, sometimes model...
>I'm not 100% sure, but maybe it started after I started to run optimization
job...
>I'd appreciate any help on this... Thank you!
>[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3023: [Microsoft][ODBC
SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and f
ile manipulation (such as CREATE FILE) operations on a database must be serialized.
Reissue the stateme
nt after the current backup, CHECKALLOC, or file manipulation operation is completed.[color
=darkred]
>[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operat
ion terminating abnormally.[/color]

Error in restoring SSAS database

Hi,

getting this error on restoring an ssas database .abf file with a different database name.
Backup and restore errors: Restore under different DatabaseId/DatabaseName can not be done for a database with partitions with specific location.
(Microsoft.AnalysisServices)

Program Location:

at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)

The db from which the backup was created had its partition data outside of the cube specific folder
....\Program Files\Microsoft SQL Server\MSSQL.1\OLAP\Data\XYZ.cub
That is the partition data was in the root folder ...\Data in the guid named folders.
Also tried changing the location for the partitions during the restore.

Is this an Analysis Services limitation?

Regards

That is correct.

The current limitation is: you cannot restore database with different name if you have partitions not in default location.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Error in restoring SQL Server 6.5 backup

Hi,
I am receiving an error when I try to do a restore of a backup from a
client. I am getting the following error:
"read on dump dump device 'e:\mssql\backup
\NAMEOFFILE' failed, vsn =1 return=1870835794 status = 2. Please consult
the errro log for more details. When I go to the error log I see the message
"dev_read_extent: Trying to read after end of data"
Can someone please tell me what the error means?
I am running 6.5.416 as my dbserver.
Thanks for your assistance,
KimHi
Google turns up nothing other than the obvious ones of corrupt dump file or
no disc space!
Are you both on the same service pack?
Are your devices the same size (or large enough!)?
John
"kimcmm" <kimcmm@.discussions.microsoft.com> wrote in message
news:2001E620-B617-4221-B921-C74BA0C9AB19@.microsoft.com...
> Hi,
> I am receiving an error when I try to do a restore of a backup from a
> client. I am getting the following error:
> "read on dump dump device 'e:\mssql\backup
> \NAMEOFFILE' failed, vsn =1 return=1870835794 status = 2. Please consult
> the errro log for more details. When I go to the error log I see the
> message
> "dev_read_extent: Trying to read after end of data"
> Can someone please tell me what the error means?
> I am running 6.5.416 as my dbserver.
> Thanks for your assistance,
> Kim|||Yeah, I checked google and I believe that this is a bad backup.My devices
that created the database on are large enough, otherwise I would have gotten
the error when the backup first attempted to run that says I don't have
enough space.
The backup proceeds about half way, then I get the error that I posted. The
database stays in a loading state until I run the DROP DATABASE command.
I will check with the client on the service pack.
"John Bell" wrote:
> Hi
> Google turns up nothing other than the obvious ones of corrupt dump file or
> no disc space!
> Are you both on the same service pack?
> Are your devices the same size (or large enough!)?
> John
> "kimcmm" <kimcmm@.discussions.microsoft.com> wrote in message
> news:2001E620-B617-4221-B921-C74BA0C9AB19@.microsoft.com...
> > Hi,
> > I am receiving an error when I try to do a restore of a backup from a
> > client. I am getting the following error:
> > "read on dump dump device 'e:\mssql\backup
> > \NAMEOFFILE' failed, vsn =1 return=1870835794 status = 2. Please consult
> > the errro log for more details. When I go to the error log I see the
> > message
> > "dev_read_extent: Trying to read after end of data"
> >
> > Can someone please tell me what the error means?
> > I am running 6.5.416 as my dbserver.
> >
> > Thanks for your assistance,
> > Kim
>
>

Error in restoring SQL Server 6.5 backup

Hi,
I am receiving an error when I try to do a restore of a backup from a
client. I am getting the following error:
"read on dump dump device 'e:\mssql\backup
\NAMEOFFILE' failed, vsn =1 return=1870835794 status = 2. Please consult
the errro log for more details. When I go to the error log I see the message
"dev_read_extent: Trying to read after end of data"
Can someone please tell me what the error means?
I am running 6.5.416 as my dbserver.
Thanks for your assistance,
Kim
Hi
Google turns up nothing other than the obvious ones of corrupt dump file or
no disc space!
Are you both on the same service pack?
Are your devices the same size (or large enough!)?
John
"kimcmm" <kimcmm@.discussions.microsoft.com> wrote in message
news:2001E620-B617-4221-B921-C74BA0C9AB19@.microsoft.com...
> Hi,
> I am receiving an error when I try to do a restore of a backup from a
> client. I am getting the following error:
> "read on dump dump device 'e:\mssql\backup
> \NAMEOFFILE' failed, vsn =1 return=1870835794 status = 2. Please consult
> the errro log for more details. When I go to the error log I see the
> message
> "dev_read_extent: Trying to read after end of data"
> Can someone please tell me what the error means?
> I am running 6.5.416 as my dbserver.
> Thanks for your assistance,
> Kim
|||Yeah, I checked google and I believe that this is a bad backup.My devices
that created the database on are large enough, otherwise I would have gotten
the error when the backup first attempted to run that says I don't have
enough space.
The backup proceeds about half way, then I get the error that I posted. The
database stays in a loading state until I run the DROP DATABASE command.
I will check with the client on the service pack.
"John Bell" wrote:

> Hi
> Google turns up nothing other than the obvious ones of corrupt dump file or
> no disc space!
> Are you both on the same service pack?
> Are your devices the same size (or large enough!)?
> John
> "kimcmm" <kimcmm@.discussions.microsoft.com> wrote in message
> news:2001E620-B617-4221-B921-C74BA0C9AB19@.microsoft.com...
>
>

error in restore script

I have attempted to create a script to do a backup and restore that would be useable for several different database servers. The script works fine on SQL 2000 but on SQL 7 I get the following error.

Server: Msg 3156, Level 16, State 2, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]The file 'C:\temp\test_log.ldf ' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

The piece of code in question is:

EXEC('RESTORE DATABASE '+@.targetdb+'
FROM DISK = '''+@.sourcedb_backupdir+'''
WITH REPLACE, RECOVERY,
MOVE '''+@.source_restore_mdf_name+''' TO '''+@.target_restore_mdf_dir+''',
MOVE '''+@.source_restore_ldf_name+''' TO '''+@.target_restore_ldf_dir+'''')

Any help would be appreciated

The whole script (version SQL 7) is attached if that would help as well.I have gotten this message when I was restoring a dump that was from a different database. I see that you do use the MOVE, have you verified the physical and logical names? Instead of doing the EXEC, how about doing a PRINT, for debugging and see what the command is.|||This is what I get when I use the print statement:


RESTORE DATABASE test
FROM DISK = 'C:\temp\Northwind.bak'
WITH REPLACE, RECOVERY,
MOVE 'Northwind' TO 'c:\temp\test.mdf',
MOVE 'Northwind_log' TO 'C:\temp\test_log.ldf'

I get the following error in Query Analyzer:

Server: Msg 3156, Level 16, State 2, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]The file 'c:\temp\test.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

When I look in the error log I get this error message:

2002-04-04 11:57:16.41 kernel BackupFileDesc::VerifyCreatability: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device C:\temp\test_log.ldf

2002-04-04 13:28:15.00 kernel BackupFileDesc::VerifyCreatability: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\temp\test.mdf. 0

This error is confusing because before I start the restore I kill all users and put the db in single user mode.|||When you run the RESTORE command are you excuting it from MASTER? Is the logical names for database Northwind correct 'Northwind' for database file and 'Northwind_log' for transaction log? Also are the physical files for Test 'c:\temp\test.mdf' for data and 'C:\temp\test_log.ldf' for transaction log.
Just looking at the physical names they look like they would be the default names SQL Server generates <database name>_log.ldf and <database name>_data.mdf . The only thing is that if this is true then your physical name should be 'c:\temp\test_data.mdf'|||I am useing the master db.

If I run sp_helpdb for Northwind I get the following:

Northwind, 1, C:\MSSQL\DATA\northwnd.mdf, PRIMARY, 4864 KB, Unlimited, 10%, data only

Northwind_log, 2, C:\MSSQL7\DATA\northwnd.ldf, NULL, 1024 KB, Unlimited, 10%, log only

If I run sp_helpdb for test I get the following:

Northwind, 1, c:\temp\test.mdf, PRIMARY, 4864 KB, Unlimited, 10%, data only

Northwind_log, 2, C:\temp\test_log.ldf, NULL, 1024 KB, Unlimited, 10%, log only

When I set my variables I use the following:

CREATE TABLE #db_sysfiles
(
name VARCHAR(50),
filename VARCHAR(255),
dbname VARCHAR(50)
)

INSERT INTO #db_sysfiles EXEC ('SELECT a.name, a.filename, b.name FROM '+@.sourcedb+'..sysfiles a, master..sysdatabases b WHERE b.name = '''+@.sourcedb+'''')

INSERT INTO #db_sysfiles EXEC ('SELECT a.name, a.filename, b.name FROM '+@.targetdb+'..sysfiles a, master..sysdatabases b WHERE b.name = '''+@.targetdb+'''')

SELECT @.source_restore_mdf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.sourcedb

SELECT @.source_restore_ldf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename LIKE '%.ldf%'
AND dbname = @.sourcedb

SELECT @.target_restore_mdf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.targetdb

SELECT @.target_restore_ldf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.targetdb

SELECT @.target_restore_mdf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.targetdb

SELECT @.target_restore_ldf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.targetdb

SELECT @.source_restore_mdf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.sourcedb

SELECT @.source_restore_ldf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.sourcedb

Error in backup process.

Hi everybody,

I'm trying to backup a database of 330 GB more or less from my server to an external hard disk connected by optical fiber and I'm receiving the next error:

[SQLSTATE 01000] (Message 3211) Write on "H:\sqlbackup\pm_import" failed: 33(The process cannot access the file because another process has locked a portion of the file.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Any idea?

Thanks!

refer this link,

http://msdn2.microsoft.com/en-us/library/ms837537.aspx is there any other process that is accessing your db just type sp_who2 and see if anything is accessing your db.......any operations going in your db other than backup any bulk insert etc?......

|||Absolutely nothing! I put the batabase in SINGLE_USER mode.

|||

no need to bring to Single user mode . SQL Server has online backup architecture. The problem seems to be the Target file. ie the Backup file in H:\SQLBackup. check any process is using the file when u take backup. It need exclusive access to that file. to debug the issue... create a new database and take backup in the same path ie . H:\sqlbackup\test.bak or something like this. if u r getting same error then the problem may be something else. post back the result of this debug process i mentioned

Madhu

|||

Thank you very much Madhu. I thought the data file was locked by another process, but in fact it was the target file who was block by the anti-virus.

So problem solved!

Thanks again.

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.
Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegrou ps.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>
|||<snip>

> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
--
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||Exactly when is the error occurring?
From the code you posted? If so, which line, and can you use Profiler to catch the actual TSQL
statement executed and try them from a query window?
Or are you trying to attach (sp_attach_db) a database backup file? No can do, a backup file need to
be restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||<snip>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
--
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||Exactly when is the error occurring?
From the code you posted? If so, which line, and can you use Profiler to cat
ch the actual TSQL
statement executed and try them from a query window?
Or are you trying to attach (sp_attach_db) a database backup file? No can do
, a backup file need to
be restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||<snip>

> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.

Error Icons displayed on parent objects with no or different children

I recently had to restore a database from backup and the restored
database had a different set of publications than the one I overwrote.
Since then, in EM, on the Replication Monitor object, Publishers, and
Servername objects as well as on the Agents and Snapshot Agents
objects, I see the little red X denoting an error. I have done
everything i can think of and more, following much advice given on this
group. I even turned replication off, deleted all publications, used
hilary cotter's script to clean up all the miscellaneous replication
tables/settings, etc. and restarted the server.
Everything seems to be working fine, but I would like to try to clean
up these "errors". Does anyone have any suggestions?
I am running SqlServer 8.00.2039 SP4 Standard Edition on Windows Server
2003 SP1
You will have to manually remove the replication jobs in msdb. Sometimes you
will get transitory error messages in EM, run sp_MSload_replication_status
to clear them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"troy" <troyblakely@.gmail.com> wrote in message
news:1125073355.972990.114690@.g47g2000cwa.googlegr oups.com...
> I recently had to restore a database from backup and the restored
> database had a different set of publications than the one I overwrote.
> Since then, in EM, on the Replication Monitor object, Publishers, and
> Servername objects as well as on the Agents and Snapshot Agents
> objects, I see the little red X denoting an error. I have done
> everything i can think of and more, following much advice given on this
> group. I even turned replication off, deleted all publications, used
> hilary cotter's script to clean up all the miscellaneous replication
> tables/settings, etc. and restarted the server.
> Everything seems to be working fine, but I would like to try to clean
> up these "errors". Does anyone have any suggestions?
> I am running SqlServer 8.00.2039 SP4 Standard Edition on Windows Server
> 2003 SP1
>

Error icon on replication monitor and sub items

Hi,

Have two servers, one production server and one backup server. Both are Windows 2003 Server having SQL Server 2000 installed.

I have created replication on these servers using a wizard. I then generated SQL scripts to delete and create the replication. These scripts work well, but to test to remove the replication on both servers I used sp_removedbreplication 'DBNAME' on both servers. Not all were removed and on Replication Monitor and sub items plus Log Reader Agent there were added an icon, a red 'X' as in error. The replication seems to work.

Does anyone have any idea on why this happened and what I can do about it. It is not very nice to have errors in replication on a customers servers.

Grateful for answers
Best,
/M

Please refrain from creating multiple posts for the same question. Here's cut/paste from my reponse to your other post:

The cleanest way and supported way is to drop the distributor and recreate it from scratch. However you can also look at the following tables in distribution database and delete any rows that reference your deleted publications (but take a backup of the distribution database first, and note that manually deleting these tables is unsupported):

MSpublications

MSsubscriptions

MSarticles