Backup didn't contain last 8 hours of data

By some for me unknown reason my backup didn't contain
the last 8 hours of data when I restored them. However
the data was in the detached files that I also copied
directly after I backed up the databases. There where no
user except me while performing this operation.
Anyone had problems like this? I thought that a backup
should write all data from the DB's to the devices.
(Recovery model simple and no pending transaction on the
DB's.)
//LEBAre you sure that restore the correct backup? Perhaps you didn't specify NOINIT for the backup and
when restore didn't specify the FILE parameter -> restored an earlier backup.
I have never seen or heard about SQL Server not including the committed transactions in a database
backup. Nor can I see how that can happen architecturally.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"LEB" <lars-erik.blom@.tuesday.se> wrote in message news:09b701c36dfb$496e7360$a301280a@.phx.gbl...
> By some for me unknown reason my backup didn't contain
> the last 8 hours of data when I restored them. However
> the data was in the detached files that I also copied
> directly after I backed up the databases. There where no
> user except me while performing this operation.
> Anyone had problems like this? I thought that a backup
> should write all data from the DB's to the devices.
> (Recovery model simple and no pending transaction on the
> DB's.)
> //LEB|||Neither does me. Since I can see that the DB device is
created at the time when I did the backup I'm sure that
the file is the proper one. I scratched the previous to
be sure. I also specified different file locations, new
since I rebuild the server. As seen from a technical
standpoint there is not a failure running this. The
backup and restore runs smoothly without errors. I've
never been this confused regarding backup/restore before.
Suggestions?
//LEB
>--Original Message--
>Are you sure that restore the correct backup? Perhaps
you didn't specify NOINIT for the backup and
>when restore didn't specify the FILE parameter ->
restored an earlier backup.
>I have never seen or heard about SQL Server not
including the committed transactions in a database
>backup. Nor can I see how that can happen
architecturally.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"LEB" <lars-erik.blom@.tuesday.se> wrote in message
news:09b701c36dfb$496e7360$a301280a@.phx.gbl...
>> By some for me unknown reason my backup didn't contain
>> the last 8 hours of data when I restored them. However
>> the data was in the detached files that I also copied
>> directly after I backed up the databases. There where
no
>> user except me while performing this operation.
>> Anyone had problems like this? I thought that a backup
>> should write all data from the DB's to the devices.
>> (Recovery model simple and no pending transaction on
the
>> DB's.)
>> //LEB
>
>.
>|||The only last thing I can think of is RESTORE HEADERONLY to be 100% certain...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"LEB" <lars-erik.blom@.tuesday.se> wrote in message news:037c01c36e19$96b70380$a001280a@.phx.gbl...
> Neither does me. Since I can see that the DB device is
> created at the time when I did the backup I'm sure that
> the file is the proper one. I scratched the previous to
> be sure. I also specified different file locations, new
> since I rebuild the server. As seen from a technical
> standpoint there is not a failure running this. The
> backup and restore runs smoothly without errors. I've
> never been this confused regarding backup/restore before.
> Suggestions?
> //LEB
> >--Original Message--
> >Are you sure that restore the correct backup? Perhaps
> you didn't specify NOINIT for the backup and
> >when restore didn't specify the FILE parameter ->
> restored an earlier backup.
> >I have never seen or heard about SQL Server not
> including the committed transactions in a database
> >backup. Nor can I see how that can happen
> architecturally.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"LEB" <lars-erik.blom@.tuesday.se> wrote in message
> news:09b701c36dfb$496e7360$a301280a@.phx.gbl...
> >> By some for me unknown reason my backup didn't contain
> >> the last 8 hours of data when I restored them. However
> >> the data was in the detached files that I also copied
> >> directly after I backed up the databases. There where
> no
> >> user except me while performing this operation.
> >> Anyone had problems like this? I thought that a backup
> >> should write all data from the DB's to the devices.
> >> (Recovery model simple and no pending transaction on
> the
> >> DB's.)
> >>
> >> //LEB
> >
> >
> >.
> >

Backup database

I have couple of questions about backing up a database

1. How to create a backup file (using script) which only contain the data of a specific time range?

2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.

3. how can I backup a database to a remote computer?

Thanks for your concern on my Qestions

hi,

Frankie wrote:

I have couple of questions about backing up a database

1. How to create a backup file (using script) which only contain the data of a specific time range?

2. How to restore the database with serval backup files; for example, I got backup for june and july, how can I restore them into one mdf file which contains the data of both june and july.

I do strongly suggest you to start reading about backup functionality in SQL Server starting from this overview, as it seems you really did not understand what a backup is

3. how can I backup a database to a remote computer?

this can be easely done, even if not directly recommended (at least not by me).. usually you perform a "local" backup and, later, you push or pull that backup remotely via scheduled tasks, or scripts or the like, in order not to load to much the SQL Server service with tasks not directly related to it's main activity.. anyway, you have to grant the account running SQL Server service enougth NTFS permissions on the remote share you are dealing with... in our case, SQLExpress usually runs under Local System, Local Service or Network Service builtin accounts, and you can not add remote permissions to these ones.. you have to define a domain account to use for the service and grant this one appropriate permissions (AD and NTFS) to complete the task on the remote share..

regards|||

In addition to Andrea, the transfer to normal UNC paths are not reliable, leading to the problem that the transfer could stop somewhere in the middle of the process, unless you use something like a SAN or NAS storage which has a much more reliable way for tranferring. Use the local copy to copy the files (e.g. usign Xcopy with a restartable copy process) to make sure the file was really copied).

Jens K. Suessmeyer

http://www.sqlserver2005.de

Backup data from database.

Hi all experts,
I would like to know if there are any method to generate an SQL
insert statements which contain all the data inside one of my database?
I do not want to backup the mdf files. Thanks alot.
Thanks
Rena.You can use bcp.exe or DTS to export just the data. If you really want
Insert statements, Narayana created a procedure -
http://vyaskn.tripod.com/code/generate_inserts.txt.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"rena" <rena@.hotmail.com> wrote in message
news:%23yvXBvHFEHA.3132@.TK2MSFTNGP12.phx.gbl...
> Hi all experts,
> I would like to know if there are any method to generate an SQL
> insert statements which contain all the data inside one of my database?
> I do not want to backup the mdf files. Thanks alot.
> Thanks
> Rena.
>|||... and a few other options:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage
news:eHYiUBKFEHA.2416@.TK2MSFTNGP12.phx.gbl...
> You can use bcp.exe or DTS to export just the data. If you really want
> Insert statements, Narayana created a procedure -
> http://vyaskn.tripod.com/code/generate_inserts.txt.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "rena" <rena@.hotmail.com> wrote in message
> news:%23yvXBvHFEHA.3132@.TK2MSFTNGP12.phx.gbl...
>