Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

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 copying between 2000 and 2005

currently built a new server with sql server 2005,
whats the best practise for copying the databases from a sql 2000 machine ?
backup / restore ? - i tried the copy wizard and it kept falling overNot sure if it is best practice. But I have been using Backup/Restore to
upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
fine.
Linchi
"luna" <luna@.themoon.com> wrote in message
news:p4sGf.16419$Fy4.3228@.newsfe4-win.ntli.net...
> currently built a new server with sql server 2005,
> whats the best practise for copying the databases from a sql 2000 machine
> ?
> backup / restore ? - i tried the copy wizard and it kept falling over
>|||"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:evdU6HSLGHA.984@.tk2msftngp13.phx.gbl...
> Not sure if it is best practice. But I have been using Backup/Restore to
> upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
> fine.
> Linchi
>
great i'll try that today!
mark

best practice for copying between 2000 and 2005

currently built a new server with sql server 2005,
whats the best practise for copying the databases from a sql 2000 machine ?
backup / restore ? - i tried the copy wizard and it kept falling overNot sure if it is best practice. But I have been using Backup/Restore to
upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
fine.
Linchi
"luna" <luna@.themoon.com> wrote in message
news:p4sGf.16419$Fy4.3228@.newsfe4-win.ntli.net...
> currently built a new server with sql server 2005,
> whats the best practise for copying the databases from a sql 2000 machine
> ?
> backup / restore ? - i tried the copy wizard and it kept falling over
>|||"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:evdU6HSLGHA.984@.tk2msftngp13.phx.gbl...
> Not sure if it is best practice. But I have been using Backup/Restore to
> upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
> fine.
> Linchi
>
great i'll try that today!
mark

best practice for copying between 2000 and 2005

currently built a new server with sql server 2005,
whats the best practise for copying the databases from a sql 2000 machine ?
backup / restore ? - i tried the copy wizard and it kept falling over
Not sure if it is best practice. But I have been using Backup/Restore to
upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
fine.
Linchi
"luna" <luna@.themoon.com> wrote in message
news:p4sGf.16419$Fy4.3228@.newsfe4-win.ntli.net...
> currently built a new server with sql server 2005,
> whats the best practise for copying the databases from a sql 2000 machine
> ?
> backup / restore ? - i tried the copy wizard and it kept falling over
>
|||"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:evdU6HSLGHA.984@.tk2msftngp13.phx.gbl...
> Not sure if it is best practice. But I have been using Backup/Restore to
> upgrade (or migrate) databases from SQL2000 to SQL2005 and found it work
> fine.
> Linchi
>
great i'll try that today!
mark

Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backu
p
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:

> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though
.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are th
en
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to kee
p
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
>|||Either use the maintenance wizard for this, or you have to do some coding to
create a new backup
files each time you do the backup. Search the archives and you will find suc
h sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backu
p
> give me everything I need to restore a database? How would I get it to bac
kup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse
3
> months ago I will be able to. What qould be the best way to do this?

Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:
> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
> > I am wanting to know some ways to backup my databases. I have 3 I need to
> > backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> > growing. I want to have a backup every night. Will doing just a full backup
> > give me everything I need to restore a database? How would I get it to backup
> > every day but NOT overwrite the previous file, I want to create a new file
> > every night so if for some reason I need to get information off a databse 3
> > months ago I will be able to. What qould be the best way to do this?|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?

Best practice for backups

I am wanting to know some ways to backup my databases. I have 3 I need to
backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
growing. I want to have a backup every night. Will doing just a full backup
give me everything I need to restore a database? How would I get it to backup
every day but NOT overwrite the previous file, I want to create a new file
every night so if for some reason I need to get information off a databse 3
months ago I will be able to. What qould be the best way to do this?
what is the back up topology for your company? what kinds of back up
retention policies do they have? You could make sql back ups, which are then
carted off to a back up server, and depending on the retention policy, you
could feasibly have data from 3 months ago...that is a lot of data to keep
around though...do you really need to be able to go that far back?
"pwverber" wrote:

> I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?
|||3 months is what my manager wants. On the 4th month we will keep only 1 file
and remove the rest from that month. We will have 3 months of backups though.
"Tom Mongold" wrote:
[vbcol=seagreen]
> what is the back up topology for your company? what kinds of back up
> retention policies do they have? You could make sql back ups, which are then
> carted off to a back up server, and depending on the retention policy, you
> could feasibly have data from 3 months ago...that is a lot of data to keep
> around though...do you really need to be able to go that far back?
> "pwverber" wrote:
|||Either use the maintenance wizard for this, or you have to do some coding to create a new backup
files each time you do the backup. Search the archives and you will find such sample code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pwverber" <pwverber@.discussions.microsoft.com> wrote in message
news:72FAC21D-3FA7-4672-9A1F-345210AA94AB@.microsoft.com...
>I am wanting to know some ways to backup my databases. I have 3 I need to
> backup. 1st = 155 MB 2nd = 12 MB 3rd = 5 MB. 2nd one is fairly new and is
> growing. I want to have a backup every night. Will doing just a full backup
> give me everything I need to restore a database? How would I get it to backup
> every day but NOT overwrite the previous file, I want to create a new file
> every night so if for some reason I need to get information off a databse 3
> months ago I will be able to. What qould be the best way to do this?

best option when master db is not available

i want to setup my database to new server machine. i have backup as well as copy of database files (data & log), but i lost my master database from existing server.

so i have 2 ways of having my database

1. restore from backup

2. attach the data files.

which one is best option, when we start afresh on new machine with new master database.

Either method should work. I would prefer the RESTORE.

IF the file location (folder) is different, you may need to add the WITH MOVE option to the RESTORE.

Refer to Books Online, Topic: RESTORE

|||yes both options will work.......i'll advise you to go with restore db from backup but ensure that you start SQL Server in single user mode and then only you can perform restoration..........refer BOL its the best resource|||

thanks for your replies. I have gone thru the BOL.

One thing i need to reconfirm before i proceed, with the loss of master database i lost all the information that the master database hold, in this situation, which of the option will be best.

|||

i feel that there is some confusion in the requirement. ie Whether u r trying to resotre Master Database or User database. To restore a master database of one machine to another machine you have many restriction. OS/SQL SErver Version/Service pack and configuration (if i remember correctly) should be the same. I have also read somewhere that the backup of the same physical machine can ionly be restored(i have never tried this).

To transfer the objects from one server to other there are scripts available. You need to transfer Login/Jobs/DTS. THis is possible through Scripts.

(a) Install new instance of sql server in new machine

(b) transfer the LOgin refer : http://support.microsoft.com/default.aspx/kb/246133

(c) Make script of JOBs and run the script in the destination

(d) use save as option or file object trasfer for DTS

(e) Use Backup /restore for user databases.

Madhu

|||

there should not be any confusion, i want to restore user database.

since my old master db is lost, i lost login, etc. logins i can recreate manually, but i am worried is there is thing else about my User database which is lost along with master db, which i may not recover.

|||

All of the data in the users databases should be intact.

Any Jobs would be in the msdb database, so if you didn't lose msdb, you still have the jobs.

Most likely, the only thing lost is logins.

Best MSSQL management tools

Hi everyone.

I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.

Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)

Cheers~

gmefmax:angel:You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.

Example:
osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'|||You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).

It's not impossible, but I admit that I'm not up to the challenge right now.

Regards,

hmscott|||I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?|||I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.

basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.|||Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?|||I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?

Then use osql.exe instead.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.

I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.

Anybody use PowerShell yet for scripting ?

I keep meaning to get into powershell, but haven't yet.

ps combined with SMO would be a nice combo:

http://www.google.com/search?q=smo+powershell|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.

I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
Thanks
Delimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks
|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:

> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>
|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:
> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> > We have a couple of databases about 5G each and around 300 tables in each
> > databases.We have to keep a yealry backup for 6 years. Also we might be
> > converting to Oracle in about 2 years. If we want to store the SQL Server
> > tables information in a compatible format that would be restorable in a
> > couple of years, what would be the recommended Export format ?
> > Thanks
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>> Delimited ASCII text is just about the only format that never seems to go
>> out of style...
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
>> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>> > We have a couple of databases about 5G each and around 300 tables in
>> > each
>> > databases.We have to keep a yealry backup for 6 years. Also we might be
>> > converting to Oracle in about 2 years. If we want to store the SQL
>> > Server
>> > tables information in a compatible format that would be restorable in a
>> > couple of years, what would be the recommended Export format ?
>> > Thanks

Best Export format for long term compatibility

We have a couple of databases about 5G each and around 300 tables in each
databases.We have to keep a yealry backup for 6 years. Also we might be
converting to Oracle in about 2 years. If we want to store the SQL Server
tables information in a compatible format that would be restorable in a
couple of years, what would be the recommended Export format ?
ThanksDelimited ASCII text is just about the only format that never seems to go
out of style...
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
> We have a couple of databases about 5G each and around 300 tables in each
> databases.We have to keep a yealry backup for 6 years. Also we might be
> converting to Oracle in about 2 years. If we want to store the SQL Server
> tables information in a compatible format that would be restorable in a
> couple of years, what would be the recommended Export format ?
> Thanks|||Adam,
Thanks for the info. But when I choose ASCII Text as an output format it
seems to only pickup one table at a time. Is thre any way to specify all
tables or multiple tables during the Export ?
Thanks
"Adam Machanic" wrote:

> Delimited ASCII text is just about the only format that never seems to go
> out of style...
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
> message news:D55CFF19-39E5-4707-B515-079A7F5EAA4B@.microsoft.com...
>|||My suggestion for ASCII text was really more of a joke than anything
else--suggesting the one format you KNOW will still work. But based on what
you posted, I would just stick with a standard SQL Server backup. You can
always re-install a copy of SQL Server and get the data from there, even if
you switch to Oracle.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:1CA39E77-19FB-47EC-8E22-CBE6348A5647@.microsoft.com...[vbcol=seagreen]
> Adam,
> Thanks for the info. But when I choose ASCII Text as an output format it
> seems to only pickup one table at a time. Is thre any way to specify all
> tables or multiple tables during the Export ?
> Thanks
> "Adam Machanic" wrote:
>

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

Beginner at Backups

I've spent the afternoon reading as much as possible on how to backup and
restore databases except there are a couple of small areas I can't quite get
my head round, so apologies for the beginners question but here goes:
I can set up a disk backup device and then every night run the 'Backup
Database' command to back up our database to a local folder, on top of this
I can run the 'backup log with truncate_only' every 20 minutes to provide
even better restore capability. I have tested these functions and
successfully restored the DB, but in this scenario the backup device is one
file and will simlpy keep growing every day. How can I overcome this? Should
I be creating a new file for every day and then deleting old files? Also,
should each transaction log be backed up to a new file - if not then how do
you know when to start a new backup file for the transaction logs without
the possibility of loosing data?
Cheers, Tom
Hi,
Use the INIT option along with BACKUP database command to reinitialize the
file every time.
Backup database dbname to device_name with INIT
See books online for more detail
Thanks
Hari
MCDBA
"Tom Clark" <tom_clark100@.hotmail.com> wrote in message
news:eTN0zsweEHA.140@.TK2MSFTNGP12.phx.gbl...
> I've spent the afternoon reading as much as possible on how to backup and
> restore databases except there are a couple of small areas I can't quite
get
> my head round, so apologies for the beginners question but here goes:
> I can set up a disk backup device and then every night run the 'Backup
> Database' command to back up our database to a local folder, on top of
this
> I can run the 'backup log with truncate_only' every 20 minutes to provide
> even better restore capability. I have tested these functions and
> successfully restored the DB, but in this scenario the backup device is
one
> file and will simlpy keep growing every day. How can I overcome this?
Should
> I be creating a new file for every day and then deleting old files? Also,
> should each transaction log be backed up to a new file - if not then how
do
> you know when to start a new backup file for the transaction logs without
> the possibility of loosing data?
> Cheers, Tom
>
|||1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
2. Instead of overwriting the backup file each night, it is fairly typical
to move the OLD Backup to an archive location and save a few previous
backups. OR you can just delete it and replace it with the new backup file.
We typically name our Backup Files with a date time element
...\Backups\DatabaseName\Full\DatabaseNameXX_FULL _MMDDYYYYMMSS.Bak
Where XX is the File Number (We create 4 seperate files for each full backup
to improve backup performance as SQL Server can write to multiple files at
once using multiple threads)
3. We write each transaction backup to a seperate file following the same
naming convention.
...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_ MMDDYYYYMMSS.Bak
we backup our transaction logs every 15 minutes.
AFTER each successful FULL Backup, we move the Old Transaction Logs to an
Archive Location. in the archive folder we keep 48 hours of logs. We delete
files older than 48 hours.
this may not be perfect, but it works for us and is a good start for you.
Cheers,
Greg Jackson
PDX, Oregon
|||Cheers Greg, this was exactly the kind of answer I was after!
Thinking on though, if you had to do a restore and then apply all the logs
up to the last log backup, would you do this by script (or apply each
transaction log by hand) and if so how? The testing I've done has only been
with a small number of log files and so I have applied them one by one.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:eb68X3weEHA.4068@.TK2MSFTNGP11.phx.gbl...
> 1. With TRUNCATE Only will ONLY truncate the logs (Basically smokes Em)
> 2. Instead of overwriting the backup file each night, it is fairly
typical
> to move the OLD Backup to an archive location and save a few previous
> backups. OR you can just delete it and replace it with the new backup
file.
> We typically name our Backup Files with a date time element
> ...\Backups\DatabaseName\Full\DatabaseNameXX_FULL_ MMDDYYYYMMSS.Bak
> Where XX is the File Number (We create 4 seperate files for each full
backup
> to improve backup performance as SQL Server can write to multiple files at
> once using multiple threads)
> 3. We write each transaction backup to a seperate file following the same
> naming convention.
> ...\Backups\DatabaseName\TRXN\DatabaseName_Trnxn_M MDDYYYYMMSS.Bak
> we backup our transaction logs every 15 minutes.
> AFTER each successful FULL Backup, we move the Old Transaction Logs to an
> Archive Location. in the archive folder we keep 48 hours of logs. We
delete
> files older than 48 hours.
>
> this may not be perfect, but it works for us and is a good start for you.
>
> Cheers,
>
> Greg Jackson
> PDX, Oregon
>
|||you can do it by hand or by script if you wanted.
I've actually done it by hand most often in past.
GAJ