Thanks Ekrem.
In your opinion which one is better, using backup to disk? Or directly to
tape; considering I can get tape drive hooked directly to SQL Server.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
It depends on the scenarios. You can see some scenarios from the following
articles.
Some of them:
http://www.smallbusinesscomputing.com/testdrive/article.php/3528326
http://www.storagesearch.com/engenio-art2.html
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:79E1C65C-5732-4957-B18B-14B765A9D558@.microsoft.com...
> Thanks Ekrem.
> In your opinion which one is better, using backup to disk? Or directly to
> tape; considering I can get tape drive hooked directly to SQL Server.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
|||Thanks! Those are good links, now I can backup some of what I say to
management ;-). Thanks again.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||I'm glad they helped, good luck! =)
Ekrem ?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:59BFA06B-DCAE-4840-8CBF-2C36A946D056@.microsoft.com...
> Thanks! Those are good links, now I can backup some of what I say to
> management ;-). Thanks again.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
sql
Showing posts with label tape. Show all posts
Showing posts with label tape. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Backup SQL to both TAPE AND DISK
Here's my problem. I want to perform occasional full backups on a tape but I
also want to backup to disk. The disk backup does a full backup every night
and trans log backups every half hour between 7 AM and 7 PM. The tape backup
(Veritas) just performs a full backup every day. Now when I restore, I don't
want my disk backup to "depend" on my tape backup. In other words, I don't
want to see the tape backup in the SQL backup log when I'm trying to
performs a restore from the disk backups. Is there a way to do this?
Thanks
jBA database backup doesn't break the chain of log backups so you are fine. Yo
u can apply any of the
database backups as long as you apply all subsequent log backups in sequence
. But why not skip the
Veritas SQL Server agent and let Veritas pick up the database backup files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"msnews.microsoft.com" <jbonds@.XXX.com> wrote in message
news:O2XVi3BWGHA.4960@.TK2MSFTNGP05.phx.gbl...
> Here's my problem. I want to perform occasional full backups on a tape but
I also want to backup
> to disk. The disk backup does a full backup every night and trans log back
ups every half hour
> between 7 AM and 7 PM. The tape backup (Veritas) just performs a full back
up every day. Now when I
> restore, I don't want my disk backup to "depend" on my tape backup. In oth
er words, I don't want
> to see the tape backup in the SQL backup log when I'm trying to performs a
restore from the disk
> backups. Is there a way to do this?
> Thanks
> jB
>|||msnews.microsoft.com wrote:
> Here's my problem. I want to perform occasional full backups on a tape but
I
> also want to backup to disk. The disk backup does a full backup every nigh
t
> and trans log backups every half hour between 7 AM and 7 PM. The tape back
up
> (Veritas) just performs a full backup every day. Now when I restore, I don
't
> want my disk backup to "depend" on my tape backup. In other words, I don't
> want to see the tape backup in the SQL backup log when I'm trying to
> performs a restore from the disk backups. Is there a way to do this?
> Thanks
> jB
Unless you are also doing Differential backups, nothing will "depend"
on your full backup to tape.
Why do you care what is in the backup log (I assume you mean the
backupfile table)? If you aren't using Maintenance Plans you can safely
delete from that table anyway so maybe you could just delete stuff you
don't want to see.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql
also want to backup to disk. The disk backup does a full backup every night
and trans log backups every half hour between 7 AM and 7 PM. The tape backup
(Veritas) just performs a full backup every day. Now when I restore, I don't
want my disk backup to "depend" on my tape backup. In other words, I don't
want to see the tape backup in the SQL backup log when I'm trying to
performs a restore from the disk backups. Is there a way to do this?
Thanks
jBA database backup doesn't break the chain of log backups so you are fine. Yo
u can apply any of the
database backups as long as you apply all subsequent log backups in sequence
. But why not skip the
Veritas SQL Server agent and let Veritas pick up the database backup files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"msnews.microsoft.com" <jbonds@.XXX.com> wrote in message
news:O2XVi3BWGHA.4960@.TK2MSFTNGP05.phx.gbl...
> Here's my problem. I want to perform occasional full backups on a tape but
I also want to backup
> to disk. The disk backup does a full backup every night and trans log back
ups every half hour
> between 7 AM and 7 PM. The tape backup (Veritas) just performs a full back
up every day. Now when I
> restore, I don't want my disk backup to "depend" on my tape backup. In oth
er words, I don't want
> to see the tape backup in the SQL backup log when I'm trying to performs a
restore from the disk
> backups. Is there a way to do this?
> Thanks
> jB
>|||msnews.microsoft.com wrote:
> Here's my problem. I want to perform occasional full backups on a tape but
I
> also want to backup to disk. The disk backup does a full backup every nigh
t
> and trans log backups every half hour between 7 AM and 7 PM. The tape back
up
> (Veritas) just performs a full backup every day. Now when I restore, I don
't
> want my disk backup to "depend" on my tape backup. In other words, I don't
> want to see the tape backup in the SQL backup log when I'm trying to
> performs a restore from the disk backups. Is there a way to do this?
> Thanks
> jB
Unless you are also doing Differential backups, nothing will "depend"
on your full backup to tape.
Why do you care what is in the backup log (I assume you mean the
backupfile table)? If you aren't using Maintenance Plans you can safely
delete from that table anyway so maybe you could just delete stuff you
don't want to see.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql
backup SQL server databases
Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.
You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegr oups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.
You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegr oups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
backup SQL server databases
Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
backup SQL server databases
Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously
. This means that you
cannot reliably "wait" until end of execution and then report back the error
. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that
using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously
. This means that you
cannot reliably "wait" until end of execution and then report back the error
. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that
using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>
Tuesday, March 20, 2012
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>sql
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>sql
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.
Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.
Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
Backup Question For Newbie
We use SQL for a few minor applications, and currently, we just down the
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
service at night and run a regular tape backup. However, that is no longer
a solution (SQL has to be up at night) so I'm researching using the built-in
backup features of SQL.
My question is this. I set up a test database, and just did a few backups
of it to a file. When I do a restore database, where is it pulling the
restore information from? (The list of all backups that I have done). If I
delete the actual backup files, I still see the list of all the backups I
have done, even though the actual backup file was deleted. Is there a way
to clear that information out?
Also, if I do a simple backup of each database at night, overwriting the
backup file, how many of the backups will it list? Is this behavior
controllable?
Thanks.Michael,
The backup information is stored in the system tables in the MSDB database.
See sp_delete_backuphistory for clearing the backup history info for a job.
A backup to a file will apend the backup to the file by default if overwrite
is specified only one backup will exist on the file at a time however the
backup history will probably still show all of the backups for the database.
HTH
Jerry
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>|||Hi,
All the backup information will be stored in backupset and backupfile tables
in MSDB database and all the restore information
is stored in restorehistory table in MSDB.
1. When I do a restore database, where is it pulling the restore information
from?
From the table mentioned above from MSDB
Is there a way to clear that information out?
Use msdb
go
sp_delete_backuphistory 'Until_date_forwhich_history_needs to removed'
overwriting the backup file, how many of the backups will it list?
It lists all the backups becase this information is fetched from MSDB table,
Thanks
Hari
SQL Server MVP
"Michael Long" <michael.long@.jrsmith.com> wrote in message
news:e0tV2LD0FHA.3180@.TK2MSFTNGP14.phx.gbl...
> We use SQL for a few minor applications, and currently, we just down the
> service at night and run a regular tape backup. However, that is no
> longer a solution (SQL has to be up at night) so I'm researching using the
> built-in backup features of SQL.
> My question is this. I set up a test database, and just did a few
> backups of it to a file. When I do a restore database, where is it
> pulling the restore information from? (The list of all backups that I
> have done). If I delete the actual backup files, I still see the list of
> all the backups I have done, even though the actual backup file was
> deleted. Is there a way to clear that information out?
> Also, if I do a simple backup of each database at night, overwriting the
> backup file, how many of the backups will it list? Is this behavior
> controllable?
> Thanks.
>
Backup question
Not sure if this is the correct group......
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
--
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> Not sure if this is the correct group......
> The tape drive on our SQL Server 2000 box died and I have just had it
> replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> problem I am having is that after every single backup job...or every step
in
> a backup job if it has multiple steps...the tape ejects on its own. I have
> made no changes to the backup jobs, just changed the physical tape drive.
> Any ideas where to start looking for the cause?
> Thanks
> Eric Stewart
>|||Thanks Andrew. I will check this out first thing in the morning!
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>|||That was it!!!
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>sql
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
--
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> Not sure if this is the correct group......
> The tape drive on our SQL Server 2000 box died and I have just had it
> replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> problem I am having is that after every single backup job...or every step
in
> a backup job if it has multiple steps...the tape ejects on its own. I have
> made no changes to the backup jobs, just changed the physical tape drive.
> Any ideas where to start looking for the cause?
> Thanks
> Eric Stewart
>|||Thanks Andrew. I will check this out first thing in the morning!
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>|||That was it!!!
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
> > Not sure if this is the correct group......
> >
> > The tape drive on our SQL Server 2000 box died and I have just had it
> > replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> > problem I am having is that after every single backup job...or every
step
> in
> > a backup job if it has multiple steps...the tape ejects on its own. I
have
> > made no changes to the backup jobs, just changed the physical tape
drive.
> > Any ideas where to start looking for the cause?
> >
> > Thanks
> >
> > Eric Stewart
> >
> >
>sql
Monday, March 19, 2012
Backup question
Not sure if this is the correct group......
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
in
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
The tape drive on our SQL Server 2000 box died and I have just had it
replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
problem I am having is that after every single backup job...or every step in
a backup job if it has multiple steps...the tape ejects on its own. I have
made no changes to the backup jobs, just changed the physical tape drive.
Any ideas where to start looking for the cause?
Thanks
Eric StewartCheck out the section in BooksOnLine for Backup and the Options UNLOAD and
NOUNLOAD. I suspect the old tape drive just didn't support this
functionality and you might now need to set NOUNLOAD.
Andrew J. Kelly
SQL Server MVP
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
quote:
> Not sure if this is the correct group......
> The tape drive on our SQL Server 2000 box died and I have just had it
> replaced. The new tape drive is a Dell Powervault 110T DLT VS 80. The
> problem I am having is that after every single backup job...or every step
in
quote:|||Thanks Andrew. I will check this out first thing in the morning!
> a backup job if it has multiple steps...the tape ejects on its own. I have
> made no changes to the backup jobs, just changed the physical tape drive.
> Any ideas where to start looking for the cause?
> Thanks
> Eric Stewart
>
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
quote:|||That was it!!!
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
step[QUOTE]
> in
have[QUOTE]
drive.[QUOTE]
>
Thanks again Andrew for the very helpful advice.
Eric
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OEAd2956DHA.2996@.tk2msftngp13.phx.gbl...
quote:
> Check out the section in BooksOnLine for Backup and the Options UNLOAD and
> NOUNLOAD. I suspect the old tape drive just didn't support this
> functionality and you might now need to set NOUNLOAD.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
> news:OZrLr246DHA.2264@.tk2msftngp13.phx.gbl...
step[QUOTE]
> in
have[QUOTE]
drive.[QUOTE]
>
Sunday, March 11, 2012
backup problem
when i try to take back from sqlserver 2000 enterprise manager it shows the error. wheni tried with NT itsays tape not found.
backup medium ::reportloerror:write failure on backup device\\.\tape0.operating system error 23(data error(cyclic reduncancy check));
pls help out in this issueeDo you have a tape drive?
You'd be better off dumping to disk and then copying to tape (faster)
Have you tried to set up a maint plan with the wizard (easiest)
backup medium ::reportloerror:write failure on backup device\\.\tape0.operating system error 23(data error(cyclic reduncancy check));
pls help out in this issueeDo you have a tape drive?
You'd be better off dumping to disk and then copying to tape (faster)
Have you tried to set up a maint plan with the wizard (easiest)
Wednesday, March 7, 2012
backup of transaction log
Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server database each midnight on tape and moving it off-site. I then take differential backups and also want to backup the transaction log. Assuming that my production database server is
on site and the site is burned down, how do I recover my transaction logs from? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recovery? Thanks in advance for the help!
- Bill
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill
|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential backup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file server store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You must create SP-s).
BOL: BACKUP and RESTORE
JBandi
|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill
Disaster recovery scenario ques: I am taking full backups of my SQL Server database each midnight on tape and moving it off-site. I then take differential backups and also want to backup the transaction log. Assuming that my production database server is
on site and the site is burned down, how do I recover my transaction logs from? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recovery? Thanks in advance for the help!
- Bill
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill
|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential backup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file server store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You must create SP-s).
BOL: BACKUP and RESTORE
JBandi
|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill
backup of transaction log
Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server d
atabase each midnight on tape and moving it off-site. I then take differenti
al backups and also want to backup the transaction log. Assuming that my pro
duction database server is
on site and the site is burned down, how do I recover my transaction logs fr
om? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recove
ry? Thanks in advance for the help!
- BillHi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe
.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential b
ackup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file serv
er store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You mus
t create SP-s).
BOL: BACKUP and RESTORE
JBandi|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill
Disaster recovery scenario ques: I am taking full backups of my SQL Server d
atabase each midnight on tape and moving it off-site. I then take differenti
al backups and also want to backup the transaction log. Assuming that my pro
duction database server is
on site and the site is burned down, how do I recover my transaction logs fr
om? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recove
ry? Thanks in advance for the help!
- BillHi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe
.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential b
ackup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file serv
er store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You mus
t create SP-s).
BOL: BACKUP and RESTORE
JBandi|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill
Saturday, February 25, 2012
Backup of databases
Hi
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on another machine (or locally
and then copying the file).
>The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your own scripts/jobs to
handle this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work with
> SQL and I don't know if this configuration could be done.
> Thanks in advance
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on another machine (or locally
and then copying the file).
>The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your own scripts/jobs to
handle this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work with
> SQL and I don't know if this configuration could be done.
> Thanks in advance
Sunday, February 12, 2012
Backup information
I want to know whether or not netbackup or tape backup was successful by runnign the script in sql query analyzer.
Take a look at backupfile and backupset tables
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
Take a look at backupfile and backupset tables
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
Backup information
I want to know whether or not netbackup or tape backup was successful by run
nign the script in sql query analyzer.Take a look at backupfile and backupset tables
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
nign the script in sql query analyzer.Take a look at backupfile and backupset tables
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
Backup information
I want to know whether or not netbackup or tape backup was successful by runnign the script in sql query analyzer.Take a look at backupfile and backupset tables
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
in MSDB... A row is created when backup is done...
YOu can also parse the SQL log or NT Application log for backup information
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brijesh" <Brijesh@.discussions.microsoft.com> wrote in message
news:4A1DD1FD-397B-41B1-BFCF-652B70FFF2B1@.microsoft.com...
> I want to know whether or not netbackup or tape backup was successful by
runnign the script in sql query analyzer.
Subscribe to:
Posts (Atom)