Newbie question on physical file last mod date vs. virtual DB/Log

I think I understand the basic features of log files and how to
backup/truncate them to avoid oversized files. Where I need
help is understanding why the O/S rarely puts a new datetime
on the physical files. It appears to only update the log file
time when the log file grows. The DB file may go a month or more
without updating the timestamp. Our backups of the DB grow in
size and appear to be OK. CHECKPOINT commands don't force
the physical disk to update. Is there any other way to do it
(short of drastic measures like detaching the DB)?
Here's my concern: Suppose the server crashes due to a power
failure/UPS failure/whatever. When it restarts it will look at the
log to recover the DB. The physical DB file appears to be a month old
and the log datetime appears to be a day or two old.
If I truncated my log at any time in the last month then it seems like
it will not be able to recover correctly.
Is this really a problem or is it OK?
Environment: SQL Server 2000 on Win2K ServerWhy and when Windows updates the datetime for the file, I don't know. Perhaps somebody in the
windows forum can answer that. However:
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB.
Correct. SQL Server know where to find the ldf file, it is stored both in the mdf file as well as in
the master database.
> The physical DB file appears to be a month old
Doesn't matter to SQL Server.
> and the log datetime appears to be a day or two old.
Can you explain what you mean by "log datetime"?
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
What do you mean by "truncated"? Something like BACKUP LOG ... WITH TRUNCATE_ONLY? As long as you
haven't deleted the log file and replaced with an older version, you are fine. SQL server will not
remove log records needed to do recovery of the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:0817C2B3-1F18-4B50-A4C8-6A54D52D0D87@.microsoft.com...
>I think I understand the basic features of log files and how to
> backup/truncate them to avoid oversized files. Where I need
> help is understanding why the O/S rarely puts a new datetime
> on the physical files. It appears to only update the log file
> time when the log file grows. The DB file may go a month or more
> without updating the timestamp. Our backups of the DB grow in
> size and appear to be OK. CHECKPOINT commands don't force
> the physical disk to update. Is there any other way to do it
> (short of drastic measures like detaching the DB)?
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB. The physical DB file appears to be a month old
> and the log datetime appears to be a day or two old.
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
> Is this really a problem or is it OK?
> Environment: SQL Server 2000 on Win2K Server
>|||Re: Can you explain what you mean by "log datetime"?
Answer: The last modification time on the log file (i.e, both
the DB file and the Log file appear to be "old" on the disk).
Re: What do you mean by "truncated"?
Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
Answer: Yes.
If the log file had a recent last modification time (the Windows File)
then it all makes sense. It seems like "magic" because it "looks" like
SQL Server does recovery with an very old DB file and a not-so-recent
Log file. Is it possible the disk files are actually updated by SQL
Server without changing the disk file last modification time?
Thank you for your help.|||> Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
Yes, this is what is happening. Again, check with the Windows people under what conditions the NTFS
file timestamps are changed. I understand that you find this ... interesting, but just don't worry
about the file timestamps.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:898EB1A4-A02E-456F-B82A-4391608EAD6A@.microsoft.com...
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>|||Don Anthony wrote:
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>
I'm not sure, but I'd think that the file timestamp for the logfile and
database file are only updated when the files are actually changed. That
could e.g. be when the file grows or shrinks.
I've just looked at one of our databases, and here the date for the
database file is 26. febr. 2006 and for the logfile it's 3. jan. 2006.
Like Tibors says, SQL server isn't using these file timestamps for
anything, so help yourself and don't worry about them...:-).
Regards
Steen

Newbie question about SQL 2000 backup

I have my SQL 2000 server running on W2003 Server
Logged in as domain admin
I want to do a manual backup of my database
So I follow the Microsoft document
Open Enterprise Manager, right click the database, all tasks, backeup
database, select full backup, add destination (I choose the default file
location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
device)
I get a warning 'the filename specified is recognised as a directory name.
The file won't be able to be rewritten. Do you wish to continue?'
I select OK
I then get an error '(ODBC SQLState 42000) Cannot open the backup device
C:\Program Files\etc.' Device error or device offline. See the SQL server log
for more details'
The eventlog error is '17055' Source' MSSQLServer' '3041 :
BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' WITH NOINIT ,
NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
Any ideas anyone please !?!
why wouldn't you specify full file name (for example : C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\DATASTORE.BAK ) ?
"goundhog" <goundhog@.discussions.microsoft.com> wrote in message
news:2B0D39F7-0324-4C8C-B22B-0F70BDD8E530@.microsoft.com...
>I have my SQL 2000 server running on W2003 Server
> Logged in as domain admin
> I want to do a manual backup of my database
> So I follow the Microsoft document
> Open Enterprise Manager, right click the database, all tasks, backeup
> database, select full backup, add destination (I choose the default file
> location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
> device)
> I get a warning 'the filename specified is recognised as a directory name.
> The file won't be able to be rewritten. Do you wish to continue?'
> I select OK
> I then get an error '(ODBC SQLState 42000) Cannot open the backup device
> C:\Program Files\etc.' Device error or device offline. See the SQL server
> log
> for more details'
> The eventlog error is '17055' Source' MSSQLServer' '3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO DISK
> =
> N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' WITH NOINIT ,
> NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
>
> Any ideas anyone please !?!
>

Newbie question about SQL 2000 backup

I have my SQL 2000 server running on W2003 Server
Logged in as domain admin
I want to do a manual backup of my database
So I follow the Microsoft document
Open Enterprise Manager, right click the database, all tasks, backeup
database, select full backup, add destination (I choose the default file
location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
device)
I get a warning 'the filename specified is recognised as a directory name.
The file won't be able to be rewritten. Do you wish to continue?'
I select OK
I then get an error '(ODBC SQLState 42000) Cannot open the backup device
C:\Program Files\etc.' Device error or device offline. See the SQL server lo
g
for more details'
The eventlog error is '17055' Source' MSSQLServer' '3041 :
BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO DI
SK =
N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP' WITH NOINIT ,
NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
Any ideas anyone please !?!why wouldn't you specify full file name (for example : C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\DATASTORE.BAK ) ?
"goundhog" <goundhog@.discussions.microsoft.com> wrote in message
news:2B0D39F7-0324-4C8C-B22B-0F70BDD8E530@.microsoft.com...
>I have my SQL 2000 server running on W2003 Server
> Logged in as domain admin
> I want to do a manual backup of my database
> So I follow the Microsoft document
> Open Enterprise Manager, right click the database, all tasks, backeup
> database, select full backup, add destination (I choose the default file
> location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
> device)
> I get a warning 'the filename specified is recognised as a directory name.
> The file won't be able to be rewritten. Do you wish to continue?'
> I select OK
> I then get an error '(ODBC SQLState 42000) Cannot open the backup device
> C:\Program Files\etc.' Device error or device offline. See the SQL server
> log
> for more details'
> The eventlog error is '17055' Source' MSSQLServer' '3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO
DISK
> =
> N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP' WITH NOINIT ,
> NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
>
> Any ideas anyone please !?!
>

Newbie question about SQL 2000 backup

I have my SQL 2000 server running on W2003 Server
Logged in as domain admin
I want to do a manual backup of my database
So I follow the Microsoft document
Open Enterprise Manager, right click the database, all tasks, backeup
database, select full backup, add destination (I choose the default file
location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
device)
I get a warning 'the filename specified is recognised as a directory name.
The file won't be able to be rewritten. Do you wish to continue?'
I select OK
I then get an error '(ODBC SQLState 42000) Cannot open the backup device
C:\Program Files\etc.' Device error or device offline. See the SQL server log
for more details'
The eventlog error is '17055' Source' MSSQLServer' '3041 :
BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' WITH NOINIT ,
NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
Any ideas anyone please !?!why wouldn't you specify full file name (for example : C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\DATASTORE.BAK ) ?
"goundhog" <goundhog@.discussions.microsoft.com> wrote in message
news:2B0D39F7-0324-4C8C-B22B-0F70BDD8E530@.microsoft.com...
>I have my SQL 2000 server running on W2003 Server
> Logged in as domain admin
> I want to do a manual backup of my database
> So I follow the Microsoft document
> Open Enterprise Manager, right click the database, all tasks, backeup
> database, select full backup, add destination (I choose the default file
> location C:\ ProgramdFiles\SQL Servere etc. - as I don't have a backup
> device)
> I get a warning 'the filename specified is recognised as a directory name.
> The file won't be able to be rewritten. Do you wish to continue?'
> I select OK
> I then get an error '(ODBC SQLState 42000) Cannot open the backup device
> C:\Program Files\etc.' Device error or device offline. See the SQL server
> log
> for more details'
> The eventlog error is '17055' Source' MSSQLServer' '3041 :
> BACKUP failed to complete the command BACKUP DATABASE [DATASTORE] TO DISK
> => N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' WITH NOINIT ,
> NOUNLOAD , NAME = N'DATASTORE backup', NOSKIP , STATS = 10, NOFORMAT '
>
> Any ideas anyone please !?!
>sql

Newbie Question - Table only Backup

I may be missing something... but I simply want to backup a single table from my DB inorder to export it to another server (via FTP)... is there a way to do this specifically in MS SQL 7 ' I want to save the backup on my desktop, for instance... and not into another DB on the server... am I clear or missing something..
Thanks !!Put the table on a different filegroup, since you can back up filegroups
separately. You can't back up tables separately.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> I may be missing something... but I simply want to backup a single table
from my DB inorder to export it to another server (via FTP)... is there a
way to do this specifically in MS SQL 7 ' I want to save the backup on my
desktop, for instance... and not into another DB on the server... am I clear
or missing something...
> Thanks !!|||Note that such a backup is possibly unusable for Joe's scenario. When
restoring an FG backup you have to do that into the same database from where
you took the backup and also apply all log backups taken since, up to
current point in time.
Joe,
another option is to export he data in the table, using such tools as BCP,
DTS etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eIhT5OpvDHA.2408@.tk2msftngp13.phx.gbl...
> Put the table on a different filegroup, since you can back up filegroups
> separately. You can't back up tables separately.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
> news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> > I may be missing something... but I simply want to backup a single table
> from my DB inorder to export it to another server (via FTP)... is there a
> way to do this specifically in MS SQL 7 ' I want to save the backup on my
> desktop, for instance... and not into another DB on the server... am I
clear
> or missing something...
> > Thanks !!
>|||Ah, that's a good point. Yes, you could DTS the table to another database,
and backup from there. Or keep the table in its own separate database
permanently, I suppose.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eCdyUSpvDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Note that such a backup is possibly unusable for Joe's scenario. When
> restoring an FG backup you have to do that into the same database from
where
> you took the backup and also apply all log backups taken since, up to
> current point in time.
> Joe,
> another option is to export he data in the table, using such tools as BCP,
> DTS etc.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:eIhT5OpvDHA.2408@.tk2msftngp13.phx.gbl...
> > Put the table on a different filegroup, since you can back up filegroups
> > separately. You can't back up tables separately.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
> > news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> > > I may be missing something... but I simply want to backup a single
table
> > from my DB inorder to export it to another server (via FTP)... is there
a
> > way to do this specifically in MS SQL 7 ' I want to save the backup on
my
> > desktop, for instance... and not into another DB on the server... am I
> clear
> > or missing something...
> > > Thanks !!
> >
> >
>

Newbie Question

Hi
I simply want to automate the backup of the database every hour using the
Enterprise Manager
Thank you,
Shmuel Shulman
Use "Database Maintenance Plan Wizard".
"SQL Server 2000 Administrator's Pocket Consultant: Database Automation and
Maintenance"
http://www.microsoft.com/technet/pro.../c12ppcsq.mspx
"Automate database upkeep with the SQL Server Maintenance Plan Wizard"
http://techrepublic.com.com/5100-6268_11-1043672.html
Cristian Lefter, SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%234sWEVsOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
|||Steps:-
Hi,
Use the maintenance Plan wizard from enterprise manager to scdule a database
backup every one hour.
1. Open Enterprise manager and connect to server
2. Go to Managaement option
3. Select Database maintenance plans
4. Right click and select the new maintenenace plan
5. Select the database
6. Go further by clicking next
7. You couild see the Backup option, retension days...
8. schedule the maintenance plan and give a suitable name
Note:
You can also scdule by writing a backup script using Backup database command
and scheule using SQL Agent... Jobs..
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
>

Newbie Question

Hi
I simply want to automate the backup of the database every hour using the
Enterprise Manager
Thank you,
Shmuel ShulmanUse "Database Maintenance Plan Wizard".
"SQL Server 2000 Administrator's Pocket Consultant: Database Automation and
Maintenance"
http://www.microsoft.com/technet/pr...s/c12ppcsq.mspx
"Automate database upkeep with the SQL Server Maintenance Plan Wizard"
http://techrepublic.com.com/5100-6268_11-1043672.html
Cristian Lefter, SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%234sWEVsOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>|||Steps:-
Hi,
Use the maintenance Plan wizard from enterprise manager to scdule a database
backup every one hour.
1. Open Enterprise manager and connect to server
2. Go to Managaement option
3. Select Database maintenance plans
4. Right click and select the new maintenenace plan
5. Select the database
6. Go further by clicking next
7. You couild see the Backup option, retension days...
8. schedule the maintenance plan and give a suitable name
Note:
You can also scdule by writing a backup script using Backup database command
and scheule using SQL Agent... Jobs..
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
>

Newbie Question

Hi
I simply want to automate the backup of the database every hour using the
Enterprise Manager
Thank you,
Shmuel ShulmanUse "Database Maintenance Plan Wizard".
"SQL Server 2000 Administrator's Pocket Consultant: Database Automation and
Maintenance"
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c12ppcsq.mspx
"Automate database upkeep with the SQL Server Maintenance Plan Wizard"
http://techrepublic.com.com/5100-6268_11-1043672.html
Cristian Lefter, SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%234sWEVsOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>|||Steps:-
Hi,
Use the maintenance Plan wizard from enterprise manager to scdule a database
backup every one hour.
1. Open Enterprise manager and connect to server
2. Go to Managaement option
3. Select Database maintenance plans
4. Right click and select the new maintenenace plan
5. Select the database
6. Go further by clicking next
7. You couild see the Backup option, retension days...
8. schedule the maintenance plan and give a suitable name
Note:
You can also scdule by writing a backup script using Backup database command
and scheule using SQL Agent... Jobs..
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
>sql

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.
I'm sorry, Monty, but there's no single table restore facility in SQL Server (think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to specify new physical file names)
and then copy the desired table(s)/data into your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>
|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
I
>
sql

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.I'm sorry, Monty, but there's no single table restore facility in SQL Server
(think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to s
pecify new physical file names)
and then copy the desired table(s)/data into your production database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx
.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
I[vbcol=seagreen]
>

Newbie Query Syntax question

I just need to restore a deleted table from a local backup of the entire
database. For my example the database name is MYDB and the table MYTABL. I
THINK I understand the start to be:
USE MYDB
Go
Restore Database MYDB
From MYDB
Disk = 'C:\Location of folder\MYDB.bak
With NORECOVERY
MOVE
and from there I am stumped. I am not sure about the FILE clause and
"Logical file name" v. "Operating system file name"
Thanks in advance for any help.I'm sorry, Monty, but there's no single table restore facility in SQL Server (think about data integrity and
relationships between tables).
You can restore the whole database into a new database (use MOVE option to specify new physical file names)
and then copy the desired table(s)/data into your production database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Monty" <montysl@.nospam.hotmail.com> wrote in message news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> I just need to restore a deleted table from a local backup of the entire
> database. For my example the database name is MYDB and the table MYTABL. I
> THINK I understand the start to be:
> USE MYDB
> Go
> Restore Database MYDB
> From MYDB
> Disk = 'C:\Location of folder\MYDB.bak
> With NORECOVERY
> MOVE
> and from there I am stumped. I am not sure about the FILE clause and
> "Logical file name" v. "Operating system file name"
> Thanks in advance for any help.
>
>|||Thanks Tibor...I had been looking for the answer all morning...Thought I was
missing something.....feel slightly less ignorant of the matter now.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23tNy3CVgEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm sorry, Monty, but there's no single table restore facility in SQL
Server (think about data integrity and
> relationships between tables).
> You can restore the whole database into a new database (use MOVE option to
specify new physical file names)
> and then copy the desired table(s)/data into your production database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Monty" <montysl@.nospam.hotmail.com> wrote in message
news:uKYJNBVgEHA.3932@.TK2MSFTNGP09.phx.gbl...
> > I just need to restore a deleted table from a local backup of the entire
> > database. For my example the database name is MYDB and the table MYTABL.
I
> > THINK I understand the start to be:
> >
> > USE MYDB
> > Go
> > Restore Database MYDB
> > From MYDB
> > Disk = 'C:\Location of folder\MYDB.bak
> > With NORECOVERY
> > MOVE
> >
> > and from there I am stumped. I am not sure about the FILE clause and
> > "Logical file name" v. "Operating system file name"
> >
> > Thanks in advance for any help.
> >
> >
> >
>

newbie maintenance/backup question

Please lend some advice,

Right now we use a standard Database Maintenance Plan to backup and organize our SQL server databases. However we are running out of disk space because every night it does a full backup of the databases and a few of the databases are quite large > 1 GByte.

Is there a way to perform differential backups daily and full backups once a week (or month) in conjunction with a Database Maintenance Plan? I know how to schedule jobs and perform differential and full backups on this kind of schedule for a single database.

But I'm worried that creating separate jobs for each database to handle the full backup, the differential backup, the backup of the transaction log, compacting the database, (etc) is going to become a nightmare to keep up with 50+ jobs.

I like the Database Maintenance Plan because i can go to one place and manage the backup, reorganization, compacting, etc of all of our databases at once and schedule just a few jobs.

What is the best way to maintain (and especially backup) 10+ databases of decent size?

Thanks in advance,
Shane Kunklecheck google. There are plenty of site containing SQL Server administration tips... (ex: sswug.org)

Newbie backup question

Hi, am really new to all of this, so please be gentle. :)

On computer A (the server) I have SBS2k3 with SQL server 2k. On computer B (the workstation) I have MS Office/Access. I have created a couple of access projects on the workstation that have tables, stored procedures, etc.. in a couple of database on the server. Now I need some kind of backup plan.

I backup the Access projects on removable USB drives plugged into the workstation. I'd like to do scheduled backups of the databases on the server and also copy those backups to the USB drives on the workstation.

I've read that I can use Enterprise manager on the server to do scheduled backups to any drive on the server. I figure if I do that to a shared folder on the server that I can then copy that backup via the workstation and save a copy of it to the USB drive on the workstation.

1 - Do you see any problems with this scheme?

2 - Does the backup from enterprise manager really back everything up (table structures, stored procedures, etc...)?

3 - Assuming that I cycle between onsite and offsite USB drives on the workstation, if the place burned down and I had to rebuild the machines from scratch, is the backup saved to the USB drive mentioned above going to contain everything I need to recreate my databases on new blank hardware (after a fresh install of SBS2k3 and SQL server, of course)?there are many ways of doing this.
1. create a shared on the serverA (i.e. c:\shared -> \\serverA\shared)
2. create a sql job to backup your db:
backup database [your_db]
to disk 'c:\shared\your_db.bak'
with init
3. on your workstation, you can just create a windows schedule to connect to \\serverA\shared and copy the backup.

In general, I think it's a bad idea to try to schedule a backup job on the server to backup to your workstation usb drive. This can be done but is not really reliable. You have a regular backup job on the server and do copy/pull of the backup file as needed.|||Thanks.

No, I wasn't planning to schedule a task on the server to backup directly to the workstation's USB drive. I figured I'd do that manually from the workstation, since I don't always have one of the USB drives turned on. But I was planning to do a scheduled backup on the server to a folder on the server. (probably in the middle of the night)

> backup database [your_db]
> to disk 'c:\shared\your_db.bak'
> with init

So, that will backup everything, including the table structures and stored procedures, so the database can be recreated on a different computer in addition to being able to restore on the same one?|||The BAK file should contain everything. However, you shouldn't take anyone here's word for it. Create a backup of "DatabaseName", then take that BAK file and restore it to "NewDatabaseName". See if the restored database contains what you want. You can delete it after that, but then you'll know for sure what you'll get.

Newbie backup question

I just start learning how to use SQL Server 2000.
A question about transaction log backup:
There is a full backup on Sunday night and differential backup from Monday
to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
weekdays.
Just wonder is the transaction log at 6pm on Saturday night containing all
transactions from Monday to Friday of the same week ?Nope, the log is emptied each time you do a backup. So the 6PM log backup contains the transactions
produced on sat from 3pm to 6pm (roughly).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>|||To restore your DB to Saturday post log backup you will need
Sunday Full
Friday Differential (Differences between last Full backup and now)
Saturday Logs(9,12,3,6)
BOL has a good example
Differential Backup and Restore
A differential backup creates a copy of all the pages in a database modified
after the last database backup. Differential logs are used primarily in
heavily used systems where a failed database must be brought back online
quickly. Differential backups are smaller than full database backups;
therefore, they have less of an effect on the system while they run.
For example, a site executes a full database backup on Sunday night. A set
of transaction log backups is made every four hours during the day, with the
backups from one day overwriting the backups from the day before. Each night
the site makes a differential backup. If one of the data disks for the
database fails at 9:12 A.M. on Thursday, the site can:
1.. Back up the current transaction log.
2.. Restore the database backup from Sunday night.
3.. Restore the differential backup from Wednesday night to roll the
database forward to that point.
4.. Restore the transaction log backups from 4:00 A.M. and 8:00 A.M. to
roll the database forward to 8:00 A.M.
5.. Restore the log backup taken after the failure. This will roll the
database forward to the time of the failure
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:e7T46DlhDHA.1864@.TK2MSFTNGP10.phx.gbl...
> I just start learning how to use SQL Server 2000.
> A question about transaction log backup:
> There is a full backup on Sunday night and differential backup from Monday
> to Saturday night. Transaction log backup on 9am, 12pm, 3pm, 6pm on
> weekdays.
> Just wonder is the transaction log at 6pm on Saturday night containing all
> transactions from Monday to Friday of the same week ?
>

Newbie backup Question

I have a table in sql 2000 that is storing Financial Tick data. The database
size now appears at about 30GB and I have decided to backup the database and
store the data offsite.

Here is where I am confused about whether only a Database (complete) backup
is necessary or whether I must also include a Transaction log backup for the
most recent data.

1. If I use a complete Database backup only, will all my data be stored on
this? (Even if I have never done a Transaction log backup)

2. My Database size Data.MDF is 500MB and the Log.LDF is 30GB. If I complete
a database Backup can I now get rid of this Log file, or is it also storing
data that is necessary?? I noticed on Books online that it states
following a log file backup that it truncates the inactive portion of the
transaction log, which would hopefully decrease this enormous log file size.

Thank you for your help"Fred" <Fred@.hotmail.com> wrote in message
news:418462d8$1@.duster.adelaide.on.net...
> I have a table in sql 2000 that is storing Financial Tick data. The
database
> size now appears at about 30GB and I have decided to backup the database
and
> store the data offsite.
> Here is where I am confused about whether only a Database (complete)
backup
> is necessary or whether I must also include a Transaction log backup for
the
> most recent data.

The Full will only be complete up through any committed transaction at the
time of its finish.

Now, if you do a full once a day (say at 1:00 AM), you can restore to that
point.

But let's say your DB crashes at 11:00 PM. You've lost 22 hours worth of
transactions.

If you do a transaction backup say every hour, you could restore up through
the 10:00 PM log and lose less data.

> 1. If I use a complete Database backup only, will all my data be stored on
> this? (Even if I have never done a Transaction log backup)

See above.

> 2. My Database size Data.MDF is 500MB and the Log.LDF is 30GB. If I
complete
> a database Backup can I now get rid of this Log file, or is it also
storing
> data that is necessary?? I noticed on Books online that it states
> following a log file backup that it truncates the inactive portion of the
> transaction log, which would hopefully decrease this enormous log file
size.

Right.

You have a couple of options here.

If you don't care about transaction log backups (i.e. do the "restore once a
day, don't care if I lose lots of data") then set the DB to simple recovery
mode.

However, generally you DO care about transaction log backups, which means
you should do them.

In that case your log will generally stay much smaller and you can then
shrink it to a reasonable size.

Hope that helps some.

> Thank you for your help|||Thank you, starting to make sense now.

I just did a transaction log backup but unfortunately the Log.LDF is still
30GB. I was hoping that it would be extremely small now.

I am assuming that the Log.LDF has all my transactions going back for the
last two months since the databse was created. Is there some method for me
to reduce this file to say on the last couple days of transactions?

Why would SQL not have the option for this huge transaction log to be wiped
when I did the full database backup. Am I missing somethting here on the log
files purpose?

Thanks.

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:qCZgd.341053$bp1.260186@.twister.nyroc.rr.com. ..
> "Fred" <Fred@.hotmail.com> wrote in message
> news:418462d8$1@.duster.adelaide.on.net...
> > I have a table in sql 2000 that is storing Financial Tick data. The
> database
> > size now appears at about 30GB and I have decided to backup the database
> and
> > store the data offsite.
> > Here is where I am confused about whether only a Database (complete)
> backup
> > is necessary or whether I must also include a Transaction log backup for
> the
> > most recent data.
> The Full will only be complete up through any committed transaction at the
> time of its finish.
> Now, if you do a full once a day (say at 1:00 AM), you can restore to that
> point.
> But let's say your DB crashes at 11:00 PM. You've lost 22 hours worth of
> transactions.
> If you do a transaction backup say every hour, you could restore up
through
> the 10:00 PM log and lose less data.
> > 1. If I use a complete Database backup only, will all my data be stored
on
> > this? (Even if I have never done a Transaction log backup)
> See above.
> > 2. My Database size Data.MDF is 500MB and the Log.LDF is 30GB. If I
> complete
> > a database Backup can I now get rid of this Log file, or is it also
> storing
> > data that is necessary?? I noticed on Books online that it states
> > following a log file backup that it truncates the inactive portion of
the
> > transaction log, which would hopefully decrease this enormous log file
> size.
> Right.
> You have a couple of options here.
> If you don't care about transaction log backups (i.e. do the "restore once
a
> day, don't care if I lose lots of data") then set the DB to simple
recovery
> mode.
> However, generally you DO care about transaction log backups, which means
> you should do them.
> In that case your log will generally stay much smaller and you can then
> shrink it to a reasonable size.
> Hope that helps some.
>
> > Thank you for your help|||I managed to find the information here.
http://support.microsoft.com/defaul...kb;en-us;272318

Backed up the log with TRUNCATE _ONLY and then ran DBCC SHRINKFILE.

Worked like a charm.

"Fred" <Fred@.hotmail.com> wrote in message
news:41846b36@.duster.adelaide.on.net...
> Thank you, starting to make sense now.
> I just did a transaction log backup but unfortunately the Log.LDF is
still
> 30GB. I was hoping that it would be extremely small now.
> I am assuming that the Log.LDF has all my transactions going back for the
> last two months since the databse was created. Is there some method for me
> to reduce this file to say on the last couple days of transactions?
> Why would SQL not have the option for this huge transaction log to be
wiped
> when I did the full database backup. Am I missing somethting here on the
log
> files purpose?
> Thanks.
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:qCZgd.341053$bp1.260186@.twister.nyroc.rr.com. ..
> > "Fred" <Fred@.hotmail.com> wrote in message
> > news:418462d8$1@.duster.adelaide.on.net...
> > > I have a table in sql 2000 that is storing Financial Tick data. The
> > database
> > > size now appears at about 30GB and I have decided to backup the
database
> > and
> > > store the data offsite.
> > > > Here is where I am confused about whether only a Database (complete)
> > backup
> > > is necessary or whether I must also include a Transaction log backup
for
> > the
> > > most recent data.
> > The Full will only be complete up through any committed transaction at
the
> > time of its finish.
> > Now, if you do a full once a day (say at 1:00 AM), you can restore to
that
> > point.
> > But let's say your DB crashes at 11:00 PM. You've lost 22 hours worth
of
> > transactions.
> > If you do a transaction backup say every hour, you could restore up
> through
> > the 10:00 PM log and lose less data.
> > > > 1. If I use a complete Database backup only, will all my data be
stored
> on
> > > this? (Even if I have never done a Transaction log backup)
> > See above.
> > > > 2. My Database size Data.MDF is 500MB and the Log.LDF is 30GB. If I
> > complete
> > > a database Backup can I now get rid of this Log file, or is it also
> > storing
> > > data that is necessary?? I noticed on Books online that it states
> > > following a log file backup that it truncates the inactive portion of
> the
> > > transaction log, which would hopefully decrease this enormous log file
> > size.
> > Right.
> > You have a couple of options here.
> > If you don't care about transaction log backups (i.e. do the "restore
once
> a
> > day, don't care if I lose lots of data") then set the DB to simple
> recovery
> > mode.
> > However, generally you DO care about transaction log backups, which
means
> > you should do them.
> > In that case your log will generally stay much smaller and you can then
> > shrink it to a reasonable size.
> > Hope that helps some.
> > > > Thank you for your help
> >|||To keep your log size manageable in the future, either backup the
transaction log periodically (FULL or BULK_LOGGED recovery model or set the
recovery model to SIMPLE so that committed transactions are periodically
removed from the log. The proper choice depends on your recovery plan as
described by Greg. Once you've setup log backups or use the SIMPLE model,
you'll only need to shrink the log when the log grows unusually large due to
an large transaction.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Fred" <Fred@.hotmail.com> wrote in message
news:41847847$1@.duster.adelaide.on.net...
>I managed to find the information here.
> http://support.microsoft.com/defaul...kb;en-us;272318
> Backed up the log with TRUNCATE _ONLY and then ran DBCC SHRINKFILE.
> Worked like a charm.
>
> "Fred" <Fred@.hotmail.com> wrote in message
> news:41846b36@.duster.adelaide.on.net...
>> Thank you, starting to make sense now.
>>
>> I just did a transaction log backup but unfortunately the Log.LDF is
> still
>> 30GB. I was hoping that it would be extremely small now.
>>
>> I am assuming that the Log.LDF has all my transactions going back for the
>> last two months since the databse was created. Is there some method for
>> me
>> to reduce this file to say on the last couple days of transactions?
>>
>> Why would SQL not have the option for this huge transaction log to be
> wiped
>> when I did the full database backup. Am I missing somethting here on the
> log
>> files purpose?
>>
>> Thanks.
>>
>>
>>
>> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message
>> news:qCZgd.341053$bp1.260186@.twister.nyroc.rr.com. ..
>>> > "Fred" <Fred@.hotmail.com> wrote in message
>> > news:418462d8$1@.duster.adelaide.on.net...
>> > > I have a table in sql 2000 that is storing Financial Tick data. The
>> > database
>> > > size now appears at about 30GB and I have decided to backup the
> database
>> > and
>> > > store the data offsite.
>> >> > > Here is where I am confused about whether only a Database (complete)
>> > backup
>> > > is necessary or whether I must also include a Transaction log backup
> for
>> > the
>> > > most recent data.
>>> > The Full will only be complete up through any committed transaction at
> the
>> > time of its finish.
>>> > Now, if you do a full once a day (say at 1:00 AM), you can restore to
> that
>> > point.
>>> > But let's say your DB crashes at 11:00 PM. You've lost 22 hours worth
> of
>> > transactions.
>>> > If you do a transaction backup say every hour, you could restore up
>> through
>> > the 10:00 PM log and lose less data.
>>> >> > > 1. If I use a complete Database backup only, will all my data be
> stored
>> on
>> > > this? (Even if I have never done a Transaction log backup)
>>> > See above.
>>> >> > > 2. My Database size Data.MDF is 500MB and the Log.LDF is 30GB. If I
>> > complete
>> > > a database Backup can I now get rid of this Log file, or is it also
>> > storing
>> > > data that is necessary?? I noticed on Books online that it states
>> > > following a log file backup that it truncates the inactive portion of
>> the
>> > > transaction log, which would hopefully decrease this enormous log
>> > > file
>> > size.
>>> > Right.
>>> > You have a couple of options here.
>>> > If you don't care about transaction log backups (i.e. do the "restore
> once
>> a
>> > day, don't care if I lose lots of data") then set the DB to simple
>> recovery
>> > mode.
>>> > However, generally you DO care about transaction log backups, which
> means
>> > you should do them.
>>> > In that case your log will generally stay much smaller and you can then
>> > shrink it to a reasonable size.
>>> > Hope that helps some.
>>>> >> > > Thank you for your help
>> >> >>>>
>>|||Fred (Fred@.hotmail.com) writes:
> I just did a transaction log backup but unfortunately the Log.LDF is
> still 30GB. I was hoping that it would be extremely small now.
> I am assuming that the Log.LDF has all my transactions going back for the
> last two months since the databse was created. Is there some method for me
> to reduce this file to say on the last couple days of transactions?
> Why would SQL not have the option for this huge transaction log to be
> wiped when I did the full database backup. Am I missing somethting here
> on the log files purpose?

It is quite clear that it you have a 500 MB data file and a 30 GB log file
that you have not full understanding of the purpose transaction log, yes.

The normal procedures is to backup log at least as frequently as often
you backup the database. Often more frequently. If you do this, the log
will not grow to 30 GB for a database of your size.

SQL Server does not shrink the log automatically, because shrinking
something that will grow again is not a good idea, since growing takes
machine power.

As noted by Greg and Dan, you should make the decision whether you want
full/bulk-logged recovery or simple. If you choose simple, you don't have
to bother about the transaction log, but if the database crashes in the
afternoon, you lose all ticks for that day, assuming that you took a
full backup at midnight. So my guess is that you should stick with full
recovery and backup the translog regularly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

newbie :Backup database from within an VB application

Hello everybody
I need to take backup of the database at the end of everyday from within an VB application.I need to have an interface(VB) to take a back up as the targeted user is not well versed with mssqlserver environment. How do i go abt doin' this.
Thankx in advance :pYou'll need to use SQLDMO, which you can include in your VB Project. Under 'Project->Reference' include Microsoft SQLDMO Object Library, if you are using a version of SQL Server prior to 7.0 the library will be Microsoft SQLOLE Object Library.

For examples:
Using SQL-DMO To Backup and Verify a Database (http://www.asp101.com/articles/carvin/sqldmobackup/default.asp)
15 Seconds : Code Samples : Backing Up A Database (http://www.15seconds.com/howto/pg000945.htm)
Introduction to DMO (http://www.swynk.com/friends/warren/dmointro.asp)

newbie : backup set begins at LSN xxx, ... too late to apply ...

<< The log in this backup set begins at LSN xxx, which is too late to apply
to the database. An earlier log backup that includes LSN yyy can be
restored >>
ok forgive my ignrance here, using the maintenance plan wizards i have
backups in place that fire full db back up every 4 hours or so and back up
the transaction logs every hour.
i have set it up 2 different maint plans - one writes to the server's hard
diskl; the other writes to a network share using UNC path.
full restore from either location works ok; but when trying to do
transaction log restores i get the error message above. i think its
because:
the 2 transaction log backups are firing at the same time and writing to
different locations, so i would have to bounce back and forth between the 2
locations to get the transaction logs restored in proper order, correct?
obviously i should change this, so suggestions would be welcome.
thx
Use only one maintenance plan to either the local HD or the remote share.
HTH
Jerry
"jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
> << The log in this backup set begins at LSN xxx, which is too late to
> apply
> to the database. An earlier log backup that includes LSN yyy can be
> restored >>
> ok forgive my ignrance here, using the maintenance plan wizards i have
> backups in place that fire full db back up every 4 hours or so and back up
> the transaction logs every hour.
> i have set it up 2 different maint plans - one writes to the server's hard
> diskl; the other writes to a network share using UNC path.
> full restore from either location works ok; but when trying to do
> transaction log restores i get the error message above. i think its
> because:
> the 2 transaction log backups are firing at the same time and writing to
> different locations, so i would have to bounce back and forth between the
> 2
> locations to get the transaction logs restored in proper order, correct?
> obviously i should change this, so suggestions would be welcome.
> thx
|||...and optionally copy the backup files to the other location after each backup has been performed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uFz2fQU0FHA.2652@.TK2MSFTNGP14.phx.gbl...
> Use only one maintenance plan to either the local HD or the remote share.
> HTH
> Jerry
> "jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
> news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
>

newbie : backup set begins at LSN xxx, ... too late to apply ...

<< The log in this backup set begins at LSN xxx, which is too late to apply
to the database. An earlier log backup that includes LSN yyy can be
restored >>
ok forgive my ignrance here, using the maintenance plan wizards i have
backups in place that fire full db back up every 4 hours or so and back up
the transaction logs every hour.
i have set it up 2 different maint plans - one writes to the server's hard
diskl; the other writes to a network share using UNC path.
full restore from either location works ok; but when trying to do
transaction log restores i get the error message above. i think its
because:
the 2 transaction log backups are firing at the same time and writing to
different locations, so i would have to bounce back and forth between the 2
locations to get the transaction logs restored in proper order, correct?
obviously i should change this, so suggestions would be welcome.
thxUse only one maintenance plan to either the local HD or the remote share.
HTH
Jerry
"jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
> << The log in this backup set begins at LSN xxx, which is too late to
> apply
> to the database. An earlier log backup that includes LSN yyy can be
> restored >>
> ok forgive my ignrance here, using the maintenance plan wizards i have
> backups in place that fire full db back up every 4 hours or so and back up
> the transaction logs every hour.
> i have set it up 2 different maint plans - one writes to the server's hard
> diskl; the other writes to a network share using UNC path.
> full restore from either location works ok; but when trying to do
> transaction log restores i get the error message above. i think its
> because:
> the 2 transaction log backups are firing at the same time and writing to
> different locations, so i would have to bounce back and forth between the
> 2
> locations to get the transaction logs restored in proper order, correct?
> obviously i should change this, so suggestions would be welcome.
> thx|||...and optionally copy the backup files to the other location after each backup has been performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uFz2fQU0FHA.2652@.TK2MSFTNGP14.phx.gbl...
> Use only one maintenance plan to either the local HD or the remote share.
> HTH
> Jerry
> "jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
> news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
>> << The log in this backup set begins at LSN xxx, which is too late to apply
>> to the database. An earlier log backup that includes LSN yyy can be
>> restored >>
>> ok forgive my ignrance here, using the maintenance plan wizards i have
>> backups in place that fire full db back up every 4 hours or so and back up
>> the transaction logs every hour.
>> i have set it up 2 different maint plans - one writes to the server's hard
>> diskl; the other writes to a network share using UNC path.
>> full restore from either location works ok; but when trying to do
>> transaction log restores i get the error message above. i think its
>> because:
>> the 2 transaction log backups are firing at the same time and writing to
>> different locations, so i would have to bounce back and forth between the 2
>> locations to get the transaction logs restored in proper order, correct?
>> obviously i should change this, so suggestions would be welcome.
>> thx
>

newbie : backup set begins at LSN xxx, ... too late to apply ...

<< The log in this backup set begins at LSN xxx, which is too late to apply
to the database. An earlier log backup that includes LSN yyy can be
restored >>
ok forgive my ignrance here, using the maintenance plan wizards i have
backups in place that fire full db back up every 4 hours or so and back up
the transaction logs every hour.
i have set it up 2 different maint plans - one writes to the server's hard
diskl; the other writes to a network share using UNC path.
full restore from either location works ok; but when trying to do
transaction log restores i get the error message above. i think its
because:
the 2 transaction log backups are firing at the same time and writing to
different locations, so i would have to bounce back and forth between the 2
locations to get the transaction logs restored in proper order, correct?
obviously i should change this, so suggestions would be welcome.
thxUse only one maintenance plan to either the local HD or the remote share.
HTH
Jerry
"jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
> << The log in this backup set begins at LSN xxx, which is too late to
> apply
> to the database. An earlier log backup that includes LSN yyy can be
> restored >>
> ok forgive my ignrance here, using the maintenance plan wizards i have
> backups in place that fire full db back up every 4 hours or so and back up
> the transaction logs every hour.
> i have set it up 2 different maint plans - one writes to the server's hard
> diskl; the other writes to a network share using UNC path.
> full restore from either location works ok; but when trying to do
> transaction log restores i get the error message above. i think its
> because:
> the 2 transaction log backups are firing at the same time and writing to
> different locations, so i would have to bounce back and forth between the
> 2
> locations to get the transaction logs restored in proper order, correct?
> obviously i should change this, so suggestions would be welcome.
> thx|||...and optionally copy the backup files to the other location after each ba
ckup has been performed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uFz2fQU0FHA.2652@.TK2MSFTNGP14.phx.gbl...
> Use only one maintenance plan to either the local HD or the remote share.
> HTH
> Jerry
> "jimbo-black" <passwords.20.jimbo-black@.antichef.net> wrote in message
> news:Xns96EFDE4BBEF04jimboblac@.207.115.63.158...
>

newbie - have backup file and trn file - how do I get it up and going?

Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.Check out RESTORE DATABASE and RESTORE LOG in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
Hello there,
I have a project to look into where I need to look into an MSSQL database.
I have been sent a file that contains a BAK file and a TRN file. I am told
this is a dump of the MSSQL database.
I have installed the evaluation version of SQL Server 2000 database onto my
Windows 2000 PC.
My first question is, am I going about it the right way, and what is the
first thing I want to do with regard to actually opening the database.
I looked into the Enterprise manager about restoring a database, but it did
not seem to let me choose a file location to restore from.
Any guidance or input would be appreciated.
Kind regards
Dave.|||It is probably easiest for you to read about the below commands in Books Online
RESTORE HEADERONLY
RESTORE FILELISTONLY
Use above two to investigate what is on those backup files. Then do the restore operations using
RESTORE DATABASE
RESTORE LOG
You probably have to use the MOVE option for above commands, guided but he output from RESTORE
FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Smithz" <dave1900@.blueyonder.co.uk> wrote in message
news:HR%5g.63459$wl.12794@.text.news.blueyonder.co.uk...
> Hello there,
> I have a project to look into where I need to look into an MSSQL database.
> I have been sent a file that contains a BAK file and a TRN file. I am told this is a dump of the
> MSSQL database.
> I have installed the evaluation version of SQL Server 2000 database onto my Windows 2000 PC.
> My first question is, am I going about it the right way, and what is the first thing I want to do
> with regard to actually opening the database.
> I looked into the Enterprise manager about restoring a database, but it did not seem to let me
> choose a file location to restore from.
> Any guidance or input would be appreciated.
> Kind regards
> Dave.
>
>