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 and Restore specific objects

Is it so hard to backup and restore specific objects as part of the backup
and restore process ?
It could have been a beneficial feature and was wondering if it was hard for
MS to implement which is why it isnt a feature yet
ThanksHi Hassan,
It depends on which objects you are backing up.
Databases and files/file groups transaction logs are easy. But to backup a
table or view as part of the backup command is not. I think (think) it was
version 6.5 which allowed you to backup a table that was removed in version
7.0.
The way backups are done now changed. Now backups are page dumps on disk or
tape. The reason or one of them is for performance. Dumping pages direct
to disk allows far greater performance than tranversing object structures
and object specific memory. It also reduces CPU needs. Ms was very
sensitive to this need and when you concider databases are growing and
growing no-one wants to hang around for days to backup data.
So yes it is very hard to backup individual objects and still have a fast
performing backup service. On the other hand it would be simple for a DBA
to create there own (home grown) backup script that just copied a table or
object from one place to another. Or you may want to place tables or object
on different file groups and that way you could just backup the file groups.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IJXZ1Q%23FHA.472@.TK2MSFTNGP15.phx.gbl...
> Is it so hard to backup and restore specific objects as part of the backup
> and restore process ?
> It could have been a beneficial feature and was wondering if it was hard
> for MS to implement which is why it isnt a feature yet
> Thanks
>

Backup and Restore specific objects

Is it so hard to backup and restore specific objects as part of the backup
and restore process ?
It could have been a beneficial feature and was wondering if it was hard for
MS to implement which is why it isnt a feature yet
ThanksHi Hassan,
It depends on which objects you are backing up.
Databases and files/file groups transaction logs are easy. But to backup a
table or view as part of the backup command is not. I think (think) it was
version 6.5 which allowed you to backup a table that was removed in version
7.0.
The way backups are done now changed. Now backups are page dumps on disk or
tape. The reason or one of them is for performance. Dumping pages direct
to disk allows far greater performance than tranversing object structures
and object specific memory. It also reduces CPU needs. Ms was very
sensitive to this need and when you concider databases are growing and
growing no-one wants to hang around for days to backup data.
So yes it is very hard to backup individual objects and still have a fast
performing backup service. On the other hand it would be simple for a DBA
to create there own (home grown) backup script that just copied a table or
object from one place to another. Or you may want to place tables or object
on different file groups and that way you could just backup the file groups.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IJXZ1Q%23FHA.472@.TK2MSFTNGP15.phx.gbl...
> Is it so hard to backup and restore specific objects as part of the backup
> and restore process ?
> It could have been a beneficial feature and was wondering if it was hard
> for MS to implement which is why it isnt a feature yet
> Thanks
>

Backup and Restore specific objects

Is it so hard to backup and restore specific objects as part of the backup
and restore process ?
It could have been a beneficial feature and was wondering if it was hard for
MS to implement which is why it isnt a feature yet
Thanks
Hi Hassan,
It depends on which objects you are backing up.
Databases and files/file groups transaction logs are easy. But to backup a
table or view as part of the backup command is not. I think (think) it was
version 6.5 which allowed you to backup a table that was removed in version
7.0.
The way backups are done now changed. Now backups are page dumps on disk or
tape. The reason or one of them is for performance. Dumping pages direct
to disk allows far greater performance than tranversing object structures
and object specific memory. It also reduces CPU needs. Ms was very
sensitive to this need and when you concider databases are growing and
growing no-one wants to hang around for days to backup data.
So yes it is very hard to backup individual objects and still have a fast
performing backup service. On the other hand it would be simple for a DBA
to create there own (home grown) backup script that just copied a table or
object from one place to another. Or you may want to place tables or object
on different file groups and that way you could just backup the file groups.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IJXZ1Q%23FHA.472@.TK2MSFTNGP15.phx.gbl...
> Is it so hard to backup and restore specific objects as part of the backup
> and restore process ?
> It could have been a beneficial feature and was wondering if it was hard
> for MS to implement which is why it isnt a feature yet
> Thanks
>

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
--
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!
What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>
|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>
|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>