Backup error

Hi all!
I take backup of a database every night using
"BACKUP DATABASE [Db1] TO DISK = N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT , NOUNLOAD , NAME = N'Db1 backup', NOSKIP , STATS = 10, NOFORMAT"
But some nights a have recived the following error message when I view the job history.
"'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fa
iled."
I have tried to delete the backup devices and created new ones. I am backing up close to twenty databases to the same folder every night. But I get error on a couple backup jobs every night, not the same ones every night. I have tride to run the jobs manu
ally daytime and sometimes they run ok and sometimes they fails.
I have read the MS KB 290787 but i'm not sure if it answers my question. Does anybody has any more info/help/hints it would be greatly appriciated.
Thansk
Fredrik
Sounds like you may have some disk issues. Have you checked the integrity
of that disk lately?
Andrew J. Kelly SQL MVP
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:46FACB20-2670-41DD-9452-CE9753680453@.microsoft.com...
> Hi all!
> I take backup of a database every night using
> "BACKUP DATABASE [Db1] TO DISK =
N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT , NOUNLOAD , NAME =
N'Db1 backup', NOSKIP , STATS = 10, NOFORMAT"
> But some nights a have recived the following error message when I view the
job history.
> "'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups
cannot be appended, but existing backup sets may still be usable. [SQLSTATE
42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE
42000] (Error 3013). The step failed."
> I have tried to delete the backup devices and created new ones. I am
backing up close to twenty databases to the same folder every night. But I
get error on a couple backup jobs every night, not the same ones every
night. I have tride to run the jobs manually daytime and sometimes they run
ok and sometimes they fails.
> I have read the MS KB 290787 but i'm not sure if it answers my question.
Does anybody has any more info/help/hints it would be greatly appriciated.
> Thansk
> Fredrik
|||Hi Fredrik
I have a similar problem and was able to fix it, quick and simple.
I renamed the backup and log file
and re ran my backup job, it obviously was unable to append as the file was not fould (due to name change), this created new file
and the backup job suceeded.
Note before all this I manually backed up DB (FULL) and Log file (for each db that I had the problem for) Just in case
For completness my exact error was
Executed as user: [USER NAME]. The backup data in '[DB NAME]' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (E
rror 3013). The step failed.
obviously i was able to delete all non required excess backup files after a few days as I was happy with my backups
Good Luck
Eoin B
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
sql

Backup error

Hi all!
I take backup of a database every night using
"BACKUP DATABASE [Db1] TO DISK = N'\\server\e$\Backup\Databases\Db1\Db1
.bak' WITH INIT , NOUNLOAD , NAME = N'Db1 backup', NOSKIP , STATS = 10,
NOFORMAT"
But some nights a have recived the following error message when I view the j
ob history.
"'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups cann
ot be appended, but existing backup sets may still be usable. [SQLSTATE
42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTAT
E 42000] (Error 3013). The step fa
iled."
I have tried to delete the backup devices and created new ones. I am backing
up close to twenty databases to the same folder every night. But I get erro
r on a couple backup jobs every night, not the same ones every night. I have
tride to run the jobs manu
ally daytime and sometimes they run ok and sometimes they fails.
I have read the MS KB 290787 but i'm not sure if it answers my question. Doe
s anybody has any more info/help/hints it would be greatly appriciated.
Thansk
FredrikSounds like you may have some disk issues. Have you checked the integrity
of that disk lately?
Andrew J. Kelly SQL MVP
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:46FACB20-2670-41DD-9452-CE9753680453@.microsoft.com...
> Hi all!
> I take backup of a database every night using
> "BACKUP DATABASE [Db1] TO DISK =
N'\\server\e$\Backup\Databases\Db1\Db1.bak' WITH INIT , NOUNLOAD , NAME =
N'Db1 backup', NOSKIP , STATS = 10, NOFORMAT"
> But some nights a have recived the following error message when I view the
job history.
> "'\\server\e$\Backup\Db1\Db1\Db1.bak' is incorrectly formatted. Backups
cannot be appended, but existing backup sets may still be usable. [SQLST
ATE
42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTAT
E
42000] (Error 3013). The step failed."
> I have tried to delete the backup devices and created new ones. I am
backing up close to twenty databases to the same folder every night. But I
get error on a couple backup jobs every night, not the same ones every
night. I have tride to run the jobs manually daytime and sometimes they run
ok and sometimes they fails.
> I have read the MS KB 290787 but i'm not sure if it answers my question.
Does anybody has any more info/help/hints it would be greatly appriciated.
> Thansk
> Fredrik|||Hi Fredrik
I have a similar problem and was able to fix it, quick and simple.
I renamed the backup and log file
and re ran my backup job, it obviously was unable to append as the file was
not fould (due to name change), this created new file
and the backup job suceeded.
Note before all this I manually backed up DB (FULL) and Log file (for each d
b that I had the problem for) Just in case
For completness my exact error was
Executed as user: [USER NAME]. The backup data in '[DB NAME]' is inc
orrectly formatted. Backups cannot be appended, but existing backup sets may
still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is term
inating abnormally. [SQLSTATE 42000] (E
rror 3013). The step failed.
obviously i was able to delete all non required excess backup files after a
few days as I was happy with my backups
Good Luck
Eoin B
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.sql

Backup Database at different place

Hi All!

I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:\isl_fs1\DBName.bak'

BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\\isl_fs1\DBName.bak'
it doesn't work please helpIt'll be longer, slower, at risk of network blips, not portable

Dump locally, then copy...

What, you're drive is out of space?

That's a different problem...|||I agree
typically i would back up to disk and then use network backup software to come get it out of the shared folder that i backed it up into

however to directly backup to a network address
One method is to map a drive to a network location
ex
NET USE X: \\Server\Share

and then use sql backup.
Backup database northwind to disk=x:\backup.bkf

you should however use sp_adddumpdevice to map a backup location to a permanent device it cleans up the backup processes and gives you a fixed location to find all of your backups instead of temporary ones

BackUp database

Hi all

I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?

USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'

USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'

BACKUP DATABASE test TO test_2

BACKUP LOG test TO testlog



thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)

e.g. - example of one connection & execution :

Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3

Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"

SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"

Set RS = Connection.Execute(SQLStmt)

Backup and Restore Publications

Hi All

I am using SQL Server 2000 with Windows 2000 Server. There are 3 publications in my merge replication I want to take Backup of my publication and also restore the existing backup of publication.

3 Subscribers are at remote locations what steps I should take to restore my subscription.

Thanks for kind response

FNKDo you want to keep Replication and run it in the new server where the publication was restored to?|||Originally posted by joejcheng
Do you want to keep Replication and run it in the new server where the publication was restored to?

Yes i want to keep the replication.
I have three publications configured on my server and I want to shift the Publisher database to new machine. I dont want to re-initialize the publications and subscriptions.
When the database is restored to new server, I want to get all the updates from the three subscriptions, since my last backup which is currently restored.|||When you change over to the new server, the name of the new server has to be the same as the old. Which means you have to change the old server's name to something else first. If the use the same server name, then you can restore the publication with 'Keep_replication' clause. Restart your subscirption after that. You may have problems with the Logreader. Issue sp_repldone to refresh the LSN number in the Logreader or change the system table. If the server's name is different, you need to drop subscription, restore the publication WITHOUT 'Keep_replication' clause, and setup everything from scratch. But you don't have to reinitialize the replication if you are sure the publication and the subscription are in sync. For more information check the 'Backing Up and Restoring Replication Databases' section of BOL.

Hope this helps. Good Luck!|||Hi joejcheng,

sorry I was unable to reply,

well, till now what I have done is, I changed the machine name with my actual server name, Enabled Publication, Distribution and Subscription settings and after that Restored my Replicated Database with 'Keep_Replication' Cluse and also Restored my Distribution Database.

I am little afraid with this statement from MSDN

"Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have enough meta data to apply changes to the publication database, such synchronization could lead to the non-convergence of data"

Because I got Anonymous Subscription

What should I do now? I need to change my subscription and publication options?

FNK|||One more thing is that I got Pull Subscription configured on subscriber

FNK

Backup & Restore in MSDE, How

Hi all;

I am looking to add "Make Bakup" and "Make Restore" functionalty in my applicayion menu which uses MSDE for database.

My question is How to add these two functionalty (What is the source code)??

Thanks in advanced!You'll need to use SQLDMO to accomplish this.view post 373287 andview post 389472 should help you get started.

Terri