We're running SQL Server 2000 service pack 4. I've created a Maintenance
Plan that backs up all user databases, each to its own subfolder, every
night. The plan is supposed to delete old copies as well which it is not
doing. If I change the plan to backup only 1 database it works (old copies
deleted). When I switch it to all user databases, no more deletes. And the
sqlmaint log file does not even show that any deletes were attempted. Note
that we do have some databases offline, so those are skipped, of course, and
the job does fail.
Please help! What's the secret to get the older files to delete?
Here's the Step on the SQL Agent job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
21EA0A98-9812-4AEA-8BC0-08AEAEAB24EC -Rpt "c:\temp\DB Maintenance
Plan14.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpDB "D:\Backup" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'
Thanks,
Krip
>I believe that deletion is done *at the end* of the job, so if anything
>fails, it bails out and no deletion is performed. You can try by first
>including only database so the whole job succeeds.
Tibor,
Thanks for the tip! I detached all our offline databases, and job finishes
without error, and deletes the old backups. Beautiful.
-Krip
|||I have also seen this behavior when the plan includes both Full and Simple
Recovery model databases.
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Krip" <amk@.kynetix.com> wrote in message
news:7A2C4350-5AED-4F70-A22A-47E64ED68DFD@.microsoft.com...
> Tibor,
> Thanks for the tip! I detached all our offline databases, and job
> finishes without error, and deletes the old backups. Beautiful.
> -Krip
>
Showing posts with label own. Show all posts
Showing posts with label own. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
Backups
Does anyone know if when a backup is done to an sql
database using a third party tool, if sql server
automatically (on its OWN) truncates the transaction log?If the 3rd party tool using the proper sql server plugin and issues a LOG
BACKUP command it will be truncated. Otherwise it will not. FULL backups
do not truncate the log.
--
Andrew J. Kelly
SQL Server MVP
"cs" <anonymous@.discussions.microsoft.com> wrote in message
news:07ea01c3ce2b$6e4e80e0$a601280a@.phx.gbl...
> Does anyone know if when a backup is done to an sql
> database using a third party tool, if sql server
> automatically (on its OWN) truncates the transaction log?
database using a third party tool, if sql server
automatically (on its OWN) truncates the transaction log?If the 3rd party tool using the proper sql server plugin and issues a LOG
BACKUP command it will be truncated. Otherwise it will not. FULL backups
do not truncate the log.
--
Andrew J. Kelly
SQL Server MVP
"cs" <anonymous@.discussions.microsoft.com> wrote in message
news:07ea01c3ce2b$6e4e80e0$a601280a@.phx.gbl...
> Does anyone know if when a backup is done to an sql
> database using a third party tool, if sql server
> automatically (on its OWN) truncates the transaction log?
Backups
I have transactional replication topology where each component is on
its own server. I currently backup the t-logs every 15 minutes with a
differential at noon and a full back up at midnight.
Is it necessary for me to create the exact same backup strategy at the
distributor and subscriber? Or would a full backup with less frequent
t-log backups be sufficient for the distribution and subscription
databases?As you did not mention which version of your SQL Server, I'll post 2
choices.
There is an article about "Strategies for Backing Up and Restoring
Transactional Replication" for SQL Server 2000 in Books Online. You may want
to check it out?
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
And this is for SQL Server 2005:
http://msdn2.microsoft.com/en-us/library/ms152560.aspx
--
Ekrem Önsoy
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1191534232.786974.23060@.o80g2000hse.googlegroups.com...
>I have transactional replication topology where each component is on
> its own server. I currently backup the t-logs every 15 minutes with a
> differential at noon and a full back up at midnight.
> Is it necessary for me to create the exact same backup strategy at the
> distributor and subscriber? Or would a full backup with less frequent
> t-log backups be sufficient for the distribution and subscription
> databases?
>
its own server. I currently backup the t-logs every 15 minutes with a
differential at noon and a full back up at midnight.
Is it necessary for me to create the exact same backup strategy at the
distributor and subscriber? Or would a full backup with less frequent
t-log backups be sufficient for the distribution and subscription
databases?As you did not mention which version of your SQL Server, I'll post 2
choices.
There is an article about "Strategies for Backing Up and Restoring
Transactional Replication" for SQL Server 2000 in Books Online. You may want
to check it out?
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
And this is for SQL Server 2005:
http://msdn2.microsoft.com/en-us/library/ms152560.aspx
--
Ekrem Önsoy
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:1191534232.786974.23060@.o80g2000hse.googlegroups.com...
>I have transactional replication topology where each component is on
> its own server. I currently backup the t-logs every 15 minutes with a
> differential at noon and a full back up at midnight.
> Is it necessary for me to create the exact same backup strategy at the
> distributor and subscriber? Or would a full backup with less frequent
> t-log backups be sufficient for the distribution and subscription
> databases?
>
Sunday, February 19, 2012
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?
Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?
Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "|||Hi Sue
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself? :
Thanks for your help.|||No matter found it myself on MSDN
BACKUP DATABASE DBNameGoesHer
TO DISK = 'C:\temp\Somefilename.bak
WITH INI
RESTORE DATABASE DBNameGoesHer
FROM DISK = 'C:\temp\Somefilename.bak
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =that you have SQL Server installed).
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-- Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
> > BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
> > RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "|||Hi Sue
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself? :
Thanks for your help.|||No matter found it myself on MSDN
BACKUP DATABASE DBNameGoesHer
TO DISK = 'C:\temp\Somefilename.bak
WITH INI
RESTORE DATABASE DBNameGoesHer
FROM DISK = 'C:\temp\Somefilename.bak
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =that you have SQL Server installed).
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-- Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
> > BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
> > RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Backup/restore Databases remotely.
Hi i've tried a few remote administration tools including Microsofts own Web
Data Administrator for SQL server... but none of them seem to do what is es
sential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remot
ely. I dont really want to have to use export/import data as things like tri
ggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But php would also
be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But php would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cann
ot.. it can only import/export data which does not include things like trigg
ers.
See this page:
http://www.databasejournal.com/feat...10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool can
not.. it can only import/export data which does not include things like trig
gers.
>See this page:
>http://www.databasejournal.com/feat...10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration too
l
>- Can't create a database backup and restore a database "|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link w
here I can find this out myself?
Thanks for your help.|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techin.../2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Data Administrator for SQL server... but none of them seem to do what is es
sential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remot
ely. I dont really want to have to use export/import data as things like tri
ggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But php would also
be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But php would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cann
ot.. it can only import/export data which does not include things like trigg
ers.
See this page:
http://www.databasejournal.com/feat...10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool can
not.. it can only import/export data which does not include things like trig
gers.
>See this page:
>http://www.databasejournal.com/feat...10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration too
l
>- Can't create a database backup and restore a database "|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link w
here I can find this out myself?
Thanks for your help.|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techin.../2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE
Subscribe to:
Posts (Atom)