Based on backup and recovery

hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..

as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).

backups SQL

Hi

I want to have a full backup once per month and the a twice daily differential back up.So that I can go back to any half day point in the last 4 weeks

But do I get my full monthly backup to overwrite the existing full one in order to start again. and then does this mean that if i want to restore I can't go back 1 week because I have just done a full back up. (hope that makes sense)

e.g. I do a full monthly on the 30th which overwrites the existing media (incl all the diffs) then decide I want to go back to how the database was on the 22nd of the month...I can't because I've only got a full back up from the 30th)

Cheers

ICW

Why are you only doing a full backup once a month?

My standard practice is to do a full backup once a day, and backup the transaction log once an hour. (I've also got alerts set up to trigger a transaction log backup if the log exceeds 50% of its capacity.) I back up to disk, which creates files with the date and time of the backup, and then back those files to tape each night. I keep the disk backups on disk for 3 days, which allows me quick access to the backup in case of a problem. Beyond the 3 days it's relatively easy to recover the disk files from the appropriate tape, which is kept for 90 days. With this design I can recover any database to any time within the last 3 months.

backups interfering with log shipping?

I have some questions on backups and log shipping.

Before I get to them, though, the goal:

Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.

Here is what I have done so far, which has led to my questions:

I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'

I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.

Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.

Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?

Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?

My last question is for general info:

Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).

Many thanks in advance for your input!

GeoffBoiling my above messgae down to bite-size questions:

1. Where does SQL store information related to the backups that currently exist? Or does SQL scan for backup files and figure out what's in them on the fly (seems unlikely to me)?

2. Say I want to run two transaction log backups per hour. One is included in the normal backup plan and one occurs, say, 15 minutes later for log shipping purposes. Is my log shipping going to fail because the transactions that were backed up during the normal bacup will not be in my log shipping backup?|||It's in the msdb in a table called backupset.

I run log shipping where I backup the transaction log to a file name like DBNAME_TRAN.TRN. I then copy one to the SHIP TO server and apply it while copying the other to a directory where I add the data and time. I have scripts that determine which logs to apply in case I have to restore the database from the Full, Incrimental and then apply transaction log backups.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?
How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.
|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:
> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
--
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How do you run these jobs, and what types are they of. Maint Wiz? SQL
>> Server Agent jobs, TSQL or CmdExec? Etc...
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server
Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transactio
n
> logs. 2 out of the 3 databases are being backed up and the transaction log
s
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main
Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Tibor Karaszi wrote:
>
>
> They were created using the Maintenance Wizard and scheduled to run nightly.[/vbco
l]

Backups Best Practices

It looks like I have been presented with 2 options for backing up to tape.
1. Use veritas SQL Agent and back up the full every <blank> days and logs ev
ery <blank> days
2. Backup using native SQL agent to disk and using veritas to backup the BAK
files to tape every night.
Anyone have an opinion on which is best and why?I prefer native and than pick up the files to tape. Just be aware that you
have 24 hours potential loss of data of the local backup files are lost
before they are backed up to tape. I generally let the SQL backup copy the
files to another machine directly after the backup is takes, when possible.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||I agree with Tibor. I prefer to not have to deal with the tape agents if I
can avoid it.
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9hpqvW9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> I prefer native and than pick up the files to tape. Just be aware that you
> have 24 hours potential loss of data of the local backup files are lost
> before they are backed up to tape. I generally let the SQL backup copy the
> files to another machine directly after the backup is takes, when
possible.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||Thanks for your input..
What is your reason for this preference?
Speed?
You are more comfortable with SQL native agent?
Reliability?
Frequency of backups required?
Are you trying to save I/O over the backup next work?
Any more areas you have to add to this list of things to consider?|||I agree with Tibor, except that I prefer to backup directly to the remote
file system using a unc name, so I don't to coordinate the file
copies...(although the backup itself will run slower and eat network
bandwidth.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
> It looks like I have been presented with 2 options for backing up to tape.
> 1. Use veritas SQL Agent and back up the full every <blank> days and logs
every <blank> days
> 2. Backup using native SQL agent to disk and using veritas to backup the
BAK files to tape every night.
> Anyone have an opinion on which is best and why?|||For me it is simple: I prefer to not have my SQL Server data in the hands on
some 3:rd party vendor. Backup from SQL Server has been around for ages and
we all know that it work and how it work... :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed for
the really big databases) to a UNC share on another machine. I then have
daily, weekly and monthly rotations to tape with the monthly tapes removed
and archived.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I agree with Tibor, except that I prefer to backup directly to the remote
> file system using a unc name, so I don't to coordinate the file
> copies...(although the backup itself will run slower and eat network
> bandwidth.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:A03011F7-A2F7-46F9-9E10-40EE2A695832@.microsoft.com...
tape.
logs
> every <blank> days
the
> BAK files to tape every night.
>|||If you have a copy of the backup on a local machine (by local meaning
accessible by UNC) you can restore in the quickest possible time where as
with tape it may be a while to get the tape loaded etc.
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
> Thanks for your input..
> What is your reason for this preference?
> Speed?
> You are more comfortable with SQL native agent?
> Reliability?
> Frequency of backups required?
> Are you trying to save I/O over the backup next work?
> Any more areas you have to add to this list of things to consider?|||Also, some of the tape software components don't support all the backup and
restore options. Especially the WITH MOVE option specifying where each file
gets placed. This is especially important with very large databases where
you will have to spread the data out on multiple devices but you don't want
to overwrite the original database.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23Rhsx0X9DHA.2696@.TK2MSFTNGP10.phx.gbl...
> If you have a copy of the backup on a local machine (by local meaning
> accessible by UNC) you can restore in the quickest possible time where as
> with tape it may be a while to get the tape loaded etc.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "mannie" <anonymous@.discussions.microsoft.com> wrote in message
> news:E26131EA-B631-4482-A033-B6AD4215D0B9@.microsoft.com...
>|||we are getting ready to buy sql lite speed too
testing the demo for backups is impressive wtih decrease file size and speed
restores seem to take the same amount of time as with native sql versus the
VDI Sqllitespeed
is this what other are seeing
we routinely backup DBs in the 100-250 gig range and restore them on another
server for analytical use
experimenting with ways for smallest over time window for this
things like backup to local SAN -- restore to other server across network
back to UNC path on other server -- restore from local SAN
Trying to copy such large files are a network in windows is too slow -- it
is faster to just backup in SQL and then restore in SQL or move the file
with a tape library
We soon may have a disked based backup system to try DX30 from quantum
comments on getting shortest back and restore windows
example -- a large DB we have create a 170gig backup file the backup restore
rebuild indexes on this puppy takes 15-20 hours -- we do this once a month
as that is the refresh for new data loads -- new data is about 4-6 gig a
month
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23nQaoqX9DHA.2832@.tk2msftngp13.phx.gbl...
> I am with Wayne and Tibor on this one. SQL backups (using SQLLiteSpeed
for
> the really big databases) to a UNC share on another machine. I then have
> daily, weekly and monthly rotations to tape with the monthly tapes removed
> and archived.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:%23kaRt6W9DHA.2308@.TK2MSFTNGP11.phx.gbl...
remote
> tape.
> logs
> the
>

backups

database backups and differential backups

i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?From BOL:
The sequence for restoring differential database backups is:

1.Restore the most recent database backup.

2.Restore the last differential database backup.

3.Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.

yes if u restore to the last differential database backup the database will be recoverd to that point of time.|||I'm 99% sure that you'll need to restore the last full backup, plus each differential backup in sequence in order to get back to the state of the last differential dump you've got. In other words, if you are missing the backup from day 13 out of 30, you can only get to the point of the backup from day 12.

You only explicitly restore the last file, but all of the files need to be present in order for the restore to be successful.

-PatP|||that means if i have 10 differential back ups named 1,2,..10

i'll have to restore the last full back up (on jan 1st) and then restore each of 1,2,3...10 in order. am i correct?

one more question.

BOL (under Differential Database Backups ) says that "A differential database backup records only the data that has changed since the last database backup".

so is it necessary that i restore full backup & differential backups from 1 to 10

i think restoring full back up + differential backup no 10 will give me all the data .. i donno whether im correct..

pl discuss|||You have to restore only the last diff. backup made, not the whole sequence.
See 'Tracking Modified Extents' topic in Books Online for details. mojza

backups

Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
AnnAnn,
You need to use the UNC pattern.First create a share in that remote server,
make sure that the account under which SQL Server is running has the
required privileges/permissions on that share.After that, do the backup as:
BACKUP DATABASE <dbname>
TO DISK = '\\destserver\d$\dbbackup.BAK'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
> Need to do sql server 2000 database and transaction log
> backups. Using the backup wizard. Can back up on the
> server machine no problem.
> But I need to back up to another M/C which is visibly
> connected by the network, and to a CD burner (which is
> drive d on the server mc.) I have typed in a variety of
> paths for each scenario and nothing is working.
> Thank you
> Ann|||Use the UNC paths. Example:
\\MachineName\ShareName
or
\\MachineName\Driver$\Folder1\Folder2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
Ann

backup/restore strategy help

Hello.

I have only ever been required to take a full back up of my main prod database every night.

Now the times they are-a changing , and it is now required to be able to restore the database up to the last hour.

I've never really done much with tran log / differential backups so I'm asking for some advice as to what should be the best strategy. We are not a 24/7 shop we work from 6:30 am to 6:30 pm every day, so I thought:

1. Full backup @. 7pm

2. Backup tran log every hour after that starting @. 7am (as there are no changes overnight)

How does that sound? also when the tran log is backed up, is it truncated? Or do I need to shrink it?Basically I need to know what to do so it doesn't get too big!

Thanks

That's a good starting strategy.

The tran log will re-use space just the same way it does in simple recovery mode, with one change: It will not re-use space taken up by log records that haven't been backed up yet. Logical when you think about it.

So, with a regular tran log backup job, and a consistent workload, the tran log should reach a steady state, and not need to be shrunk unless it grows unusually due to an extraordinary event, like loading millions of records at once, when that's not the normal pattern.

You can then govern the size of the tran log to a large extent by varying the frequency of your log backups. The more often you back up the logs, the less records are still active due to not having been backed up, so the log doesn't need to grow as large. Also, the more frequently you back up the log, the less work is exposed to loss. Your commitment is to lose no more than an hour, but you can deliver better if you back up more frequently. The flip side of that is that with more frequent log backups, you have more backup files to manage, and restoring becomes more complicated. That's a tradeoff you have to make.

I would also STRONGLY suggest that you do a test restore of a database including log backups so that your procedures for restoring in this configuration are proven. You really don't want to be figuring this out when the database is down and everyone is screaming!

backup without .bak

i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files
in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ld
f)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create
a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup i
f
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:

> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database
.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup ifen">
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the
database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Th
en, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup
if
> something should go wrong (whatever that may be). the actual database wil
l
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen

backup without .bak

i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:
> ae wrote:
> > i have a database that's about 1.8GB and would like to back it up. I just
> > found out about it, but am reluctant to backup it up with the software GUI
> > part since it's my only database.
> >
> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> > to another disk manually to be safe, and then do a backup of the database
> > using SQL Enterprise?
> >
> > Also, if I were to create a backup of the .mdf and the .ldf files if
> > something should happen to the .bak file can I use the data file to create a
> > new database from it? If so, how?
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
>> ae wrote:
>> > i have a database that's about 1.8GB and would like to back it up. I just
>> > found out about it, but am reluctant to backup it up with the software GUI
>> > part since it's my only database.
>> >
>> > Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
>> > to another disk manually to be safe, and then do a backup of the database
>> > using SQL Enterprise?
>> >
>> > Also, if I were to create a backup of the .mdf and the .ldf files if
>> > something should happen to the .bak file can I use the data file to create a
>> > new database from it? If so, how?
>>
>> Copying the .mdf and .ldf files to another disk is not to be safe - it's
>> actually the opposite...:-). When the SQL Server service is running, you
>> can't copy the files. If you stop the service to copy the files, your
>> database will be unavailable which means that you'll have some down
>> time. As Tibor mentions, if you just copy the files with out using
>> detach there are no guarantee that you will be able to attach the files
>> to the new database. Even if you use the detach/attach method it's
>> actually not as safe as using a backup. When you run sp_detach_db, you
>> have 2 database files that you need to attach before you have working
>> database again. This means that even your original database is not
>> available/working. Theoretically something could go wrong, so it fails
>> to run sp_attach_db when want to get you original database running
>> again. In that case you have lost everything - even your original database.
>> If you use the backup, you'll have your original database running all
>> the time. If the backup doesn't work, you can just do a new backup since
>> nothing has happened to your original database.
>> Regards
>> Steen|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen

backup without .bak

i have a database that's about 1.8GB and would like to back it up. I just
found out about it, but am reluctant to backup it up with the software GUI
part since it's my only database.
Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
to another disk manually to be safe, and then do a backup of the database
using SQL Enterprise?
Also, if I were to create a backup of the .mdf and the .ldf files if
something should happen to the .bak file can I use the data file to create a
new database from it? If so, how?
Do the backup using the BACKUP command in SQL Server. Only copying the files in not guaranteed. If
you at all want to go that way, look into sp_detach_db and sp_attach_db.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:CAF78B51-7924-4617-A4E4-BD8CCA77C95B@.microsoft.com...
>i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
|||ae wrote:
> i have a database that's about 1.8GB and would like to back it up. I just
> found out about it, but am reluctant to backup it up with the software GUI
> part since it's my only database.
> Will it work if I copied the Data File (.mdf) and the Transaction Log (.ldf)
> to another disk manually to be safe, and then do a backup of the database
> using SQL Enterprise?
> Also, if I were to create a backup of the .mdf and the .ldf files if
> something should happen to the .bak file can I use the data file to create a
> new database from it? If so, how?
Copying the .mdf and .ldf files to another disk is not to be safe - it's
actually the opposite...:-). When the SQL Server service is running, you
can't copy the files. If you stop the service to copy the files, your
database will be unavailable which means that you'll have some down
time. As Tibor mentions, if you just copy the files with out using
detach there are no guarantee that you will be able to attach the files
to the new database. Even if you use the detach/attach method it's
actually not as safe as using a backup. When you run sp_detach_db, you
have 2 database files that you need to attach before you have working
database again. This means that even your original database is not
available/working. Theoretically something could go wrong, so it fails
to run sp_attach_db when want to get you original database running
again. In that case you have lost everything - even your original database.
If you use the backup, you'll have your original database running all
the time. If the backup doesn't work, you can just do a new backup since
nothing has happened to your original database.
Regards
Steen
|||hey thanks for the info., if i'm reading this correct when i do the backup if
something should go wrong (whatever that may be). the actual database will
never get damaged?
the worst thing that can happen i will have to reattempt to do a backup a
second time.
"Steen Persson (DK)" wrote:

> ae wrote:
>
> Copying the .mdf and .ldf files to another disk is not to be safe - it's
> actually the opposite...:-). When the SQL Server service is running, you
> can't copy the files. If you stop the service to copy the files, your
> database will be unavailable which means that you'll have some down
> time. As Tibor mentions, if you just copy the files with out using
> detach there are no guarantee that you will be able to attach the files
> to the new database. Even if you use the detach/attach method it's
> actually not as safe as using a backup. When you run sp_detach_db, you
> have 2 database files that you need to attach before you have working
> database again. This means that even your original database is not
> available/working. Theoretically something could go wrong, so it fails
> to run sp_attach_db when want to get you original database running
> again. In that case you have lost everything - even your original database.
> If you use the backup, you'll have your original database running all
> the time. If the backup doesn't work, you can just do a new backup since
> nothing has happened to your original database.
> Regards
> Steen
>
|||> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
If depends on what you mean by "something goes wrong". If the disk where the database sis crashes,
well, you cannot expect the database to be healthy... :-)
But I take it you mean if something goes wrong with the backup procedure. Then, don't worry. Just
redo the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ae" <ae@.discussions.microsoft.com> wrote in message
news:2A4B9FD5-BC7A-4130-B7F4-B32BA31610A5@.microsoft.com...[vbcol=seagreen]
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
> "Steen Persson (DK)" wrote:
|||ae wrote:
> hey thanks for the info., if i'm reading this correct when i do the backup if
> something should go wrong (whatever that may be). the actual database will
> never get damaged?
> the worst thing that can happen i will have to reattempt to do a backup a
> second time.
>
That's correct.
Regards
Steen

backup when trn. is going on

Hi everybody,
Is there any way to back Database when transcations are going on?sql server backups are designed in such a way that they can be run at any time against the database and or the transaction log.
you can perform a database backup from the enterprise manager or from the query analyzer.
Instructions are at the bottom of the following books online topic.

Books Online {Database Backups}

Backup up to disk file

I have SQL 2000 set to back up our primary database every six hours to a
disk file. Over time, the disk file has gotten rather large. How can I
delete the some of the older content of the Backup Device? And can I make a
job to have it delete some of the older contents on a regular basis?You cannot delete some of the backups from a backup device. It is all or nothing (see the INIT and
NOINIT options of the backup command).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Stephen Ott" <sott3@.yahoo.com> wrote in message news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> I have SQL 2000 set to back up our primary database every six hours to a
> disk file. Over time, the disk file has gotten rather large. How can I
> delete the some of the older content of the Backup Device? And can I make a
> job to have it delete some of the older contents on a regular basis?
>|||Have a look at the RETAINDAYS property of the BACKUP command in BOL
--
HTH
Ryan Waight, MCDBA, MCSE
"Stephen Ott" <sott3@.yahoo.com> wrote in message
news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> I have SQL 2000 set to back up our primary database every six hours to a
> disk file. Over time, the disk file has gotten rather large. How can I
> delete the some of the older content of the Backup Device? And can I make
a
> job to have it delete some of the older contents on a regular basis?
>|||RATAINDAYS doesn't delete contents of a backup device. It only prohibit you from doing INIT before a
certain number of days has elapsed.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Ols8BaSfDHA.3104@.TK2MSFTNGP11.phx.gbl...
> Have a look at the RETAINDAYS property of the BACKUP command in BOL
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Stephen Ott" <sott3@.yahoo.com> wrote in message
> news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> > I have SQL 2000 set to back up our primary database every six hours to a
> > disk file. Over time, the disk file has gotten rather large. How can I
> > delete the some of the older content of the Backup Device? And can I make
> a
> > job to have it delete some of the older contents on a regular basis?
> >
> >
>

Backup up sql server express database

Hi, I want to back up my database. I found in the books online where it said
backups could be done by simply copying the .mdf and .ldf files. Its that
enough? I don't have to run sqlmaint to produce the .bak files? and then
copy those?
Is there going t be a new newsgroup for SQL Server 2005 Express?
Thanks,
Brian
hi Brian,
SuperBK wrote:
> Hi, I want to back up my database. I found in the books online where
> it said backups could be done by simply copying the .mdf and .ldf
> files. Its that enough? I don't have to run sqlmaint to produce the
> .bak files? and then copy those?
you can just simply copy your database and log files, but the database must
not be in use in order to allow that... so consider a "standard" stategy
where you perform Transact-SQL BACKUP DATABASE statements, that's to say the
"natural" backup option for SQL Server databases..
http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
you can perform that via the native sqlcmd.exe or oSql.exe, or even your own
home made tool and/or application..

> Is there going t be a new newsgroup for SQL Server 2005 Express?
think there will be...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

backup Transaction Log

Hi All,
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
Mitra
Correct. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra
|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx.gbl...
>

backup Transaction Log

Hi All,
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
MitraCorrect. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx
.gbl...
>

backup Transaction Log

Hi All,
Yesterday i posted my queston and i got couple of replies.
However, I was not clear with how i could back up a
transaction log file that is 25 GB when i have only 1GB
free space available.
Does it mean that i should not worry about the size of
the disk space available when i run the following code:
BACKUP LOG pubs WITH TRUNCATE_ONLY
Thank you,
MitraCorrect. Incidentally, you might want to read the documentation on the
commands before you do anything recommended in the newsgroups to make sure
you understand what the commands do. After all, we are not affected if your
database is destroyed.
"Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:138f701c44421$7e747f30$a601280a@.phx.gbl...
> Hi All,
> Yesterday i posted my queston and i got couple of replies.
> However, I was not clear with how i could back up a
> transaction log file that is 25 GB when i have only 1GB
> free space available.
> Does it mean that i should not worry about the size of
> the disk space available when i run the following code:
> BACKUP LOG pubs WITH TRUNCATE_ONLY
> Thank you,
> Mitra|||Hi,
Look into the SQL Server Recovery models documentation in Books online.
I recommend you to set the recovery model to "SIMPLE" incase if your
database is not a production data.
In this model the transaction file will be cleared on each commit. This will
ensure that your transaction file wont grow that big.
Thanks
Hari
MCDBA
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OHrx7cCREHA.2572@.TK2MSFTNGP12.phx.gbl...
> Correct. Incidentally, you might want to read the documentation on the
> commands before you do anything recommended in the newsgroups to make sure
> you understand what the commands do. After all, we are not affected if
your
> database is destroyed.
> "Mittra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:138f701c44421$7e747f30$a601280a@.phx.gbl...
> > Hi All,
> >
> > Yesterday i posted my queston and i got couple of replies.
> >
> > However, I was not clear with how i could back up a
> > transaction log file that is 25 GB when i have only 1GB
> > free space available.
> >
> > Does it mean that i should not worry about the size of
> > the disk space available when i run the following code:
> > BACKUP LOG pubs WITH TRUNCATE_ONLY
> >
> > Thank you,
> >
> > Mitra
>

Backup Transaction fails.

Hi,
I have taken a back up of a database that was under transactional
replication and was the subscriber. I have restored the backup on to another
server as a stand alone database (no replication what so ever).
I am now trying to truncate the transaction log as its taking a whole lot of
space on the disc and want to shrink the file.
I issue the following statement:
BACKUP LOG <databaseName> WITH NO_LOG
when i issue this statement it comes out with the following error:
The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.
Is there any way I can get around this and truncate the log file?
Thanks
Vikram
Vikram,
try:
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time
= 0, @.reset = 1
then truncate the log.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I executed that procedure and came up with the following:
Server: Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
The database is not published.
Any clues?
Cheers
Vikram
"Paul Ibison" wrote:

> Vikram,
> try:
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time
> = 0, @.reset = 1
> then truncate the log.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||One idea I'd try is to publish the database in TR, run the script, truncate,
then drop the publication.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks very much Paul.
It worked.
thanks a lot.
Cheers
"Paul Ibison" wrote:

> One idea I'd try is to publish the database in TR, run the script, truncate,
> then drop the publication.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>