full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

sql

Full Text Searching

I've assumed responsibilty for a sqlserver 2000 that has many databases and has only 1 database that uses full text searching. That 1 application now wishes for me to update the noise words file to remove the single characters.

As far as I can tell, no other application is using the full text searching since I don't see any catalogs created for any of them.

So, if no other databases and applications in this sqlserver have catalogs created for them, can I safely assume that changing the noise words file will not impact any of them even if the other databases do have text fields in some tables?First, which version of SQL are you using? What edition?

Second, thanks for your question; I went Googling for some answers and found some other links to (unrelated) stuff that I found useful.

Third, I don't have the "textbook" answer (I'm still googling), but I found this link on M$ site: http://support.microsoft.com/kb/905617. I hope it helps.

I'll continue to dig around.

Regards,

hmscott|||thanks for any help!
It's sql server 2000 standard edition - SP4.sql

Full Text Query Starts Slow

I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.

The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.

Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.

I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.

I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.

Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.

In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql

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 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

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.

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

full server backup

is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all databases
> > in
> > the current server , DTS packages and everything else in one single backup
> > file?
> >
> >
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:
> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > No. The backup restore mechanism works at database level, not server
> > level.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > is that possible to make a full server backup which include all
databases
> > in
> > the current server , DTS packages and everything else in one single
backup
> > file?
> >
> >
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
> > Hi,Vyas
> > He can use undocumented stored procedure to do that
> >
> >
> > EXEC sp_MSforeachdb
> > 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> > ''pubs'')
> > BEGIN
> > DECLARE @.sql varchar(1000);
> > set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK => > ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> > EXEC (@.sql)
> > END'
> >
> > BTW. What is about .NET examples on your site that you have promised us some
> > time ago :-)
> >
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > > No. The backup restore mechanism works at database level, not server
> > > level.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> > >
> > >
> > > "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> > > news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> > > is that possible to make a full server backup which include all databases
> > > in
> > > the current server , DTS packages and everything else in one single backup
> > > file?
> > >
> > >
> >
> >
> >|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they. :)|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>> If you save DTS packages to SQL Server, then they get stored in the msdb
>> database.
>> --
>> HTH,
>> Vyas, MVP (SQL Server)
>> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>>
>> <unc27932@.yahoo.com> wrote in message
>> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
>> I agree - how does this grab the DTS packages? The DTS packages are
>> saved to the server - not to a database. The backup command only
>> references a specific database.
>>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:
> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||The closest thing to that would be to stop the server and to zip all the
database files (mdf and ldf) to 1 archive.
--
Cheers,
Wojtek
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?|||It would be much easier and supported to just do regular database backups of each database, all
going to the same backup file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wojtek Garwol" <garwol@.usunto.poczta.fm> wrote in message news:dcb893$r69$1@.nemesis.news.tpi.pl...
> The closest thing to that would be to stop the server and to zip all the database files (mdf and
> ldf) to 1 archive.
> --
> Cheers,
> Wojtek
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
>> is that possible to make a full server backup which include all databases in
>> the current server , DTS packages and everything else in one single backup
>> file?
>

full server backup

is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?No. The backup restore mechanism works at database level, not server level.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:

> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us so
me
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>|||Yes, if your DTS packages are stored in SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:

> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup[vbcol=seagreen]
>
>|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
>|||where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:

> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>|||sysdtspackages
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...[vbcol=seagreen]
> where exactly can i c it un the msdb?
> "Narayana Vyas Kondreddi" wrote:
>|||sysdtspackages
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:7C78600B-9414-48A8-9B9B-2E8312D9A223@.microsoft.com...
where exactly can i c it un the msdb?
"Narayana Vyas Kondreddi" wrote:

> If you save DTS packages to SQL Server, then they get stored in the msdb
> database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> <unc27932@.yahoo.com> wrote in message
> news:1122560371.634115.202710@.z14g2000cwz.googlegroups.com...
> I agree - how does this grab the DTS packages? The DTS packages are
> saved to the server - not to a database. The backup command only
> references a specific database.
>
>

full server backup

is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
No. The backup restore mechanism works at database level, not server level.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
is that possible to make a full server backup which include all databases in
the current server , DTS packages and everything else in one single backup
file?
|||Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||Uri, your script is still creating multiple backup files, which is not what
the OP wanted. Anyway, you could still write multiple database backups to a
single backup file though.
Anyway, I just wanted to point out that you cannot run a single command, and
create a single backup with everything that is there on the server.
Regarding the .Net code samples, hopefully before end of this year :-)
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uqh7DY3kFHA.3256@.TK2MSFTNGP12.phx.gbl...
Hi,Vyas
He can use undocumented stored procedure to do that
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
BTW. What is about .NET examples on your site that you have promised us some
time ago :-)
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
> No. The backup restore mechanism works at database level, not server
> level.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oren101" <Oren101@.discussions.microsoft.com> wrote in message
> news:AAA53155-0EED-47FA-8CCD-08BF0DF3A3E9@.microsoft.com...
> is that possible to make a full server backup which include all databases
> in
> the current server , DTS packages and everything else in one single backup
> file?
>
|||is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:

> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us some
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
>
>
|||Yes, if your DTS packages are stored in SQL Server.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:0937D676-861F-4FFB-9174-156131BFC4E8@.microsoft.com...
is that code will backup the DTS packeges too?
Regards.
OreN101
"Uri Dimant" wrote:

> Hi,Vyas
> He can use undocumented stored procedure to do that
>
> EXEC sp_MSforeachdb
> 'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
> ''pubs'')
> BEGIN
> DECLARE @.sql varchar(1000);
> set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
> ''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
> EXEC (@.sql)
> END'
> BTW. What is about .NET examples on your site that you have promised us
some[vbcol=seagreen]
> time ago :-)
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:ubAFNs2kFHA.2472@.TK2MSFTNGP15.phx.gbl...
databases[vbcol=seagreen]
backup
>
>
|||how come? the script above make backups to the databases in the sql server
instance ,not to any other objects(like DTS)?
"Oren101" wrote:
[vbcol=seagreen]
> is that code will backup the DTS packeges too?
> Regards.
> OreN101
> "Uri Dimant" wrote:
|||I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||If you save DTS packages to SQL Server, then they get stored in the msdb
database.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122560371.634115.202710@.z14g2000cwz.googlegr oups.com...
I agree - how does this grab the DTS packages? The DTS packages are
saved to the server - not to a database. The backup command only
references a specific database.
|||Well, now there's something I didn't realize. These newsgroups are
helpful aren't they.

Full scripts

Dear folks,
I’m wondering how do I obtain full scripts of all the current databases in
a
Sql Server of an automated way instead of choose one in one out from EM.
Does anyone knows or is awared of any mechanism for that?
Thanks in advance and regards,Hi
You may want to look at using DMO to do this for an example see
http://www.nigelrivett.net/DMOScriptAllDatabases.html
John
"Enric" wrote:

> Dear folks,
> I’m wondering how do I obtain full scripts of all the current databases
in a
> Sql Server of an automated way instead of choose one in one out from EM.
> Does anyone knows or is awared of any mechanism for that?
> Thanks in advance and regards,
>|||Eric
There is no such mechanism provided by MS. You'll have to write your own
script to go throu EM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:469BD8F7-FBAC-4DB2-A20A-AEAF1A9CD565@.microsoft.com...
> Dear folks,
> Im wondering how do I obtain full scripts of all the current databases in
> a
> Sql Server of an automated way instead of choose one in one out from EM.
> Does anyone knows or is awared of any mechanism for that?
> Thanks in advance and regards,
>|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:469BD8F7-FBAC-4DB2-A20A-AEAF1A9CD565@.microsoft.com...
> Dear folks,
> I'm wondering how do I obtain full scripts of all the current databases in
a
> Sql Server of an automated way instead of choose one in one out from EM.
> Does anyone knows or is awared of any mechanism for that?
> Thanks in advance and regards,
>

Full Replication

I am about to replicate my sql databases. I have two servers, and two SAN units. I have one front end server direct attacted to each SAN unit. I want to use replication. Using the SQL 2000 replication software, will both of my databases have eactly th
e same data? I want to make an exact image for disaster recovery purposes. Will SQL2000 builtin replication work for me or do I have to purchase a third party software?
Thank You,
Joe
you can replicate all user data using merge replication, but only tables
with primary keys with transactional replication.
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:65B98550-D089-485F-8B87-EDFB7C645AED@.microsoft.com...
> I am about to replicate my sql databases. I have two servers, and two SAN
units. I have one front end server direct attacted to each SAN unit. I
want to use replication. Using the SQL 2000 replication software, will both
of my databases have eactly the same data? I want to make an exact image
for disaster recovery purposes. Will SQL2000 builtin replication work for
me or do I have to purchase a third party software?
> Thank You,
> Joe

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , N
OUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and th
e work you do requires
this amount of log space. My guess is index rebuilds. If you do require this
amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT ,
NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT
,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

Full recovery mode and large log

Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
Regards, InspektorDerrick
Most probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick

full backup and tran log backup

Hi:

I have 30 databases on sql server 2005 that I need to do a full backup every morning at 7:00 and tran log backup every 30 minutes until 7:00 PM. If I create a maintenance plan for a backup using the wizard I have the option of starting a full backup at 7 am and then an option of doing tran log backups every hour using a different schedule. I plan on selecting the option to create a different folder for every database. I just need to confirm that in this way the way to restore the data would be

1. to restore a full backup

2. apply all the tran logs depending on the time they want to recover back to.

I just think this is the easiest approach to have 30 databases on the same backup scheme instead of creating a separate backup device for each database and doing a full backup on that device and appending all tran logs to that device which means just 1 bak file versus the above strategy with a number of tran log files. Please advise.

Thanks

Correct...you restore your last full backup and then apply all the logs in order since your last full backup.

With the options you selected, you will have separate backup files for each database and each backup of that database. I kind of lost you on the separate backup device and appending everything to that device. You certainly have a lot of options and different routes you could take other than just those two. And even more options if you want to script your own and get away from maintenance plans. But your general approach is fine. Devices, folders, naming etc should be what you are comfortable with. Backup locations should be what will provide you integrity, stability with the backups. Frequency of the backups depends on the business recovery needs.

-Sue