hello,
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.
Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts
Tuesday, March 27, 2012
Basic question
hello,
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
I am not an administrator, please if I want to know the name of my sql
server, how can i find it?
I have sql server 2000 which I installed long time ago, I forgot what I
named it during the installation, i think it is the default name.
Is it local or (local) or something else?
Thank you.Hi,
Login to Query analyzer and execute the below command
select @.@.SERVERNAME
Thanks
Hari
SQL Server MVP
"Philip Germanos" <PhilipGermanos@.discussions.microsoft.com> wrote in
message news:D659E5D9-F809-4C0E-8DE4-F6DCD98BFC66@.microsoft.com...
> hello,
> I am not an administrator, please if I want to know the name of my sql
> server, how can i find it?
> I have sql server 2000 which I installed long time ago, I forgot what I
> named it during the installation, i think it is the default name.
> Is it local or (local) or something else?
> Thank you.
Tuesday, March 20, 2012
Bakup dosen't delete files
When using SQLserver to take backup throw a maintance plan
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill at
>MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill at
>MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>
Sunday, March 11, 2012
Bad Connection
I am getting the following error when I attempt to connect to SqlServer with the following connection string from my Web.config file:
<add name="LeanPathConnection" connectionString="Data Source=(local);Initial Catalog=VWOA1.DB;User Id=LeanPath;Password=jesus" />
I have made LeanPath a user at the Security level of the server. His default database is VWOA1.DB. I have given him db_owner rights. The following contains the exception detail I get. I don't see how he could NOT have a trusted SQL Server connection!!!!!!
System.Data.SqlClient.SqlException was unhandled by user code
Message="Login failed for user 'LeanPath'. The user is not associated with a trusted SQL Server connection."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=14
LineNumber=65536
Number=18452
Procedure=""
Server="localhost"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at DbConnection.Open() in c:\Documents and Settings\sarogers\My Documents\Visual Studio 2005\Projects\VWOA\LRS\App_Code\DbConnection.cs:line 21
at MyBase.Execute() in c:\Documents and Settings\sarogers\My Documents\Visual Studio 2005\Projects\VWOA\LRS\App_Code\DbStoredProc.cs:line 63
at MyReturn.RetValParamInt(String ParamName) in c:\Documents and Settings\sarogers\My Documents\Visual Studio 2005\Projects\VWOA\LRS\App_Code\DbStoredProc.cs:line 328
at DbStoredProc.PER_Validate(String LoginName, String Password) in c:\Documents and Settings\sarogers\My Documents\Visual Studio 2005\Projects\VWOA\LRS\App_Code\DbStoredProc.cs:line 1215
at Login.btnLogin_Click(Object sender, EventArgs e) in c:\Documents and Settings\sarogers\My Documents\Visual Studio 2005\Projects\VWOA\LRS\Login.aspx.cs:line 43
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
you only have Windows Authentication enabled at your server, therefore the SQL Server tries to logon the user with WIndows Authentication. I guess that the LeanUser is a SQL Server User right ? Therefore you will have to use and enable SQL Server authentication first. See my Screencast on my site for more information how to switch from Windows Auth. to Mixed Auth. (SQL Server & Windows Auth).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Backward compatiblity in sqlserver 05 Enterprise edition
Hi,
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>
Backward compatiblity in sqlserver 05 Enterprise edition
Hi,
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>
I believe I'm running into a backward compatibility issue with the sqlserver
05 Enterprise edition. I restored a database from sqlserver 2000 to my new
sqlserver 2005. Both of my .net project and Access gets time-out errors
(running remotely) when they try to access to a table that I have modified
via query analyzer in sqlserver 2005. I had no problem reading the table via
those clients before I touched its data, so it looks like modifying data in
those table creating an incompatiblity issue.
Has anybody run into similar situation before? Thank you very much in
advance for your help!Never mind. I had a transaction failed to commit or rollback because of a
fatal error and so my subsequence data modification statements would just
lock up the tables.
"Zen" <zen@.nononospam.com> wrote in message
news:eKrY0y3fGHA.5092@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I believe I'm running into a backward compatibility issue with the
> sqlserver 05 Enterprise edition. I restored a database from sqlserver 2000
> to my new sqlserver 2005. Both of my .net project and Access gets
> time-out errors (running remotely) when they try to access to a table that
> I have modified via query analyzer in sqlserver 2005. I had no problem
> reading the table via those clients before I touched its data, so it looks
> like modifying data in those table creating an incompatiblity issue.
> Has anybody run into similar situation before? Thank you very much in
> advance for your help!
>
backward compatibility (2000->2005)
Hi!
I have heard that it's not possible to use (for example) *= as a join in SQL
Server 2005 and now I have tried to find other things that I can't use. Does
anyone know where I can find this?
Thanks =)
//MalinHi
"Breaking Changes to Database Engine Features in SQL Server 2005" in Books
Online.
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Check out Backward Compatibility in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec
-ba920476dd69.htm).
This documents discontinued, deprecated and breaking changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Micheal and Dan,
this was exact what I was looking for =)
Thank you!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Om3dtTn0GHA.2636@.TK2MSFTNGP06.phx.gbl...
> Check out Backward Compatibility in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96
ec-ba920476dd69.htm).
> This documents discontinued, deprecated and breaking changes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>
I have heard that it's not possible to use (for example) *= as a join in SQL
Server 2005 and now I have tried to find other things that I can't use. Does
anyone know where I can find this?
Thanks =)
//MalinHi
"Breaking Changes to Database Engine Features in SQL Server 2005" in Books
Online.
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Check out Backward Compatibility in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec
-ba920476dd69.htm).
This documents discontinued, deprecated and breaking changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Micheal and Dan,
this was exact what I was looking for =)
Thank you!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Om3dtTn0GHA.2636@.TK2MSFTNGP06.phx.gbl...
> Check out Backward Compatibility in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96
ec-ba920476dd69.htm).
> This documents discontinued, deprecated and breaking changes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>
Wednesday, March 7, 2012
Backups files not deleted with maintenance plan
Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
Backups files not deleted with maintenance plan
Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
>> The backup works perfectly fine, but it does not delete the backup files
>> scheduled to cleanup files older then a week. Any suggestions where to
>> look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at MS:
>>
>> -- Log files don't delete --
>> This is likely to be either a permissions problem or a sharing violation
>> problem. The maintenance plan is run as a job, and jobs are run by the
>> SQLServerAgent service.
>> Permissions:
>> 1. Determine the startup account for the SQLServerAgent service
>> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
>> account is the security context for jobs, and thus the maintenance plan.
>> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
>> account) then skip step 3.
>> 3. On that box, log onto NT as that account. Using Explorer, attempt to
>> delete an expired backup. If that succeeds then go to Sharing Violation
>> section.
>> 4. Log onto NT with an account that is an administrator and use Explorer to
>> look at the Properties|Security of the folder (where the backups reside)
>> and ensure the SQLServerAgent startup account has Full Control. If the
>> SQLServerAgent startup account is LocalSystem, then the account to consider
>> is SYSTEM.
>> 5. In NT, if an account is a member of an NT group, and if that group has
>> Access is Denied, then that account will have Access is Denied, even if
>> that account is also a member of the Administrators group. Thus you may
>> need to check group permissions (if the Startup Account is a member of a
>> group).
>> 6. Keep in mind that permissions (by default) are inherited from a parent
>> folder. Thus, if the backups are stored in C:\bak, and if someone had
>> denied permission to the SQLServerAgent startup account for C:\, then
>> C:\bak will inherit access is denied.
>> Sharing violation:
>> This is likely to be rooted in a timing issue, with the most likely cause
>> being another scheduled process (such as NT Backup or Anti-Virus software)
>> having the backup file open at the time when the SQLServerAgent (i.e., the
>> maintenance plan job) tried to delete it.
>> 1. Download filemon and handle from www.sysinternals.com.
>> 2. I am not sure whether filemon can be scheduled, or you might be able to
>> use NT scheduling services to start filemon just before the maintenance
>> plan job is started, but the filemon log can become very large, so it would
>> be best to start it some short time before the maintenance plan starts.
>> 3. Inspect the filemon log for another process that has that backup file
>> open (if your lucky enough to have started filemon before this other
>> process grabs the backup folder), and inspect the log for the results when
>> the SQLServerAgent agent attempts to open that same file.
>> 4. Schedule the job or that other process to do their work at different
>> times.
>> 5. You can use the handle utility if you are around at the time when the
>> job is scheduled to run.
>> If the backup files are going to a \\share or a mapped drive (as opposed to
>> local drive), then you will need to modify the above (with respect to where
>> the tests and utilities are run).
>> Finally, inspection of the maintenance plan's history report might be
>> useful.
>> Thanks,
>> Bill Hollinshead
>> Microsoft, SQL Server
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
>> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
>> week. Any suggestions where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually deleted backups for around
>> 40 databases.
>> Thanks a lot.
>> Imran.
>>
>|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>> Thanks for the link and the details. It cannot be a permissions issue or
>> a sharing violation since I have the same issue on two servers, the
>> development server and the live server both don't delete backup files.
>> I ran the maintenance plan right now and there wasn't any other process
>> holding the backup files. but still the files were not deleted. The
>> backup log did not indicate any errors relating to deletion of files,
>> rather it does not even show the attempt to delete the older files.
>> Any clues ?
>> Imran.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at
>> MS:
>>
>> -- Log files don't delete --
>> This is likely to be either a permissions problem or a sharing violation
>> problem. The maintenance plan is run as a job, and jobs are run by the
>> SQLServerAgent service.
>> Permissions:
>> 1. Determine the startup account for the SQLServerAgent service
>> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
>> This
>> account is the security context for jobs, and thus the maintenance plan.
>> 2. If SQLServerAgent is started using LocalSystem (as opposed to a
>> domain
>> account) then skip step 3.
>> 3. On that box, log onto NT as that account. Using Explorer, attempt to
>> delete an expired backup. If that succeeds then go to Sharing Violation
>> section.
>> 4. Log onto NT with an account that is an administrator and use Explorer
>> to
>> look at the Properties|Security of the folder (where the backups reside)
>> and ensure the SQLServerAgent startup account has Full Control. If the
>> SQLServerAgent startup account is LocalSystem, then the account to
>> consider
>> is SYSTEM.
>> 5. In NT, if an account is a member of an NT group, and if that group
>> has
>> Access is Denied, then that account will have Access is Denied, even if
>> that account is also a member of the Administrators group. Thus you may
>> need to check group permissions (if the Startup Account is a member of a
>> group).
>> 6. Keep in mind that permissions (by default) are inherited from a
>> parent
>> folder. Thus, if the backups are stored in C:\bak, and if someone had
>> denied permission to the SQLServerAgent startup account for C:\, then
>> C:\bak will inherit access is denied.
>> Sharing violation:
>> This is likely to be rooted in a timing issue, with the most likely
>> cause
>> being another scheduled process (such as NT Backup or Anti-Virus
>> software)
>> having the backup file open at the time when the SQLServerAgent (i.e.,
>> the
>> maintenance plan job) tried to delete it.
>> 1. Download filemon and handle from www.sysinternals.com.
>> 2. I am not sure whether filemon can be scheduled, or you might be able
>> to
>> use NT scheduling services to start filemon just before the maintenance
>> plan job is started, but the filemon log can become very large, so it
>> would
>> be best to start it some short time before the maintenance plan starts.
>> 3. Inspect the filemon log for another process that has that backup file
>> open (if your lucky enough to have started filemon before this other
>> process grabs the backup folder), and inspect the log for the results
>> when
>> the SQLServerAgent agent attempts to open that same file.
>> 4. Schedule the job or that other process to do their work at different
>> times.
>> 5. You can use the handle utility if you are around at the time when the
>> job is scheduled to run.
>> If the backup files are going to a \\share or a mapped drive (as opposed
>> to
>> local drive), then you will need to modify the above (with respect to
>> where
>> the tests and utilities are run).
>> Finally, inspection of the maintenance plan's history report might be
>> useful.
>> Thanks,
>> Bill Hollinshead
>> Microsoft, SQL Server
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message
>> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000
>> MMC. The backup works perfectly fine, but it does not delete the backup
>> files scheduled to cleanup files older then a week. Any suggestions
>> where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>>
>>
>
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
>> The backup works perfectly fine, but it does not delete the backup files
>> scheduled to cleanup files older then a week. Any suggestions where to
>> look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at MS:
>>
>> -- Log files don't delete --
>> This is likely to be either a permissions problem or a sharing violation
>> problem. The maintenance plan is run as a job, and jobs are run by the
>> SQLServerAgent service.
>> Permissions:
>> 1. Determine the startup account for the SQLServerAgent service
>> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
>> account is the security context for jobs, and thus the maintenance plan.
>> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
>> account) then skip step 3.
>> 3. On that box, log onto NT as that account. Using Explorer, attempt to
>> delete an expired backup. If that succeeds then go to Sharing Violation
>> section.
>> 4. Log onto NT with an account that is an administrator and use Explorer to
>> look at the Properties|Security of the folder (where the backups reside)
>> and ensure the SQLServerAgent startup account has Full Control. If the
>> SQLServerAgent startup account is LocalSystem, then the account to consider
>> is SYSTEM.
>> 5. In NT, if an account is a member of an NT group, and if that group has
>> Access is Denied, then that account will have Access is Denied, even if
>> that account is also a member of the Administrators group. Thus you may
>> need to check group permissions (if the Startup Account is a member of a
>> group).
>> 6. Keep in mind that permissions (by default) are inherited from a parent
>> folder. Thus, if the backups are stored in C:\bak, and if someone had
>> denied permission to the SQLServerAgent startup account for C:\, then
>> C:\bak will inherit access is denied.
>> Sharing violation:
>> This is likely to be rooted in a timing issue, with the most likely cause
>> being another scheduled process (such as NT Backup or Anti-Virus software)
>> having the backup file open at the time when the SQLServerAgent (i.e., the
>> maintenance plan job) tried to delete it.
>> 1. Download filemon and handle from www.sysinternals.com.
>> 2. I am not sure whether filemon can be scheduled, or you might be able to
>> use NT scheduling services to start filemon just before the maintenance
>> plan job is started, but the filemon log can become very large, so it would
>> be best to start it some short time before the maintenance plan starts.
>> 3. Inspect the filemon log for another process that has that backup file
>> open (if your lucky enough to have started filemon before this other
>> process grabs the backup folder), and inspect the log for the results when
>> the SQLServerAgent agent attempts to open that same file.
>> 4. Schedule the job or that other process to do their work at different
>> times.
>> 5. You can use the handle utility if you are around at the time when the
>> job is scheduled to run.
>> If the backup files are going to a \\share or a mapped drive (as opposed to
>> local drive), then you will need to modify the above (with respect to where
>> the tests and utilities are run).
>> Finally, inspection of the maintenance plan's history report might be
>> useful.
>> Thanks,
>> Bill Hollinshead
>> Microsoft, SQL Server
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
>> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
>> week. Any suggestions where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually deleted backups for around
>> 40 databases.
>> Thanks a lot.
>> Imran.
>>
>|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>> Thanks for the link and the details. It cannot be a permissions issue or
>> a sharing violation since I have the same issue on two servers, the
>> development server and the live server both don't delete backup files.
>> I ran the maintenance plan right now and there wasn't any other process
>> holding the backup files. but still the files were not deleted. The
>> backup log did not indicate any errors relating to deletion of files,
>> rather it does not even show the attempt to delete the older files.
>> Any clues ?
>> Imran.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below KB might help:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>>
>> Also, check out below great troubleshooting suggestions from Bill H at
>> MS:
>>
>> -- Log files don't delete --
>> This is likely to be either a permissions problem or a sharing violation
>> problem. The maintenance plan is run as a job, and jobs are run by the
>> SQLServerAgent service.
>> Permissions:
>> 1. Determine the startup account for the SQLServerAgent service
>> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
>> This
>> account is the security context for jobs, and thus the maintenance plan.
>> 2. If SQLServerAgent is started using LocalSystem (as opposed to a
>> domain
>> account) then skip step 3.
>> 3. On that box, log onto NT as that account. Using Explorer, attempt to
>> delete an expired backup. If that succeeds then go to Sharing Violation
>> section.
>> 4. Log onto NT with an account that is an administrator and use Explorer
>> to
>> look at the Properties|Security of the folder (where the backups reside)
>> and ensure the SQLServerAgent startup account has Full Control. If the
>> SQLServerAgent startup account is LocalSystem, then the account to
>> consider
>> is SYSTEM.
>> 5. In NT, if an account is a member of an NT group, and if that group
>> has
>> Access is Denied, then that account will have Access is Denied, even if
>> that account is also a member of the Administrators group. Thus you may
>> need to check group permissions (if the Startup Account is a member of a
>> group).
>> 6. Keep in mind that permissions (by default) are inherited from a
>> parent
>> folder. Thus, if the backups are stored in C:\bak, and if someone had
>> denied permission to the SQLServerAgent startup account for C:\, then
>> C:\bak will inherit access is denied.
>> Sharing violation:
>> This is likely to be rooted in a timing issue, with the most likely
>> cause
>> being another scheduled process (such as NT Backup or Anti-Virus
>> software)
>> having the backup file open at the time when the SQLServerAgent (i.e.,
>> the
>> maintenance plan job) tried to delete it.
>> 1. Download filemon and handle from www.sysinternals.com.
>> 2. I am not sure whether filemon can be scheduled, or you might be able
>> to
>> use NT scheduling services to start filemon just before the maintenance
>> plan job is started, but the filemon log can become very large, so it
>> would
>> be best to start it some short time before the maintenance plan starts.
>> 3. Inspect the filemon log for another process that has that backup file
>> open (if your lucky enough to have started filemon before this other
>> process grabs the backup folder), and inspect the log for the results
>> when
>> the SQLServerAgent agent attempts to open that same file.
>> 4. Schedule the job or that other process to do their work at different
>> times.
>> 5. You can use the handle utility if you are around at the time when the
>> job is scheduled to run.
>> If the backup files are going to a \\share or a mapped drive (as opposed
>> to
>> local drive), then you will need to modify the above (with respect to
>> where
>> the tests and utilities are run).
>> Finally, inspection of the maintenance plan's history report might be
>> useful.
>> Thanks,
>> Bill Hollinshead
>> Microsoft, SQL Server
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message
>> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000
>> MMC. The backup works perfectly fine, but it does not delete the backup
>> files scheduled to cleanup files older then a week. Any suggestions
>> where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>>
>>
>
Backups files not deleted with maintenance plan
Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works
> perfectly fine, but it does not delete the backup files scheduled to clean
up files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually delete
d backups for around 40
> databases.
> Thanks a lot.
> Imran.
>[/vbcol]|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>|||Do you have any databases in simple recovery mode included in the plan? I've
seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the m
aint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...[
vbcol=seagreen]
> Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since
> I have the same issue on two servers, the development server and the live
server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process ho
lding the backup files.
> but still the files were not deleted. The backup log did not indicate any
errors relating to
> deletion of files, rather it does not even show the attempt to delete the
older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works
> perfectly fine, but it does not delete the backup files scheduled to clean
up files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually delete
d backups for around 40
> databases.
> Thanks a lot.
> Imran.
>[/vbcol]|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>|||Do you have any databases in simple recovery mode included in the plan? I've
seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the m
aint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...[
vbcol=seagreen]
> Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since
> I have the same issue on two servers, the development server and the live
server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process ho
lding the backup files.
> but still the files were not deleted. The backup log did not indicate any
errors relating to
> deletion of files, rather it does not even show the attempt to delete the
older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>
Saturday, February 25, 2012
Backups - 2000 -> 2005
If I create a backup in SQL Server 2000 can I restore that database to SQL
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
Server 2005?
If not, is there some tool to convert a 2K DB to 2005?
Thanks."Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
Yes.
Note it's one way. You can't restore a 2005 backup to 2000.
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>|||Yes, you can restore a SQL Server 2000 backup to a 2005 server. You can also
detach from SQL Server 2000 and attach to 2005. However, you can not do the
same from SQL Server 2005 to 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Don Miller" wrote:
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
>|||Thanks. That saved me an hour or two looking through BOL.
"Don Miller" <nospam@.nospam.com> wrote in message
news:uyA8LekcGHA.4108@.TK2MSFTNGP03.phx.gbl...
> If I create a backup in SQL Server 2000 can I restore that database to SQL
> Server 2005?
> If not, is there some tool to convert a 2K DB to 2005?
> Thanks.
>
backups
We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather than
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
Dan D.
You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else posted.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished on
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:
> We use Veritas for backups on SS2000. Right now we do backups using the sql
> server agent. Would there be any problem doing just a file backup rather than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware related
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and get
> the data backed up. The MDF and LDF files are in use, as someone else posted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, then
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the market
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
server agent. Would there be any problem doing just a file backup rather than
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
Dan D.
You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else posted.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished on
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:
> We use Veritas for backups on SS2000. Right now we do backups using the sql
> server agent. Would there be any problem doing just a file backup rather than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.
|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware related
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and get
> the data backed up. The MDF and LDF files are in use, as someone else posted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, then
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the market
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
backups
I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.you need to identify your app requirements, retention resources availabilities, disaster recovery processes (if exist, else - develop them based on business needs), and then tailor your backup strategy to the above. looks like you inherited the box(-s) from someone else and nothing is documented.|||yep, nothing is documented abt the existing backups,
How do i know the info?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.you need to identify your app requirements, retention resources availabilities, disaster recovery processes (if exist, else - develop them based on business needs), and then tailor your backup strategy to the above. looks like you inherited the box(-s) from someone else and nothing is documented.|||yep, nothing is documented abt the existing backups,
How do i know the info?
backups
We use Veritas for backups on SS2000. Right now we do backups using the sql
server agent. Would there be any problem doing just a file backup rather tha
n
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
--
Dan D.You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else poste
d.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished o
n
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:
> We use Veritas for backups on SS2000. Right now we do backups using the sq
l
> server agent. Would there be any problem doing just a file backup rather t
han
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the fil
e
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware relate
d
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
--
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and g
et
> the data backed up. The MDF and LDF files are in use, as someone else pos
ted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, the
n
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the mark
et
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished
on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
>
server agent. Would there be any problem doing just a file backup rather tha
n
whatever the sql server agent does?
If we restored from the file backup, I'm guessing we would restore the file
then do a detach/attach to get the restored file in place. Or would doing
just a file backup miss something?
Thanks,
--
Dan D.You can't use file backup on a SQL Server database file. While it is being
written to it is not guaranteed to be in a internally consistent state, and
even Veritas and other file backup software can backup open files, the file
can be corrupted from a SQL Server point of view. When you use SQL Server
backup, SQL Server will make sure that it is in a consistent state before
making the backup.
Jacco Schalkwijk
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Hi,
If you need to backup the MDF and LDF directly then:-
1. Detach the database <sp_detach_db>
2. Copy the MDF, LDF and NDF for the database to the backup folder
3. Attach back the databasee <SP_attach_db>
While required you could attach the file to a database in any SQL Server
using sp_attach_db command.
Thanks
Hari
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FD6EAE71-85D7-4AE9-918A-D504D29761D1@.microsoft.com...
> We use Veritas for backups on SS2000. Right now we do backups using the
> sql
> server agent. Would there be any problem doing just a file backup rather
> than
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the
> file
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||I've personally never cared for the Veritas agent in backing up SQL
databases. There are several good methods available for backing up SQL
data.. However, you cannot simply run a file backup on a SQL server and get
the data backed up. The MDF and LDF files are in use, as someone else poste
d.
To use the file backup method, you must use a SQL backup to disk, then
backup the backup files. That is, let SQL dump a backup file to DISK, then
use Veritas to backup that file.
I currently manage 40 SQL Servers and use this method. I have had mixed
results with the Veritas agent. There are some other products on the market
to assist with file based backups, such as Red Gate SQL Backup or SQL
Litespeed. They compress the backups to disk.
An easy way to get started with disk based backups is to go through the
database maintenance wizard and have it walk you through configuring and
scheduling a backup. Just remember to make sure your backups are finished o
n
SQL before Veritas starts it's backup.
Hope this helps.
Bryan
"Dan D." wrote:
> We use Veritas for backups on SS2000. Right now we do backups using the sq
l
> server agent. Would there be any problem doing just a file backup rather t
han
> whatever the sql server agent does?
> If we restored from the file backup, I'm guessing we would restore the fil
e
> then do a detach/attach to get the restored file in place. Or would doing
> just a file backup miss something?
> Thanks,
> --
> Dan D.|||Thanks everyone for your responses. That helps a lot.
Bryan,
I tried LiteSpeed once but it wasn't any faster (which I expected it to be)
and according to our sysadmin it didn't compress the files any smaller than
Veritas.
We've had a lot of problems too with Veritas. I'm not in charge of the
backups so I'm not sure if the problems are Veritas related, hardware relate
d
or something else but it really is a pain. I backup our most critical
databases to another server via Sql Server.
Thanks,
--
Dan D.
"Bryan Ivie" wrote:
[vbcol=seagreen]
> I've personally never cared for the Veritas agent in backing up SQL
> databases. There are several good methods available for backing up SQL
> data.. However, you cannot simply run a file backup on a SQL server and g
et
> the data backed up. The MDF and LDF files are in use, as someone else pos
ted.
> To use the file backup method, you must use a SQL backup to disk, then
> backup the backup files. That is, let SQL dump a backup file to DISK, the
n
> use Veritas to backup that file.
> I currently manage 40 SQL Servers and use this method. I have had mixed
> results with the Veritas agent. There are some other products on the mark
et
> to assist with file based backups, such as Red Gate SQL Backup or SQL
> Litespeed. They compress the backups to disk.
> An easy way to get started with disk based backups is to go through the
> database maintenance wizard and have it walk you through configuring and
> scheduling a backup. Just remember to make sure your backups are finished
on
> SQL before Veritas starts it's backup.
> Hope this helps.
> Bryan
>
> "Dan D." wrote:
>
Sunday, February 19, 2012
Backup/Restore Script
am new at this and I am stuck...
I am trying to backup an entire database and restore it on the same SQL
server with a different name.
I have this in the Query Analyzer
BACKUP DATABASE merchant
TO DISK = 'c:\merchant.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\merchant.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\merchant.bak'
WITH MOVE 'merchant' TO 'c:\testdb.mdf',
MOVE 'merchant_log' TO 'c:\testdb.ldf'
GO
I get the following error
Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
13.
Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5
Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
Can anyone help with this?
I want to copy the entire database called MERCHANT to another databaseWell that because it looks like your Data files logical name is
'Struky3_Data' and the Log files logical name is really 'Struky3_Log'. So
your restore script needs to specify those for the Logical names.
Andrew J. Kelly SQL MVP
"Brian Branco" <bbranco@.bncconsulting.com> wrote in message
news:OTAtoi5HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> am new at this and I am stuck...
> I am trying to backup an entire database and restore it on the same SQL
> server with a different name.
> I have this in the Query Analyzer
> BACKUP DATABASE merchant
> TO DISK = 'c:\merchant.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'c:\merchant.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\merchant.bak'
> WITH MOVE 'merchant' TO 'c:\testdb.mdf',
> MOVE 'merchant_log' TO 'c:\testdb.ldf'
> GO
> I get the following error
> Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
> 13.
> Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
> BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5
> Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 5
> RESTORE DATABASE is terminating abnormally.
>
> Can anyone help with this?
> I want to copy the entire database called MERCHANT to another database
>
>
I am trying to backup an entire database and restore it on the same SQL
server with a different name.
I have this in the Query Analyzer
BACKUP DATABASE merchant
TO DISK = 'c:\merchant.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\merchant.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\merchant.bak'
WITH MOVE 'merchant' TO 'c:\testdb.mdf',
MOVE 'merchant_log' TO 'c:\testdb.ldf'
GO
I get the following error
Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
13.
Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5
Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
Can anyone help with this?
I want to copy the entire database called MERCHANT to another databaseWell that because it looks like your Data files logical name is
'Struky3_Data' and the Log files logical name is really 'Struky3_Log'. So
your restore script needs to specify those for the Logical names.
Andrew J. Kelly SQL MVP
"Brian Branco" <bbranco@.bncconsulting.com> wrote in message
news:OTAtoi5HFHA.3376@.TK2MSFTNGP14.phx.gbl...
> am new at this and I am stuck...
> I am trying to backup an entire database and restore it on the same SQL
> server with a different name.
> I have this in the Query Analyzer
> BACKUP DATABASE merchant
> TO DISK = 'c:\merchant.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'c:\merchant.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\merchant.bak'
> WITH MOVE 'merchant' TO 'c:\testdb.mdf',
> MOVE 'merchant_log' TO 'c:\testdb.ldf'
> GO
> I get the following error
> Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file
> 13.
> Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13.
> BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5
> Logical file 'merchant' is not part of database 'TestDB'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 5
> RESTORE DATABASE is terminating abnormally.
>
> Can anyone help with this?
> I want to copy the entire database called MERCHANT to another database
>
>
Backup/Restore *.mdf
I have a program, which connects to the SQLServer 2005 *.mdf file. I
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStrin
g);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStr
ing);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>|||Are you 1005 certain that you have connected to the correct SQL Server insta
nce and that you have
spelled the database name correctly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStr
ing);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStrin
g);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStr
ing);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>|||Are you 1005 certain that you have connected to the correct SQL Server insta
nce and that you have
spelled the database name correctly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionStr
ing);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
Backup/Restore *.mdf
I have a program, which connects to the SQLServer 2005 *.mdf file. I
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK = 'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK => 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>|||Are you 1005 certain that you have connected to the correct SQL Server instance and that you have
spelled the database name correctly?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK => 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK = 'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK => 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>|||Are you 1005 certain that you have connected to the correct SQL Server instance and that you have
spelled the database name correctly?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegroups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Default.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK => 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
Backup/Restore *.mdf
I have a program, which connects to the SQLServer 2005 *.mdf file. I
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Defaul t.GreenTourConnectionString);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?
Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegr oups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Defaul t.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
need to add backup/restore database function to my program.
Try to do:
SqlConnection greenTourConnection = new
SqlConnection(GreenTour.Properties.Settings.Defaul t.GreenTourConnectionString);
SqlCommand hotelList = greenTourConnection.CreateCommand();
hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
'C:\GreenTourDataBase.bak'";
greenTourConnection.Open();
hotelList.ExecuteNonQuery();
greenTourConnection.Close();
Says: System.Data.SqlClient.SqlException: Could not locate entry in
sysdatabases for database 'GreenTourDataBase'. No entry found with
that name. Make sure that the name is entered correctly.
But the name is printed correctly. Can anyone please help?
Hi
Can you show us printed script?
"radandri" <radandri@.gmail.com> wrote in message
news:1171875188.289749.322570@.p10g2000cwp.googlegr oups.com...
>I have a program, which connects to the SQLServer 2005 *.mdf file. I
> need to add backup/restore database function to my program.
> Try to do:
> SqlConnection greenTourConnection = new
> SqlConnection(GreenTour.Properties.Settings.Defaul t.GreenTourConnectionString);
> SqlCommand hotelList = greenTourConnection.CreateCommand();
> hotelList.CommandText = @."BACKUP DATABASE GreenTourDataBase TO DISK =
> 'C:\GreenTourDataBase.bak'";
> greenTourConnection.Open();
> hotelList.ExecuteNonQuery();
> greenTourConnection.Close();
> Says: System.Data.SqlClient.SqlException: Could not locate entry in
> sysdatabases for database 'GreenTourDataBase'. No entry found with
> that name. Make sure that the name is entered correctly.
> But the name is printed correctly. Can anyone please help?
>
Thursday, February 16, 2012
Backup/DR configuration using mirroring and log shipping
I'm trying to come up with a db backup/Disaster Recovery plan using SQL
Server 2005 Mirroring and Log shipping and I need help understanding which o
f
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Server 2005 Mirroring and Log shipping and I need help understanding which o
f
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG
Monday, February 13, 2012
Backup with SQL Engine
Hi all
I awant to backup the .MDF and .LDF files that are managed by a SQL
Server 7 Engine (the installation uses only the Engine not the full
SQL Server 7 installation).
How can I do that?
thanks!
Hi,
Use BACKUP DATABASE comamnd to backup the database. Refer SQL server books
online for
BACKUP database command.
Thanks
Hari
MCDBA
"steve simpson" <simpsonst3@.comcast.net> wrote in message
news:4107d689.1017269476@.msnews.microsoft.com...
> Hi all
> I awant to backup the .MDF and .LDF files that are managed by a SQL
> Server 7 Engine (the installation uses only the Engine not the full
> SQL Server 7 installation).
> How can I do that?
> thanks!
>
|||You can backup a database with the BACKUP command (see Books Online for the
syntax), and backup the resulting backup file to a backup medium, like tape.
If you don't have the graphical SQL Server client tools installed on your
server, you can use the osql command line tool to execute SQL statement.
More information about this you can also find in Books Online.
Jacco Schalkwijk
SQL Server MVP
"steve simpson" <simpsonst3@.comcast.net> wrote in message
news:4107d689.1017269476@.msnews.microsoft.com...
> Hi all
> I awant to backup the .MDF and .LDF files that are managed by a SQL
> Server 7 Engine (the installation uses only the Engine not the full
> SQL Server 7 installation).
> How can I do that?
> thanks!
>
I awant to backup the .MDF and .LDF files that are managed by a SQL
Server 7 Engine (the installation uses only the Engine not the full
SQL Server 7 installation).
How can I do that?
thanks!
Hi,
Use BACKUP DATABASE comamnd to backup the database. Refer SQL server books
online for
BACKUP database command.
Thanks
Hari
MCDBA
"steve simpson" <simpsonst3@.comcast.net> wrote in message
news:4107d689.1017269476@.msnews.microsoft.com...
> Hi all
> I awant to backup the .MDF and .LDF files that are managed by a SQL
> Server 7 Engine (the installation uses only the Engine not the full
> SQL Server 7 installation).
> How can I do that?
> thanks!
>
|||You can backup a database with the BACKUP command (see Books Online for the
syntax), and backup the resulting backup file to a backup medium, like tape.
If you don't have the graphical SQL Server client tools installed on your
server, you can use the osql command line tool to execute SQL statement.
More information about this you can also find in Books Online.
Jacco Schalkwijk
SQL Server MVP
"steve simpson" <simpsonst3@.comcast.net> wrote in message
news:4107d689.1017269476@.msnews.microsoft.com...
> Hi all
> I awant to backup the .MDF and .LDF files that are managed by a SQL
> Server 7 Engine (the installation uses only the Engine not the full
> SQL Server 7 installation).
> How can I do that?
> thanks!
>
Subscribe to:
Posts (Atom)