sql backup files: cant

Showing posts with label cant. Show all posts

Thursday, March 22, 2012

Backup script

I want to create a simple SQL 2005 database backup script to run a scheduled
times. This was easy with SQL 2000 but I can't find a similar process in
2005. Can anyone tell me where I can find it? Thanks.
David
David,
In SQL Server Management Studio under the node Management / Maintenance
Plans you can create a maintenance plan using the Maintenance Plan Wizard
that backs up the databases. If you back up to disk using the appropriate
BACK UP DATABASE settings you should also be sure to check and fill out the
information for the MAINTENANCE CLEANUP TASK so that old backups get
deleted.
Is that what you are looking for?
RLF
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Alternatively,
In SQL Server 2005 every nearly window is scriptable. So, go to a database
and right click on it and Tasks\Back up... make your changes from the GUI
and you'll see a button on top of the Back Up Database window which is
labeled "Script". There is an arrow near it, click on it and you'll see your
choices.
You can use these scripts to create Jobs from the SQL Server Agent.
Ekrem nsoy
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>I want to create a simple SQL 2005 database backup script to run a
>scheduled times. This was easy with SQL 2000 but I can't find a similar
>process in 2005. Can anyone tell me where I can find it? Thanks.
> David
>
|||Thank you both. That is what I needed.
David
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:D82FA9CF-70A7-4802-A7C2-8C1BA3B053E8@.microsoft.com...
> Alternatively,
> In SQL Server 2005 every nearly window is scriptable. So, go to a database
> and right click on it and Tasks\Back up... make your changes from the GUI
> and you'll see a button on top of the Back Up Database window which is
> labeled "Script". There is an arrow near it, click on it and you'll see
> your choices.
> You can use these scripts to create Jobs from the SQL Server Agent.
> --
> Ekrem nsoy
>
> "David C" <dlchase@.lifetimeinc.com> wrote in message
> news:OFL4DY5MIHA.5400@.TK2MSFTNGP04.phx.gbl...
>
sql

Wednesday, March 7, 2012

Backup of transaction log disappears

Hi All
I have this weird problem, and i cant find out what is happening.
I have a a database maintence plan that backsup the transaction log for one
of our databases. The job runs, i watch the trn file get created, then when
the job is complete, the trn file disappers, and the job fails becasue it
cannot open the log file to verify the backup.
I have no idea where the trn files are going.
here is the DBmaintance log file.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
[1] Database riverred: Transaction Log Backup...
Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
** Execution Time: 0 hrs, 0 mins, 11 secs **
[2] Database Riverred: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
SQL2000 with SP3a - in a w2k cluster environment
Any ideas?
thx in advanceHi
Run the backup from T-SQL and see if it also occurs. Check your retention
period of dumps in the maintenace plan.
Regards
Mike
"David" wrote:
> Hi All
> I have this weird problem, and i cant find out what is happening.
> I have a a database maintence plan that backsup the transaction log for one
> of our databases. The job runs, i watch the trn file get created, then when
> the job is complete, the trn file disappers, and the job fails becasue it
> cannot open the log file to verify the backup.
> I have no idea where the trn files are going.
> here is the DBmaintance log file.
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> [1] Database riverred: Transaction Log Backup...
> Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> ** Execution Time: 0 hrs, 0 mins, 11 secs **
> [2] Database Riverred: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> SQL2000 with SP3a - in a w2k cluster environment
> Any ideas?
> thx in advance
>|||We tried to run it via t-SQL and it didnt work, The retention period is set
for 2 days. I have tried changing the times as well.
D
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Run the backup from T-SQL and see if it also occurs. Check your retention
> period of dumps in the maintenace plan.
> Regards
> Mike
> "David" wrote:
> > Hi All
> >
> > I have this weird problem, and i cant find out what is happening.
> >
> > I have a a database maintence plan that backsup the transaction log for one
> > of our databases. The job runs, i watch the trn file get created, then when
> > the job is complete, the trn file disappers, and the job fails becasue it
> > cannot open the log file to verify the backup.
> >
> > I have no idea where the trn files are going.
> >
> > here is the DBmaintance log file.
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> > [1] Database riverred: Transaction Log Backup...
> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> >
> > [2] Database Riverred: Verifying Backup...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> > See the SQL Server error log for more details.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> > terminating abnormally.
> > Deleting old text reports... 0 file(s) deleted.
> >
> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
> > SQL2000 with SP3a - in a w2k cluster environment
> >
> > Any ideas?
> >
> > thx in advance
> >|||"David" wrote:
> Hi All
> I have this weird problem, and i cant find out what is happening.
> I have a a database maintence plan that backsup the transaction log for one
> of our databases. The job runs, i watch the trn file get created, then when
> the job is complete, the trn file disappers, and the job fails becasue it
> cannot open the log file to verify the backup.
> I have no idea where the trn files are going.
> here is the DBmaintance log file.
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> [1] Database riverred: Transaction Log Backup...
> Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> ** Execution Time: 0 hrs, 0 mins, 11 secs **
> [2] Database Riverred: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> SQL2000 with SP3a - in a w2k cluster environment
> Any ideas?
> thx in advance
>|||We just moved the log file backup to a different drive. and it works.
Now i am scared about why those trn files were disappearing on the other
drive?
any ideas?
"David" wrote:
> We tried to run it via t-SQL and it didnt work, The retention period is set
> for 2 days. I have tried changing the times as well.
> D
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Run the backup from T-SQL and see if it also occurs. Check your retention
> > period of dumps in the maintenace plan.
> >
> > Regards
> > Mike
> >
> > "David" wrote:
> >
> > > Hi All
> > >
> > > I have this weird problem, and i cant find out what is happening.
> > >
> > > I have a a database maintence plan that backsup the transaction log for one
> > > of our databases. The job runs, i watch the trn file get created, then when
> > > the job is complete, the trn file disappers, and the job fails becasue it
> > > cannot open the log file to verify the backup.
> > >
> > > I have no idea where the trn files are going.
> > >
> > > here is the DBmaintance log file.
> > >
> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> > > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> > > [1] Database riverred: Transaction Log Backup...
> > > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> > >
> > > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> > >
> > > [2] Database Riverred: Verifying Backup...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> > > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> > > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> > > See the SQL Server error log for more details.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> > > terminating abnormally.
> > > Deleting old text reports... 0 file(s) deleted.
> > >
> > > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> > > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > >
> > > SQL2000 with SP3a - in a w2k cluster environment
> > >
> > > Any ideas?
> > >
> > > thx in advance
> > >|||What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did the
backup command return?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
> We tried to run it via t-SQL and it didnt work, The retention period is set
> for 2 days. I have tried changing the times as well.
> D
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Run the backup from T-SQL and see if it also occurs. Check your retention
>> period of dumps in the maintenace plan.
>> Regards
>> Mike
>> "David" wrote:
>> > Hi All
>> >
>> > I have this weird problem, and i cant find out what is happening.
>> >
>> > I have a a database maintence plan that backsup the transaction log for one
>> > of our databases. The job runs, i watch the trn file get created, then when
>> > the job is complete, the trn file disappers, and the job fails becasue it
>> > cannot open the log file to verify the backup.
>> >
>> > I have no idea where the trn files are going.
>> >
>> > here is the DBmaintance log file.
>> >
>> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> > [1] Database riverred: Transaction Log Backup...
>> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >
>> > [2] Database Riverred: Verifying Backup...
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> > See the SQL Server error log for more details.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> > terminating abnormally.
>> > Deleting old text reports... 0 file(s) deleted.
>> >
>> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >
>> > SQL2000 with SP3a - in a w2k cluster environment
>> >
>> > Any ideas?
>> >
>> > thx in advance
>> >|||Hi
There might be something that is deleting the files. It might be a
housekeeping job that someone wrote, setup and forgot to stop.
Try giving the SQL Service account only read/write access to the directory,
and everyone else, read only. Run the backups and see if they survive.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.discussions.microsoft.com> wrote in message
news:A31659E5-BFED-4516-B8EA-89C1D96B9691@.microsoft.com...
> We just moved the log file backup to a different drive. and it works.
> Now i am scared about why those trn files were disappearing on the other
> drive?
> any ideas?
> "David" wrote:
>> We tried to run it via t-SQL and it didnt work, The retention period is
>> set
>> for 2 days. I have tried changing the times as well.
>> D
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > Run the backup from T-SQL and see if it also occurs. Check your
>> > retention
>> > period of dumps in the maintenace plan.
>> >
>> > Regards
>> > Mike
>> >
>> > "David" wrote:
>> >
>> > > Hi All
>> > >
>> > > I have this weird problem, and i cant find out what is happening.
>> > >
>> > > I have a a database maintence plan that backsup the transaction log
>> > > for one
>> > > of our databases. The job runs, i watch the trn file get created,
>> > > then when
>> > > the job is complete, the trn file disappers, and the job fails
>> > > becasue it
>> > > cannot open the log file to verify the backup.
>> > >
>> > > I have no idea where the trn files are going.
>> > >
>> > > here is the DBmaintance log file.
>> > >
>> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> > > Server
>> > > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> > > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03
>> > > AM
>> > > [1] Database riverred: Transaction Log Backup...
>> > > Destination: [G:\riverred\Log File
>> > > Backups\riverred_tlog_200504080727.TRN]
>> > >
>> > > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> > >
>> > > [2] Database Riverred: Verifying Backup...
>> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
>> > > [Microsoft][ODBC SQL
>> > > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log
>> > > File
>> > > Backups\reiverred_tlog_200504080727.TRN'. Device error or device
>> > > off-line.
>> > > See the SQL Server error log for more details.
>> > > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> > > terminating abnormally.
>> > > Deleting old text reports... 0 file(s) deleted.
>> > >
>> > > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14
>> > > AM
>> > > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> > >
>> > > SQL2000 with SP3a - in a w2k cluster environment
>> > >
>> > > Any ideas?
>> > >
>> > > thx in advance
>> > >|||That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP LOG
command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
> Sorry, didnt work meant, i recieved the error message, Error, 22029
>
> "Tibor Karaszi" wrote:
>> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did
>> the
>> backup command return?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
>> > We tried to run it via t-SQL and it didnt work, The retention period is set
>> > for 2 days. I have tried changing the times as well.
>> >
>> > D
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Run the backup from T-SQL and see if it also occurs. Check your retention
>> >> period of dumps in the maintenace plan.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "David" wrote:
>> >>
>> >> > Hi All
>> >> >
>> >> > I have this weird problem, and i cant find out what is happening.
>> >> >
>> >> > I have a a database maintence plan that backsup the transaction log for one
>> >> > of our databases. The job runs, i watch the trn file get created, then when
>> >> > the job is complete, the trn file disappers, and the job fails becasue it
>> >> > cannot open the log file to verify the backup.
>> >> >
>> >> > I have no idea where the trn files are going.
>> >> >
>> >> > here is the DBmaintance log file.
>> >> >
>> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> >> > [1] Database riverred: Transaction Log Backup...
>> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >> >
>> >> > [2] Database Riverred: Verifying Backup...
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> >> > See the SQL Server error log for more details.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> >> > terminating abnormally.
>> >> > Deleting old text reports... 0 file(s) deleted.
>> >> >
>> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> >
>> >> > SQL2000 with SP3a - in a w2k cluster environment
>> >> >
>> >> > Any ideas?
>> >> >
>> >> > thx in advance
>> >> >
>>|||I moved the backup destination to another drive, then the maitanence plan
started to work, after 1 day of working properly i am exhibiting the same
problem.
I can see the trn file get created with 0kb then i see the size of the file
appears then the whole trn file disappears.
I tried making a new maitanence plan as well, and get the same problem.
error messages for the job
18204 :
BackupDiskFile::OpenMedia: Backup device
'H:\SQLServer\redriver_tlog_200504152131.TRN' failed to open. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
3041 :
BACKUP failed to complete the command BACKUP LOG [AbsolutePoker] TO DISK =N'H:\SQLServer\redriver_tlog_200504152131.TRN' WITH INIT , NOUNLOAD ,
NOSKIP , STATS = 10, NOFORMAT
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'redriver Log Backup'' (0x6A757F003EBDCD4DB810F28061D0AAE2) - Status: Failed
- Invoked on: 2005-04-15 21:31:50 - Message: The job failed. The Job was
invoked by User domain\administrator. The last step to run was step 1 (Step
1).
Maintanence plan history:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLServer\redriver_tlog_200504152131.TRN'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
"Tibor Karaszi" wrote:
> That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP LOG
> command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
> > Sorry, didnt work meant, i recieved the error message, Error, 22029
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did
> >> the
> >> backup command return?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
> >> > We tried to run it via t-SQL and it didnt work, The retention period is set
> >> > for 2 days. I have tried changing the times as well.
> >> >
> >> > D
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Run the backup from T-SQL and see if it also occurs. Check your retention
> >> >> period of dumps in the maintenace plan.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "David" wrote:
> >> >>
> >> >> > Hi All
> >> >> >
> >> >> > I have this weird problem, and i cant find out what is happening.
> >> >> >
> >> >> > I have a a database maintence plan that backsup the transaction log for one
> >> >> > of our databases. The job runs, i watch the trn file get created, then when
> >> >> > the job is complete, the trn file disappers, and the job fails becasue it
> >> >> > cannot open the log file to verify the backup.
> >> >> >
> >> >> > I have no idea where the trn files are going.
> >> >> >
> >> >> > here is the DBmaintance log file.
> >> >> >
> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> >> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> >> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> >> >> > [1] Database riverred: Transaction Log Backup...
> >> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> >> >> >
> >> >> > [2] Database Riverred: Verifying Backup...
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> >> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> >> >> > See the SQL Server error log for more details.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> >> >> > terminating abnormally.
> >> >> > Deleting old text reports... 0 file(s) deleted.
> >> >> >
> >> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> >> >
> >> >> > SQL2000 with SP3a - in a w2k cluster environment
> >> >> >
> >> >> > Any ideas?
> >> >> >
> >> >> > thx in advance
> >> >> >
> >>
> >>
> >>
>
>|||I have a feeling that this is an OS, possibly a permissions issue. I'd start by scheduling a regular
Agent job to do the same and see if it behaves the same. I'd also look in eventlog for all possibly
related errors. Could be permissions, quota and other stuff...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:F49567EC-9741-4A24-8BD1-CCE0EFD4DDF7@.microsoft.com...
>I moved the backup destination to another drive, then the maitanence plan
> started to work, after 1 day of working properly i am exhibiting the same
> problem.
> I can see the trn file get created with 0kb then i see the size of the file
> appears then the whole trn file disappears.
> I tried making a new maitanence plan as well, and get the same problem.
> error messages for the job
> 18204 :
> BackupDiskFile::OpenMedia: Backup device
> 'H:\SQLServer\redriver_tlog_200504152131.TRN' failed to open. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [AbsolutePoker] TO DISK => N'H:\SQLServer\redriver_tlog_200504152131.TRN' WITH INIT , NOUNLOAD ,
> NOSKIP , STATS = 10, NOFORMAT
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'redriver Log Backup'' (0x6A757F003EBDCD4DB810F28061D0AAE2) - Status: Failed
> - Invoked on: 2005-04-15 21:31:50 - Message: The job failed. The Job was
> invoked by User domain\administrator. The last step to run was step 1 (Step
> 1).
> Maintanence plan history:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLServer\redriver_tlog_200504152131.TRN'. Device error or device
> off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
>
>
>
>
> "Tibor Karaszi" wrote:
>> That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP
>> LOG
>> command?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
>> > Sorry, didnt work meant, i recieved the error message, Error, 22029
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages
>> >> did
>> >> the
>> >> backup command return?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
>> >> > We tried to run it via t-SQL and it didnt work, The retention period is set
>> >> > for 2 days. I have tried changing the times as well.
>> >> >
>> >> > D
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Run the backup from T-SQL and see if it also occurs. Check your retention
>> >> >> period of dumps in the maintenace plan.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "David" wrote:
>> >> >>
>> >> >> > Hi All
>> >> >> >
>> >> >> > I have this weird problem, and i cant find out what is happening.
>> >> >> >
>> >> >> > I have a a database maintence plan that backsup the transaction log for one
>> >> >> > of our databases. The job runs, i watch the trn file get created, then when
>> >> >> > the job is complete, the trn file disappers, and the job fails becasue it
>> >> >> > cannot open the log file to verify the backup.
>> >> >> >
>> >> >> > I have no idea where the trn files are going.
>> >> >> >
>> >> >> > here is the DBmaintance log file.
>> >> >> >
>> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> >> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> >> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> >> >> > [1] Database riverred: Transaction Log Backup...
>> >> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >> >> >
>> >> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >> >> >
>> >> >> > [2] Database Riverred: Verifying Backup...
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> >> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> >> >> > See the SQL Server error log for more details.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> >> >> > terminating abnormally.
>> >> >> > Deleting old text reports... 0 file(s) deleted.
>> >> >> >
>> >> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> >> >
>> >> >> > SQL2000 with SP3a - in a w2k cluster environment
>> >> >> >
>> >> >> > Any ideas?
>> >> >> >
>> >> >> > thx in advance
>> >> >> >
>> >>
>> >>
>> >>
>>

Backup of transaction log disappears

Friday, February 24, 2012

Backup name with DateTime

I know how to backup my DB but i cant find a way to give a name with the current date time.

backup database SUSDB to disk = 'e:\backup\test_%date%.bak' with init;

Any idea how i can put the date to my file name?

thank you

You could use dynamic sq;:

declare

@.sql varchar(300)

set

@.sql='backup database SUSDB to disk = ''e:\backup\test_'+convert(varchar,getdate(),101)+'.bak'''

exec

(@.sql)

|||

You have two options modify the code in the first link for your needs or use the last two links to create one just for you. Hope this helps.

http://support.microsoft.com/kb/241397

Create custom code from these two.

http://forums.asp.net/thread/1049663.aspx

http://msdn2.microsoft.com/en-gb/library/aa259582(SQL.80).aspx

|||

Sorry i forgot to mention that i use my code inside SQL 2005 jobs,

so the backup is taken everynight

|||

So in your job step you should generate file name like was shown in one of previous steps, you can also if you wolud like to keep information how many backup you did in file name, modify backup step automatically on success to conatain current backup number. But for only modifing date, code from first answer should be helpful.

Good luck

Backup MSDE database

Hi,

we have about 12 databases in an MSDE server.

We can install the enterprise manager, but cant get the maintenance plans to work. (fields stay empty and setting dont get saved)
I've searched a bit, but couldn't fix it.

No problem, i created a job, with 12 T-SQL commands, and the backups are working perfect.

Now we wanne backup with a history (we take a backup every 4 hours of the day), because now they always get overwritten.

So im trying to change my transactSQL to create dynamic files.

The code i'm using right now :

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

I've tried different things like

BACKUP DATABASE PW0001A00 TO DISK = 'C:\SQLBACKUP\PW0001A00' + DATEPART(month, GETDATE()) + '.BAK' WITH INIT, NOUNLOAD, NOSKIP, STATS=10, NOFORMAT

but nothing worked.

so basically I wanne be able to create dynamic filenames in the above command.

You got any ideas/solutions.

tnx.Take Umachander's http://www.umachandar.com/technical/SQL70Scripts/Main30.htm solution in this case.|||thank you.

i'll try that solution. looks good. :-)

Monday, February 13, 2012

backup job can't remove old .BAK from disk

.
I assume you are copying the backup somewhere otherwise if the backup
fails or is corrupt you are left without a backup and can lose the
database.
I try to keep at least 3 backups on disk and will zip them if they are
large.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas

"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:

> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:%23v0oMCi2EHA.1392@.tk2msftngp13.phx.gbl...
> Get rid of the maint plan and code it yourself. You only keep 1 copy so
> you can just delete it before the backup.
>
Umm, can I recommend deleting it AFTER a successful backup.
Otherwise you risk deleting your one good backup and having the new one
fail. :-)

> It would be quicker to write the job than to have posted your question
>

.
> I assume you are copying the backup somewhere otherwise if the backup
> fails or is corrupt you are left without a backup and can lose the
> database.
> I try to keep at least 3 backups on disk and will zip them if they are
> large.
> Nigel Rivett
> www.nigelrivett.net
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

backup job can't remove old .BAK from disk

Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
JohnCould you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>|||We have a space problem, but more because of our Storage Administration
team.
The default behaviour of the Maintenance Plans is to backup up and then
verify each database in the plan. Only after all backups are completed,
without error, the file delete portion runs for yesterday's backup. So, you
need space for at least 2 Full backups and any retained transaction log
backups in between.
The way we've coded it is to go ahead and set the Maintenance Plans, but
then code another job that runs several times per day checking the Archive
Bit on the prior backup files. Whenever the tape client runs, it will reset
this bit allowing our job to remove it. This reduces our space requirements
to just 1 set of Full backups and intra-day transaction log backups.
Also, we write our backups to dedicated disk partitions that are compressed.
You might also consider something like SQL Litespeed, which further
compresses the database backup files but integrates nicely withing the
Maintenance Plans already created.
Hope this helps.
Sincerely,
Anthony Thomas
"randy" <randydoub@.yahoo.com> wrote in message
news:1beb01c4dc68$9f66cb00$a301280a@.phx.gbl...
I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>

Thursday, March 22, 2012

Backup script

Wednesday, March 7, 2012

Backup of transaction log disappears

Backup of transaction log disappears

Backup of transaction log disappears

Friday, February 24, 2012

Backup name with DateTime

Backup MSDE database

Monday, February 13, 2012

backup job can't remove old .BAK from disk

backup job can't remove old .BAK from disk

sql backup files

Blog Archive

About Me