Best Recovery plan for MergeReplication

hi all,
i established merge replication between two servers with no synch option.i
dont have any idea of how to restore it original is it fails.how can i
restore both publisher and subscriber with out any data loss.what is the
recovery plan i have to use.
your help is appreciated
thanks
reddy
thanks paul,
my doubt is the replication is stopped without synchronising the data from
publisher to subscriber.the data is available at publisher and not in
subscriber.in this case which steps i have to follow.just i can take a backup
of publisher and restore it in the original and start connecting to
subscriber.is it works.please help
thanks
reddy
"Paul Ibison" wrote:

> Reddy,
> you can backup your publisher, distributor and
> subscribers. The recovery mode is not important in the
> sense that any one will work for replication, and the
> usual considerations apply - eg if you want a more
> granular backup strategy, full recovery mode and
> transaction log backups are used.
> After you restore a publisher's backup, depending on your
> situation, you might want to reinitialize or you may want
> to synchronize your publication database immediately with
> a subscription database that has the latest data, and
> attempt to recover any changes synchronized with that
> replica but not included in the most recent publication
> database backup of publication database transaction log
> backup.
> Assuming you haven't changed the replication
> configuration, you don't need to restore the distribution
> database to a consistent point in time.
> BTW BOL has a good section on this : Strategies for
> Backing Up and Restoring Merge Replication.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Reddy,
you mention that 'replication is stopped'. What error
message do you receive when you run the merge agent. This
is the crucial bit and it may not be necessary/helpful to
restore a backup.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best practices for backup and restore of large replicated databases

Hello,
I am working on a project where we have a large database that is replicated.
My task is to create a backup/restore plan for this project.
I know the basics for backup/restore, but are looking for best practices and
experiences from you who have done this already on a large scale.
Both publisher and subscribers are allowed to do updates. Restore time at
subscribers have to be short and therefore I am looking for a solution where
both publisher and subscribers are backup up.
Thanks in advance for any pointers to white papers, books, emails, etc.
Best regards,
Vemund
Hi Vemund,
Thanks for your post and I would like to take ownership of this thread and
help you with this issue.
Based on my scope, I understood that you would like to find the best
practise about large scale replicated database backup and restore strategy.
Have I understood you? Correct me if I was wrong.
First of all, please understood that it is hard to say what is the best
practice for your porject as I am not sure how much important is the data
for you and how much data lose could be acceptable. You should discussed
this with your users and find the best strategy for your project.
Generally speaking, here are some general recommendations on database
disaster recovery strategy:
1. Make regular backups that reflect your backup strategy. Ask yourselves
the question - how much data can I afford to lose? (1 min, 1 hour, 1 day
worth). Then ensure that the frequency of the backups reflects the answer
to that question.
2. Make backup to a different media from where the database file reside.
That way you protect against a central point of failure. You can consider
backing up to tape or to another disk (share). Consider moving the backed
up data off-site to another location to protect against disasters like
fires, floods, hurricanes, tornadoes, etc.
3. Test your backups on a regular basis by ensuring they are restore-able
on another system. Then run DBCC CHECKDB on those to ensure the original
database or the backup are not damaged.
4. Keep in mind that the data stored in your database(s) may be one of the
most valuable assets your organization has!
In addition, please review the following information in SQL Books On-Line
"Designing a Backup and Restore Strategy"
Here are some precautions a database administrator should take to ensure
the safety of the data:
1. It is always a good idea to ensure that your backup strategy is
sufficient to recover from a catastrophic failure. Offsite storage and
other precautions are appropriate.
2. Test the database restore operation in a secondary or test database on a
frequent basis.
3. Ensure that any caching devices can handle all failure situations (power
outage, bad sectors, bad drives, system outage, lockups, power spike, and
so forth).
4. Ensure that your caching device:
-- Has integrated battery backup.
-- Can reissue writes on power up.
-- Can be fully disabled if necessary.
-- Handles bad sector re-mapping realtime.
5. Enable torn page detection; it has little performance impact.
6. Configure RAID drives allowing for a hot swap of a bad disk drive, if
possible.
7. Use newer caching controllers that allow addition of more disk space
without
restarting the OS. This can be an ideal solution.
Check the following which covers some of this in detail:
SQL Server capacity and storage guide:
http://www.microsoft.com/technet/pro...n/sqlops6.mspx
Windows backup guide:
http://www.microsoft.com/technet/pro...aintain/backup
rest/br04.mspx
Moreover, here is one more WebCast for you, which will discuss Microsoft
SQL Server database files, the different kinds of database backup options
that are available in SQL Server 2000, and some of the factors to consider
when you design a backup strategy. It will talk about best practices for
backing up and restoring database files. It will review some case studies
and talk about how to troubleshoot problems with backup and restore
operations.
Support WebCast: Microsoft SQL Server 2000: Understanding Backup and Restore
http://support.microsoft.com/?id=329833
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Vemund,
I am just checking on your progress regarding the information that was sent
you! I wonder whether my suggestion is useful or you would like to receive
more information on this.
If you encounter any difficulty, please do not hesitate to let me know.
Please post here and let me know the status of your issue. Looking forward
to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql

Best practice-backup-

I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.
Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer
|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.
|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>
|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script
|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserv...sql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net... [vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:

Best practice-backup-

I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
[vbcol=seagreen]
> script|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK =
'g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK =
'g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK =
'g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
[vbcol=seagreen]
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlser...ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log
backup sequence.
If not, set the database to simple recovery mode and you don't have to do TR
UNCATE_ONLY.
Also, your scripts only includes the system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...[vbcol=seagreen]
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK =
> 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK =
> 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK =
> 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>

Best practice-backup-

I am confused because I see two options on the backup plan of SQL 2000.
Under SQL Server Enterprise Manager - SQL Agent:
1.Security Backup (copia de seguridad)
Here I can setup a simple copy, at first it allows me only to select the
path and filename. Then now I was able to right click and select the
schedulle and all.
2.Database Maintaince Plan (Planes de mantenimento de la base)
Here I can do more then just backup. And what I like best is that it has
option to automtacly remove files older the x day plus the schedulle.
Is there a link a good SQL backup plan.Hi,
In the security backup u can do backup of one database at one time. and
u cannot schedule to delete ur old database backup files.
In the Database maintinance plane u can sehedule back up on all the
database in ur sql server and u can also delete old file accoring to
days.
U can also do log shipping in database maintinance plan.
For more informatiom read books online
from
killer|||Hi,
For production databases it is always recommended to write your own script
for Full backup and Transaction log backup
and schedule it susing SQL Agent Jobs.
But for development/QA servers you could use the maintenance plan which is
very easy to maintain. But I have seen few issues
with maintenence plan occasionaly. Thats the reason i am not recommending
Maintenance plan for production env.
Thanks
Hari
SQL Server MVP
"API Conektia" <api@.online.nospam> wrote in message
news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>I am confused because I see two options on the backup plan of SQL 2000.
> Under SQL Server Enterprise Manager - SQL Agent:
> 1.Security Backup (copia de seguridad)
> Here I can setup a simple copy, at first it allows me only to select the
> path and filename. Then now I was able to right click and select the
> schedulle and all.
> 2.Database Maintaince Plan (Planes de mantenimento de la base)
> Here I can do more then just backup. And what I like best is that it has
> option to automtacly remove files older the x day plus the schedulle.
> Is there a link a good SQL backup plan.|||Any chance you could post sample scripts for best practise?
Thanks
Paul
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SUkjO8rFHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> For production databases it is always recommended to write your own script
> for Full backup and Transaction log backup
> and schedule it susing SQL Agent Jobs.
> But for development/QA servers you could use the maintenance plan which is
> very easy to maintain. But I have seen few issues
> with maintenence plan occasionaly. Thats the reason i am not recommending
> Maintenance plan for production env.
> Thanks
> Hari
> SQL Server MVP
> "API Conektia" <api@.online.nospam> wrote in message
> news:19z7mqty6g8g.4bq726qi6ysq.dlg@.40tude.net...
>>I am confused because I see two options on the backup plan of SQL 2000.
>> Under SQL Server Enterprise Manager - SQL Agent:
>> 1.Security Backup (copia de seguridad)
>> Here I can setup a simple copy, at first it allows me only to select the
>> path and filename. Then now I was able to right click and select the
>> schedulle and all.
>> 2.Database Maintaince Plan (Planes de mantenimento de la base)
>> Here I can do more then just backup. And what I like best is that it has
>> option to automtacly remove files older the x day plus the schedulle.
>> Is there a link a good SQL backup plan.
>|||Yes, Hari if you could post any samples of scripts.
Well I found something in the net witch I will give it a try.
http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
> script
>> for Full backup|||Now I found 2 ways of doing it thru scripts and really apreciate to know
the best one:
first:
USE Tempdb
GO
SELECT GETDATE()
GO
SELECT @.@.SERVERNAME
GO
BACKUP DATABASE Master TO DISK ='g:\temp\sql\BackupsMaster.bak'
WITH INIT
GO
BACKUP DATABASE MSDB TO DISK ='g:\temp\sql\BackupsMSDB.bak'
WITH INIT
GO
BACKUP DATABASE Model TO DISK ='g:\temp\sql\BackupsModel.bak'
WITH INIT
GO
SELECT GETDATE()
GO
and the other:
-- Start by truncating the logs.
BACKUP LOG MASTER WITH TRUNCATE_ONLY
BACKUP LOG MODEL WITH TRUNCATE_ONLY
BACKUP LOG MSDB WITH TRUNCATE_ONLY
GO
-- Now backup each database in turn.
BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
COMPLETE', NOUNLOAD
BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
COMPLETE'
BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
COMPLETE'
GO
On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
> Yes, Hari if you could post any samples of scripts.
> Well I found something in the net witch I will give it a try.
> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup|||Are you doing regular transaction log backups?
If you are, don't do backup log with TRUNCATE_ONLY as it will break the log backup sequence.
If not, set the database to simple recovery mode and you don't have to do TRUNCATE_ONLY.
Also, your scripts only includes the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"API Conektia" <api@.online.nospam> wrote in message
news:k9lcagxl496e$.6ni4bk2xmuzn$.dlg@.40tude.net...
> Now I found 2 ways of doing it thru scripts and really apreciate to know
> the best one:
> first:
> USE Tempdb
> GO
> SELECT GETDATE()
> GO
> SELECT @.@.SERVERNAME
> GO
> BACKUP DATABASE Master TO DISK => 'g:\temp\sql\BackupsMaster.bak'
> WITH INIT
> GO
> BACKUP DATABASE MSDB TO DISK => 'g:\temp\sql\BackupsMSDB.bak'
> WITH INIT
> GO
> BACKUP DATABASE Model TO DISK => 'g:\temp\sql\BackupsModel.bak'
> WITH INIT
> GO
> SELECT GETDATE()
> GO
>
> and the other:
> -- Start by truncating the logs.
> BACKUP LOG MASTER WITH TRUNCATE_ONLY
> BACKUP LOG MODEL WITH TRUNCATE_ONLY
> BACKUP LOG MSDB WITH TRUNCATE_ONLY
> GO
> -- Now backup each database in turn.
> BACKUP DATABASE MASTER TO DISK='G:\temp\SQL\master.bak' WITH NAME='MASTER
> COMPLETE', NOUNLOAD
> BACKUP DATABASE MODEL TO DISK='G:\temp\SQL\model.bak' WITH NAME='MODEL
> COMPLETE'
> BACKUP DATABASE MSDB TO DISK='G:\temp\SQL\msdb.bak' WITH NAME='MSDB
> COMPLETE'
> GO
>
> On Mon, 5 Sep 2005 15:34:49 +0200, API Conektia wrote:
>> Yes, Hari if you could post any samples of scripts.
>> Well I found something in the net witch I will give it a try.
>> http://www.cryer.co.uk/brian/sqlserver/howtofullw2ksql2000bkp.htm
>> On Fri, 2 Sep 2005 15:30:41 +0100, Paul Cahill wrote:
>> script
>> for Full backup

Best practice for maintenance plans

Hi,
with SQL 2k the maintenance plan wizard proposed defaults for all
maintenance tasks. With SQL 2005 this is gone.
What are the best practices to setup a "normal" SQL 2005 maintenance?
Thanks for any advice
Greg
--
Greg JimsonHi Greg,
You should be installed SSIS (Integration Service) to be able to use it, or
just to install Service Packets would be enough as well.
Maintenance still stands in the Management node in SQL Server 2005. Go to
Maintenance Plans and right click on it.
Click Maintenance Plan Wizard.
--
Ekrem Önsoy
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> with SQL 2k the maintenance plan wizard proposed defaults for all
> maintenance tasks. With SQL 2005 this is gone.
> What are the best practices to setup a "normal" SQL 2005 maintenance?
> Thanks for any advice
> Greg
> --
> Greg Jimson
>|||Hi Ekrem,
thanks for you answer. May I specify my question. What Task need to be
setup? Shrink, History Clean up, etc.?
Thanks in advance
Greg
--
Greg Jimson
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
> Hi Greg,
>
> You should be installed SSIS (Integration Service) to be able to use it,
> or just to install Service Packets would be enough as well.
> Maintenance still stands in the Management node in SQL Server 2005. Go to
> Maintenance Plans and right click on it.
> Click Maintenance Plan Wizard.
> --
> Ekrem Önsoy
>
>
> "Greg Jimson" <nospam@.hotmail.com> wrote in message
> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>|||It depends on what you want to do and your needs.
In the "Select Maintenance Tasks" window at the bottom side you will see the
description box which describes each option when you click on them.
For instance, you schedule "Maintenance Cleanup Task" to remove files left
over from executing a maintenance plan.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT
"Greg Jimson" <nospam@.hotmail.com> wrote in message
news:eqHkn4V7HHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Ekrem,
> thanks for you answer. May I specify my question. What Task need to be
> setup? Shrink, History Clean up, etc.?
> Thanks in advance
> Greg
> --
> Greg Jimson
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:147C4862-4C46-48F8-8440-4A2AB441AFDE@.microsoft.com...
>> Hi Greg,
>>
>> You should be installed SSIS (Integration Service) to be able to use it,
>> or just to install Service Packets would be enough as well.
>> Maintenance still stands in the Management node in SQL Server 2005. Go to
>> Maintenance Plans and right click on it.
>> Click Maintenance Plan Wizard.
>> --
>> Ekrem Önsoy
>>
>>
>> "Greg Jimson" <nospam@.hotmail.com> wrote in message
>> news:%23rL5E1T7HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> with SQL 2k the maintenance plan wizard proposed defaults for all
>> maintenance tasks. With SQL 2005 this is gone.
>> What are the best practices to setup a "normal" SQL 2005 maintenance?
>> Thanks for any advice
>> Greg
>> --
>> Greg Jimson
>>
>

Best Performance Strip setting RAID

We are running SQL6.5 and plan to upgrade to SQL2000. I'm wondering what's the best stripe size for the RAID 5 configuration. 8,16,32 or 64 kb.
The database is 90% used for read actions. Only during night complete refill of data and write actions only for statistics. Any advise is welcome on this subjectSince SQL server pages are 64K the raid stripe settings must also be set to 64K|||SQL 6.5 uses 2k pages and SQL 2000 uses 8k pages.

The stripe size of your RAID drives does NOT have to follow the page size, however I would use a RAID stripe size >= to my page size.

Best backup plan for 24*7 running production database

Hi:
ENVIRONMENT:WIN2000/SQL2000
We need best backup plan for one our 24*7 available
production database growing by 1.2 gb per day..
Any one can tell me the best plan or can recommend any
best site for this?
Thanks in advance
PuruPuru
For large databases, which yours soon will be, most people
would take advantage of differential backups to reduce
impact on the system.
Something like a full backup once a week, and
differentials the other days and regular transaction log
backups as required.
Early in the life of your database you may find towards
the end of the week your differentials are nearly a large
and take as much time as a full backup, due to volume of
changes, as the database gets bigger that will change.
Try to put your backups on seperate disks to your database
and transaction logs to reduce the impact of them running.
Try to schedule them for the quietest time of the day,
even with a 24 by 7 installation you will still have some
times that are quieter than others. (usually when peak
business hours are in the paciffic ocean).
If your database gets into the terra-bytes you may need to
come up with a different strategy.
Hope this helps
Regards
John

Behavior of OPTION

we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
now there is a complaint that these procs are running quite slow. while
trying to figure out the problem for the sluggishness
of the procedure, i noticed that commenting this line produced different
plan while uncommenting this line produced quite a
different one. Also, the usage of indexes varied drastically between these
plans (commented v/s uncommented OPTION clause)
can anybody throw some light on why using or NOT using OPTION would change
the execution plan?
thx in advanceHi
I prefer to remove hints when upgrading significant versions as things like
changes to the Query Optimizer may mean you are not using specific features
and as you have found they may be slower.
It is also a good idea to benchmark the system before and after the upgrade
to make sure that it is performing better!
It also usually a good time to review the code for general "good practices"
such as declaring temporary tables a the start of a procedure, reducing the
use of unnecessary temporary tables, owner prefixes for tables and stored
procedures, correct error and transaction handling etc...
Make sure that indexes and statistics are in place and rebuilt.
You can then look at the code and the query plans for the slower procedures.
John
"paraa" wrote:

> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>|||parasda
Firstly , BOL has a pretty good explanation about all options that you can
use
Actually ,FORCE ORDER option specifies that the join order indicated by the
query syntax is preserved during query optimization. In fact , query
optimizer is free to decide in what order (join) ( believe me, it is smart
enough) to execute the query im more efficient way ,so by using this option
you limit the optimizer to create an efficient execution plan
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:12F9AE7A-C7F4-4228-88C1-C1C5E35FA47B@.microsoft.com...
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>