Hi there,
I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it.
I get the following error from query analyser when trying to access full text indexing:
Server: Msg 7607, Level 17, State 1, Line 1
Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc).
In my event view I see error messages:
Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
Any advice appreciated.
Matt,
Can you pass us the exact sql query?Were you trying to search for 'as'? If
so, isnt that included in the ignored words list?Also, whats the SQLServer
version and service pack level?
I apologise for replying back with another set of questions but little more
detail would help !
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.
|||Hi,
Are you using SQL 2000.
If yes, then install SP3 for SQL Server 2000. I had a identical problem
after database restore some time back and got resolved after applying SP3.
Note:
Even if you have SP3 , try to reinstall and check the FULLTEXT searrh.
Thanks
Hari
MCDBA
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.
|||i think you'll need to recreate and repopulate the fulltext catalogs. a database backup does not contain the fulltext catalogs.
http://support.microsoft.com/default...b;en-us;240867
http://support.microsoft.com/default...b;en-us;236789
Matt wrote:
> Hi there,
> I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it.
> I get the following error from query analyser when trying to access full text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
> Any advice appreciated.
|||Hi and thanks for the replies;
It is SQL 7.0 with service pack 4. I am unsure whether I installed full text search AFTER applying the service pack 4 or not.
My example used an ignore word but other keywords fail as well (usually sql will tell you if it is an ignored word).
I have already rebuilt and repopulated the catelogues but still have the problem.
Should I reinstall full text search and if so how do I uninstall a Full text search and then reinstall? OR should I reinstall Service pack 4.
thanks again,
Matt
2012年3月19日星期一
Full text indexing not working!
Hi there,
I recently had to rebuild my database server and recreate databases from bac
kups. All is well except full text indexing is not working although I did in
stall it.
I get the following error from query analyser when trying to access full tex
t indexing:
Server: Msg 7607, Level 17, State 1, Line 1
Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, ta
ble ID 1029578706 with search condition 'as' failed with unknown result (449
ccdc).
In my event view I see error messages:
Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Cl
XXX not registered .
Any advice appreciated.Matt,
Can you pass us the exact sql query?Were you trying to search for 'as'? If
so, isnt that included in the ignored words list?Also, whats the SQLServer
version and service pack level?
I apologise for replying back with another set of questions but little more
detail would help !
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||Hi,
Are you using SQL 2000.
If yes, then install SP3 for SQL Server 2000. I had a identical problem
after database restore some time back and got resolved after applying SP3.
Note:
Even if you have SP3 , try to reinstall and check the FULLTEXT searrh.
Thanks
Hari
MCDBA
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||i think you'll need to recreate and repopulate the fulltext catalogs. a dat
abase backup does not contain the fulltext catalogs.
http://support.microsoft.com/defaul...kb;en-us;240867
http://support.microsoft.com/defaul...kb;en-us;236789
Matt wrote:
> Hi there,
> I recently had to rebuild my database server and recreate databases from b
ackups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full t
ext indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result (4
49ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.
I recently had to rebuild my database server and recreate databases from bac
kups. All is well except full text indexing is not working although I did in
stall it.
I get the following error from query analyser when trying to access full tex
t indexing:
Server: Msg 7607, Level 17, State 1, Line 1
Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, ta
ble ID 1029578706 with search condition 'as' failed with unknown result (449
ccdc).
In my event view I see error messages:
Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Cl
XXX not registered .
Any advice appreciated.Matt,
Can you pass us the exact sql query?Were you trying to search for 'as'? If
so, isnt that included in the ignored words list?Also, whats the SQLServer
version and service pack level?
I apologise for replying back with another set of questions but little more
detail would help !
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||Hi,
Are you using SQL 2000.
If yes, then install SP3 for SQL Server 2000. I had a identical problem
after database restore some time back and got resolved after applying SP3.
Note:
Even if you have SP3 , try to reinstall and check the FULLTEXT searrh.
Thanks
Hari
MCDBA
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||i think you'll need to recreate and repopulate the fulltext catalogs. a dat
abase backup does not contain the fulltext catalogs.
http://support.microsoft.com/defaul...kb;en-us;240867
http://support.microsoft.com/defaul...kb;en-us;236789
Matt wrote:
> Hi there,
> I recently had to rebuild my database server and recreate databases from b
ackups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full t
ext indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result (4
49ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.
Full text indexing not working!
Hi there
I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it
I get the following error from query analyser when trying to access full text indexing
Server: Msg 7607, Level 17, State 1, Line
Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc)
In my event view I see error messages
Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
Any advice appreciated.Matt,
Can you pass us the exact sql query?Were you trying to search for 'as'? If
so, isnt that included in the ignored words list?Also, whats the SQLServer
version and service pack level?
I apologise for replying back with another set of questions but little more
detail would help !
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||Hi,
Are you using SQL 2000.
If yes, then install SP3 for SQL Server 2000. I had a identical problem
after database restore some time back and got resolved after applying SP3.
Note:
Even if you have SP3 , try to reinstall and check the FULLTEXT searrh.
Thanks
Hari
MCDBA
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||i think you'll need to recreate and repopulate the fulltext catalogs. a database backup does not contain the fulltext catalogs.
http://support.microsoft.com/default.aspx?scid=kb;en-us;240867
http://support.microsoft.com/default.aspx?scid=kb;en-us;236789
Matt wrote:
> Hi there,
> I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it.
> I get the following error from query analyser when trying to access full text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
> Any advice appreciated.|||Hi and thanks for the replies;
It is SQL 7.0 with service pack 4. I am unsure whether I installed full text search AFTER applying the service pack 4 or not.
My example used an ignore word but other keywords fail as well (usually sql will tell you if it is an ignored word).
I have already rebuilt and repopulated the catelogues but still have the problem.
Should I reinstall full text search and if so how do I uninstall a Full text search and then reinstall? OR should I reinstall Service pack 4.
thanks again,
Matt
I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it
I get the following error from query analyser when trying to access full text indexing
Server: Msg 7607, Level 17, State 1, Line
Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc)
In my event view I see error messages
Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
Any advice appreciated.Matt,
Can you pass us the exact sql query?Were you trying to search for 'as'? If
so, isnt that included in the ignored words list?Also, whats the SQLServer
version and service pack level?
I apologise for replying back with another set of questions but little more
detail would help !
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||Hi,
Are you using SQL 2000.
If yes, then install SP3 for SQL Server 2000. I had a identical problem
after database restore some time back and got resolved after applying SP3.
Note:
Even if you have SP3 , try to reinstall and check the FULLTEXT searrh.
Thanks
Hari
MCDBA
"Matt" <matt@.community.nospam> wrote in message
news:3FF82A86-0B99-490C-AB5F-17E6A6A4E8F0@.microsoft.com...
> Hi there,
> I recently had to rebuild my database server and recreate databases from
backups. All is well except full text indexing is not working although I did
install it.
> I get the following error from query analyser when trying to access full
text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184,
table ID 1029578706 with search condition 'as' failed with unknown result
(449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 -
Class not registered .
> Any advice appreciated.|||i think you'll need to recreate and repopulate the fulltext catalogs. a database backup does not contain the fulltext catalogs.
http://support.microsoft.com/default.aspx?scid=kb;en-us;240867
http://support.microsoft.com/default.aspx?scid=kb;en-us;236789
Matt wrote:
> Hi there,
> I recently had to rebuild my database server and recreate databases from backups. All is well except full text indexing is not working although I did install it.
> I get the following error from query analyser when trying to access full text indexing:
> Server: Msg 7607, Level 17, State 1, Line 1
> Search on full-text catalog 'FULL TEXT INDEX SEARCH' for database ID 184, table ID 1029578706 with search condition 'as' failed with unknown result (449ccdc).
> In my event view I see error messages:
> Unable to initialize project <SQLServer SQL0018400005>. Error: 80040154 - Class not registered .
> Any advice appreciated.|||Hi and thanks for the replies;
It is SQL 7.0 with service pack 4. I am unsure whether I installed full text search AFTER applying the service pack 4 or not.
My example used an ignore word but other keywords fail as well (usually sql will tell you if it is an ignored word).
I have already rebuilt and repopulated the catelogues but still have the problem.
Should I reinstall full text search and if so how do I uninstall a Full text search and then reinstall? OR should I reinstall Service pack 4.
thanks again,
Matt
2012年3月7日星期三
Full Sql Server Backup
Hi,
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
ThanksStop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server
,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services becaus
e
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was change
d
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
>|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...[vbcol=seagreen]
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
>|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Serve
r.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
>
>
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
ThanksStop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server
,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services becaus
e
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was change
d
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
>|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...[vbcol=seagreen]
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
>|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Serve
r.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
>
>
Full Sql Server Backup
Hi,
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
ThanksStop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services because
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was changed
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
> > Hi,
> >
> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> > Manager for my backups. First i do a full backup of my files on the server,
> > then i use an Agent to create a backup of the databases. My backup works
> > fine. But when i do a restore i can't start my MSSQLSERVER services because
> > my database Master isn't there, and i can't restore my db Master because
> > MSSQLSERVER isn't running.
> > I got a few errors before that, but it was the folder name that was changed
> > to 8.3 naming in the registry, i fixed it.
> > Anyone have an idea how a can do a full backup of my server so that i can
> > restore it completly to a new server?
> >
> > Thanks|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
>> Stop SQL Services and perform file backup. Restore file backups with sql
>> services on new server stopped. Clean up.
>> "Phil" wrote:
>> > Hi,
>> >
>> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
>> > Manager for my backups. First i do a full backup of my files on the
>> > server,
>> > then i use an Agent to create a backup of the databases. My backup
>> > works
>> > fine. But when i do a restore i can't start my MSSQLSERVER services
>> > because
>> > my database Master isn't there, and i can't restore my db Master
>> > because
>> > MSSQLSERVER isn't running.
>> > I got a few errors before that, but it was the folder name that was
>> > changed
>> > to 8.3 naming in the registry, i fixed it.
>> > Anyone have an idea how a can do a full backup of my server so that i
>> > can
>> > restore it completly to a new server?
>> >
>> > Thanks|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Server.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> > Ok that's on solution, but is there a way to do it without stopping the
> > services?
> >
> >
> > "Jeffrey K. Ericson" wrote:
> >
> >> Stop SQL Services and perform file backup. Restore file backups with sql
> >> services on new server stopped. Clean up.
> >>
> >> "Phil" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> >> > Manager for my backups. First i do a full backup of my files on the
> >> > server,
> >> > then i use an Agent to create a backup of the databases. My backup
> >> > works
> >> > fine. But when i do a restore i can't start my MSSQLSERVER services
> >> > because
> >> > my database Master isn't there, and i can't restore my db Master
> >> > because
> >> > MSSQLSERVER isn't running.
> >> > I got a few errors before that, but it was the folder name that was
> >> > changed
> >> > to 8.3 naming in the registry, i fixed it.
> >> > Anyone have an idea how a can do a full backup of my server so that i
> >> > can
> >> > restore it completly to a new server?
> >> >
> >> > Thanks
>
>
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
ThanksStop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services because
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was changed
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
> > Hi,
> >
> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> > Manager for my backups. First i do a full backup of my files on the server,
> > then i use an Agent to create a backup of the databases. My backup works
> > fine. But when i do a restore i can't start my MSSQLSERVER services because
> > my database Master isn't there, and i can't restore my db Master because
> > MSSQLSERVER isn't running.
> > I got a few errors before that, but it was the folder name that was changed
> > to 8.3 naming in the registry, i fixed it.
> > Anyone have an idea how a can do a full backup of my server so that i can
> > restore it completly to a new server?
> >
> > Thanks|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
>> Stop SQL Services and perform file backup. Restore file backups with sql
>> services on new server stopped. Clean up.
>> "Phil" wrote:
>> > Hi,
>> >
>> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
>> > Manager for my backups. First i do a full backup of my files on the
>> > server,
>> > then i use an Agent to create a backup of the databases. My backup
>> > works
>> > fine. But when i do a restore i can't start my MSSQLSERVER services
>> > because
>> > my database Master isn't there, and i can't restore my db Master
>> > because
>> > MSSQLSERVER isn't running.
>> > I got a few errors before that, but it was the folder name that was
>> > changed
>> > to 8.3 naming in the registry, i fixed it.
>> > Anyone have an idea how a can do a full backup of my server so that i
>> > can
>> > restore it completly to a new server?
>> >
>> > Thanks|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Server.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
> > Ok that's on solution, but is there a way to do it without stopping the
> > services?
> >
> >
> > "Jeffrey K. Ericson" wrote:
> >
> >> Stop SQL Services and perform file backup. Restore file backups with sql
> >> services on new server stopped. Clean up.
> >>
> >> "Phil" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> >> > Manager for my backups. First i do a full backup of my files on the
> >> > server,
> >> > then i use an Agent to create a backup of the databases. My backup
> >> > works
> >> > fine. But when i do a restore i can't start my MSSQLSERVER services
> >> > because
> >> > my database Master isn't there, and i can't restore my db Master
> >> > because
> >> > MSSQLSERVER isn't running.
> >> > I got a few errors before that, but it was the folder name that was
> >> > changed
> >> > to 8.3 naming in the registry, i fixed it.
> >> > Anyone have an idea how a can do a full backup of my server so that i
> >> > can
> >> > restore it completly to a new server?
> >> >
> >> > Thanks
>
>
Full Sql Server Backup
Hi,
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
Thanks
Stop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services because
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was changed
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks
|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...[vbcol=seagreen]
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Server.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
>
>
I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
Manager for my backups. First i do a full backup of my files on the server,
then i use an Agent to create a backup of the databases. My backup works
fine. But when i do a restore i can't start my MSSQLSERVER services because
my database Master isn't there, and i can't restore my db Master because
MSSQLSERVER isn't running.
I got a few errors before that, but it was the folder name that was changed
to 8.3 naming in the registry, i fixed it.
Anyone have an idea how a can do a full backup of my server so that i can
restore it completly to a new server?
Thanks
Stop SQL Services and perform file backup. Restore file backups with sql
services on new server stopped. Clean up.
"Phil" wrote:
> Hi,
> I'm trying to make a Full backup of my Sql Server. I use Tivoli Storage
> Manager for my backups. First i do a full backup of my files on the server,
> then i use an Agent to create a backup of the databases. My backup works
> fine. But when i do a restore i can't start my MSSQLSERVER services because
> my database Master isn't there, and i can't restore my db Master because
> MSSQLSERVER isn't running.
> I got a few errors before that, but it was the folder name that was changed
> to 8.3 naming in the registry, i fixed it.
> Anyone have an idea how a can do a full backup of my server so that i can
> restore it completly to a new server?
> Thanks
|||Ok that's on solution, but is there a way to do it without stopping the
services?
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> Stop SQL Services and perform file backup. Restore file backups with sql
> services on new server stopped. Clean up.
> "Phil" wrote:
|||Hi
That is not a supported way of backing up and restoring a SQL Server
installation.
You must backup your databases to disk via the backup command in SQL Server.
Thne backup the resulatnt backup files to tape.
On the other SQL Server, restore the backups using the restore command.
To restore master, you must follow "Restoring the master Database from a
Current Backup" in books online.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...[vbcol=seagreen]
> Ok that's on solution, but is there a way to do it without stopping the
> services?
>
> "Jeffrey K. Ericson" wrote:
|||Ok what your telling me is that it's impossible to do a full backup a my SQL
server, and a backup of the database, and to restore to a new server(with
nothing on)?
Is that correct?
I would need to backup my DBs, then install a new SQL Server and then
restore my DB's?
Thanks
Philip
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> That is not a supported way of backing up and restoring a SQL Server
> installation.
> You must backup your databases to disk via the backup command in SQL Server.
> Thne backup the resulatnt backup files to tape.
> On the other SQL Server, restore the backups using the restore command.
> To restore master, you must follow "Restoring the master Database from a
> Current Backup" in books online.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:69E8A7C7-CDA0-4A1B-9863-74C23B855133@.microsoft.com...
>
>
2012年2月26日星期日
Full Log Setting
I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?
Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>
|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do
>
>.
>
|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> message
> you
> Do
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?
Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>
|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do
>
>.
>
|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> message
> you
> Do
Full Log Setting
I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
>> I've confused myself. If you're doing log backups and
you
>> have of course the database option set to FULL, then how
>> can your logs ever shrink? I understand that after the
>> log backup happens technically the logs can shrink w/o
>> risk of losing data in the logs. But what actually
>> shrinks these logs? Autoshrink won't do it, will it?
Do
>> you have run something manuall?
>
>.
>|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> >--Original Message--
> >Please refer to Tibor's link
> >
> >http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> >
> >
> >--
> >Jeff Duncan
> >MCDBA, MCSE+I
> >"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> >> I've confused myself. If you're doing log backups and
> you
> >> have of course the database option set to FULL, then how
> >> can your logs ever shrink? I understand that after the
> >> log backup happens technically the logs can shrink w/o
> >> risk of losing data in the logs. But what actually
> >> shrinks these logs? Autoshrink won't do it, will it?
> Do
> >> you have run something manuall?
> >>
> >
> >
> >.
> >
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
>> I've confused myself. If you're doing log backups and
you
>> have of course the database option set to FULL, then how
>> can your logs ever shrink? I understand that after the
>> log backup happens technically the logs can shrink w/o
>> risk of losing data in the logs. But what actually
>> shrinks these logs? Autoshrink won't do it, will it?
Do
>> you have run something manuall?
>
>.
>|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> >--Original Message--
> >Please refer to Tibor's link
> >
> >http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> >
> >
> >--
> >Jeff Duncan
> >MCDBA, MCSE+I
> >"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> >> I've confused myself. If you're doing log backups and
> you
> >> have of course the database option set to FULL, then how
> >> can your logs ever shrink? I understand that after the
> >> log backup happens technically the logs can shrink w/o
> >> risk of losing data in the logs. But what actually
> >> shrinks these logs? Autoshrink won't do it, will it?
> Do
> >> you have run something manuall?
> >>
> >
> >
> >.
> >
Full Log Setting
I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do[vbcol=seagreen]
>
>.
>|||There's no shrink code on that page. The code is only there to prove that wh
en you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the file
s. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But
please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.p
hx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
>
> message
> you
> Do
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?
>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do[vbcol=seagreen]
>
>.
>|||There's no shrink code on that page. The code is only there to prove that wh
en you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the file
s. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But
please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.p
hx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
>
> message
> you
> Do
2012年2月24日星期五
Full backup invisible to the next differential backup
Hi
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...
all
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...
quote:
> For instance:
> 1. On Monday a full backup is created
> 2. On Tuesday another full backup is automatically created by a 3rd-party
> application
> 3. On Wednesday a differential backup is created.
> We want this diff backup to ignore the Tuesday full backup and store
all
quote:
> changes since the Monday one.
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes
quote:|||Why not take the database offline, copy the database files, put the database
> Dallara,
>
> "Dallara" <someone@.microsoft.com> wrote in message
3rd-party[QUOTE]
> all
>
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...
quote:
> The application has an automated process that
> 1 - gets exclusive access to the DB
> 2 - back it up
> 3 - does whatever needs to be done
> if 3 fails the DB is restored, otherwise the backup can be discarded
> 4 - release exclusive access to DB
> The idea is not to interfere with the client maintenance plans and backup
> policy (if they have one).
> It's basically the same idea of the COPY BACKUP for the NT backup.
>
>
> "James Hokes" <noemail@.noway.com> wrote in message
> This is not possible.
> What's the point of the third party backup if it is disregarded?
> James Hokes
>
> 3rd-party
store[QUOTE]
>
Full backup invisible to the next differential backup
Hi
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...
> For instance:
> 1. On Monday a full backup is created
> 2. On Tuesday another full backup is automatically created by a 3rd-party
> application
> 3. On Wednesday a differential backup is created.
> We want this diff backup to ignore the Tuesday full backup and store
all
> changes since the Monday one.
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes
> Dallara,
>
> "Dallara" <someone@.microsoft.com> wrote in message
> > For instance:
> > 1. On Monday a full backup is created
> > 2. On Tuesday another full backup is automatically created by a
3rd-party
> > application
> > 3. On Wednesday a differential backup is created.
> > We want this diff backup to ignore the Tuesday full backup and store
> all
> > changes since the Monday one.
>|||Why not take the database offline, copy the database files, put the database
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...
> The application has an automated process that
> 1 - gets exclusive access to the DB
> 2 - back it up
> 3 - does whatever needs to be done
> if 3 fails the DB is restored, otherwise the backup can be discarded
> 4 - release exclusive access to DB
> The idea is not to interfere with the client maintenance plans and backup
> policy (if they have one).
> It's basically the same idea of the COPY BACKUP for the NT backup.
>
>
> "James Hokes" <noemail@.noway.com> wrote in message
> This is not possible.
> What's the point of the third party backup if it is disregarded?
> James Hokes
>
> > Dallara,
> >
> >
> > "Dallara" <someone@.microsoft.com> wrote in message
> > > For instance:
> > > 1. On Monday a full backup is created
> > > 2. On Tuesday another full backup is automatically created by a
> 3rd-party
> > > application
> > > 3. On Wednesday a differential backup is created.
> > > We want this diff backup to ignore the Tuesday full backup and
store
> > all
> > > changes since the Monday one.
> >
>
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...
> For instance:
> 1. On Monday a full backup is created
> 2. On Tuesday another full backup is automatically created by a 3rd-party
> application
> 3. On Wednesday a differential backup is created.
> We want this diff backup to ignore the Tuesday full backup and store
all
> changes since the Monday one.
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes
> Dallara,
>
> "Dallara" <someone@.microsoft.com> wrote in message
> > For instance:
> > 1. On Monday a full backup is created
> > 2. On Tuesday another full backup is automatically created by a
3rd-party
> > application
> > 3. On Wednesday a differential backup is created.
> > We want this diff backup to ignore the Tuesday full backup and store
> all
> > changes since the Monday one.
>|||Why not take the database offline, copy the database files, put the database
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...
> The application has an automated process that
> 1 - gets exclusive access to the DB
> 2 - back it up
> 3 - does whatever needs to be done
> if 3 fails the DB is restored, otherwise the backup can be discarded
> 4 - release exclusive access to DB
> The idea is not to interfere with the client maintenance plans and backup
> policy (if they have one).
> It's basically the same idea of the COPY BACKUP for the NT backup.
>
>
> "James Hokes" <noemail@.noway.com> wrote in message
> This is not possible.
> What's the point of the third party backup if it is disregarded?
> James Hokes
>
> > Dallara,
> >
> >
> > "Dallara" <someone@.microsoft.com> wrote in message
> > > For instance:
> > > 1. On Monday a full backup is created
> > > 2. On Tuesday another full backup is automatically created by a
> 3rd-party
> > > application
> > > 3. On Wednesday a differential backup is created.
> > > We want this diff backup to ignore the Tuesday full backup and
store
> > all
> > > changes since the Monday one.
> >
>
full backup and transaction log truncate
Hi,
I'm
about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>
I'm
about how the backup system works in SQL Server. I'm usingthe BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be
by the statement in BOL I actually hopethat BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm
about how the backup system works in SQL Server. I'm using> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be
by the statement in BOL I actually hope that> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:
> Hi,
> I'm
about how the backup system works in SQL Server. I'm using> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be
by the statement in BOL I actually hope> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>
Full and Differential Backups
Does the full and differential backups have to be in the same location or can I do a once a week full backup on one drive and everyday differential backups on a different drive?
Thanks
Hi,
if you store your backup local, it's still better to have them on the same drive - by watching your systemmonitor.
But it doesn' matter wehre you store your backups.
|||As such there is no restriction. you can have your full and differential back any where. If you are taking the backup in local machine , your backup is not fully fault tollerent. YOu should have either a network path or tape drive to store the backup.
Madhu
|||Another thought is to do your backups to a local disk (which one makes no difference) and then archive this of to secondary storage (network share from another machine, tape, etc.)
The reason for this is that backups and restores to/from a local disk will be the fastest method. So, for your backups, and in the event of a restore due to a single bad disk (or single bad decision 
Full and Diff Backups
If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 hour while another 5 Gig is added in transactions to the log file, The Backup should complete leaving only the new 5 Gig of transactions while the previous 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup statement.
Does the same action take place on the transaction log during a differential backup? Does a differential backup include every transaction on the log file upto and including the backup statement?
>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>
he backup. Basically backing up everything upto and including the Backup statement.
Does the same action take place on the transaction log during a differential backup? Does a differential backup include every transaction on the log file upto and including the backup statement?
>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>
Full and Diff Backups
If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 h
our while another 5 Gig is added in transactions to the log file, The Backup
should complete leaving only the new 5 Gig of transactions while the previo
us 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup sta
tement.
Does the same action take place on the transaction log during a differential
backup? Does a differential backup include every transaction on the log fil
e upto and including the backup statement?>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>
our while another 5 Gig is added in transactions to the log file, The Backup
should complete leaving only the new 5 Gig of transactions while the previo
us 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup sta
tement.
Does the same action take place on the transaction log during a differential
backup? Does a differential backup include every transaction on the log fil
e upto and including the backup statement?>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>
full and diff backup - sql2005
hello...
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
> > hello...
> > I've got problem with backups on sql2005
> > script make backup at saturday with full backup and at monday script
> > runs diff backup. On one of few sqlservers2005 diff backups doesn't
> > run because because error: "a current database backup does not
> > exist" . But I know full backup was executed corectly on saturday.
> > In backup file , that is used for diff backup exists record for
> > saturday's full backup. But server can't recognize full backup for
> > db.
> > When I run full backup again, it run sucessfully and diff backup
> > too.
> > This error is only on one server (named instance), other servers are
> > ok.
> > Any hints for this mystery ?
> > Thanks for any help.- Skry=BB citovan=FD text -
> - Zobrazi=BB citovan=FD text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.|||Perhaps you do some type of snapshot backup on the machine and this interferes in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that case. Just a thought...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
> > hello...
> > I've got problem with backups on sql2005
> > script make backup at saturday with full backup and at monday script
> > runs diff backup. On one of few sqlservers2005 diff backups doesn't
> > run because because error: "a current database backup does not
> > exist" . But I know full backup was executed corectly on saturday.
> > In backup file , that is used for diff backup exists record for
> > saturday's full backup. But server can't recognize full backup for
> > db.
> > When I run full backup again, it run sucessfully and diff backup
> > too.
> > This error is only on one server (named instance), other servers are
> > ok.
> > Any hints for this mystery ?
> > Thanks for any help.- Skry=BB citovan=FD text -
> - Zobrazi=BB citovan=FD text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.|||Perhaps you do some type of snapshot backup on the machine and this interferes in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that case. Just a thought...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
full and diff backup - sql2005
hello...
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
>
>
> - Zobrazi=BB citovan=FD text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.|||Perhaps you do some type of snapshot backup on the machine and this interfer
es in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that c
ase. Just a thought...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
>
>
> - Zobrazi=BB citovan=FD text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.|||Perhaps you do some type of snapshot backup on the machine and this interfer
es in some way with the
diff backups? Consider disabling the SQL Server VSS writer service in that c
ase. Just a thought...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
full and diff backup - sql2005
hello...
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.
Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
> - Zobrazi citovan text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.
|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>
|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.
|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
I've got problem with backups on sql2005
script make backup at saturday with full backup and at monday script
runs diff backup. On one of few sqlservers2005 diff backups doesn't
run because because error: "a current database backup does not
exist" . But I know full backup was executed corectly on saturday.
In backup file , that is used for diff backup exists record for
saturday's full backup. But server can't recognize full backup for
db.
When I run full backup again, it run sucessfully and diff backup
too.
This error is only on one server (named instance), other servers are
ok.
Any hints for this mystery ?
Thanks for any help.
Well, try doing a manual full and see if it resolves.
I'm not sure if the backup is looking at logs in msdb, or if it writes a
flag, but a manual full will deal with both. In short, I wouldn't put too
much energy into it, unless the simple solution doesn't work ...
<oto.pakozdy@.gmail.com> wrote in message
news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
|||On 27. Nov, 16:57 h., "Jay" <s...@.nospam.org> wrote:
> Well, try doing a manual full and see if it resolves.
> I'm not sure if the backup is looking at logs in msdb, or if it writes a
> flag, but a manual full will deal with both. In short, I wouldn't put too
> much energy into it, unless the simple solution doesn't work ...
> <oto.pako...@.gmail.com> wrote in message
> news:2398bfcd-fa45-4a4f-ade4-915159aebbea@.d21g2000prf.googlegroups.com...
>
>
>
>
> - Zobrazi citovan text -
Now I altered db_backupoperator role for MSDB by user running backup
script. soi I will see if it help.
|||I usually run this to get the history of backups for a specific database.
Try it and see what information it returns. Chage database name and pay
atention to the type column.
select backup_set_id, bs.media_set_id, database_creation_date,
backup_start_date, backup_finish_date,
type, database_name, physical_device_name
from backupset bs join backupmediafamily bmf
on bs.media_set_id = bmf.media_set_id
where database_name = 'your_db'
-- and type <> 'L'
order by backup_start_date desc
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"oto.pakozdy@.gmail.com" wrote:
> hello...
> I've got problem with backups on sql2005
> script make backup at saturday with full backup and at monday script
> runs diff backup. On one of few sqlservers2005 diff backups doesn't
> run because because error: "a current database backup does not
> exist" . But I know full backup was executed corectly on saturday.
> In backup file , that is used for diff backup exists record for
> saturday's full backup. But server can't recognize full backup for
> db.
> When I run full backup again, it run sucessfully and diff backup
> too.
> This error is only on one server (named instance), other servers are
> ok.
> Any hints for this mystery ?
> Thanks for any help.
>
|||I found solution on google
Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
manual to started . So when NTbackup run backup it breaks backup
chain because vss make own db backups.
So when I turn off SQL VSS Writer everyting works fine.
ntbackup backup files without backuping db and sql backup chain
full,diff is not break.
Thank for your hints.
|||Hello, I think i meet the same problem. but you said you have find the
resolution on google. Could you please give me the link that describe the
problem?
Thanks,
Gil
"oto.pakozdy@.gmail.com" wrote:
> I found solution on google
> Problem is MSSQL SP2 changed startup state SQL Server VSS Writer from
> manual to started . So when NTbackup run backup it breaks backup
> chain because vss make own db backups.
> So when I turn off SQL VSS Writer everyting works fine.
> ntbackup backup files without backuping db and sql backup chain
> full,diff is not break.
>
> Thank for your hints.
>
订阅：
博文 (Atom)