Backup to Network Drive: restoring

Showing posts with label restoring. Show all posts

Sunday, March 11, 2012

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
Laurence
Hi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP
--

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
--
David Portas
SQL Server MVP
--

Wednesday, March 7, 2012

Backups and LSNs

Hi,
My question:
Is it possible to create the full backup so that taped copies can be
made and then rather than restoring the full backup on the target
machine, the incrementals resume and dovetail with the last
incremental prior to the full backup?
Current Scenario:
1. A sequence of incremental logs backed up and shipped to another
server (reporting) where they are restored (i.e. the target database
is in standby)
2. Each night the creation of incremental backups is suspended while a
full backup takes place. This full backup is also restored on the
other server.
3. Incrementals resume after a certain period of time which is
sufficient to allow the full backup to be restored.
4. The full backup is also copied to tape.
Motivation:
I want to avoid moving large backup files around on the network. I
want to decrease the time lag with the live data from the reporting
perspective. (I know about transactional replication and want/have to
stay with the current approach. Thanks)
Background:
There was an occasion when the full backup failed to move to the
target machine due to a space issue there. However, I noticed that the
incrementals post-(full backup) were still being copied across and
being restored even though the full backup didn't make it. This kind
of answers my question but the thing is that I was a bit confused at
the time and just wanted to get the problem sorted asap without doing
root cause analysis. Was it my imagination or it this possible?
I would have thought that the LSN count would have been reset by the
full backup. Here is a little pictorial of the sequence that I would
like.
Production Reporting
Box Box
... ...
i1 i1
i2 i2
i3 i3
fbup i4
i4 i5
i5 i6
... ...
Regards
Liam Caffrey
Liam,
If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
when you perform a database backup. This allow you for instance to pick you second newest db backup
and restore it and then apply *all* subsequent log backups.
In other words, a database backup does not break the sequence of transaction log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060154.7bc1bebf@.posting.google.c om...
> Hi,
> My question:
> Is it possible to create the full backup so that taped copies can be
> made and then rather than restoring the full backup on the target
> machine, the incrementals resume and dovetail with the last
> incremental prior to the full backup?
> Current Scenario:
> 1. A sequence of incremental logs backed up and shipped to another
> server (reporting) where they are restored (i.e. the target database
> is in standby)
> 2. Each night the creation of incremental backups is suspended while a
> full backup takes place. This full backup is also restored on the
> other server.
> 3. Incrementals resume after a certain period of time which is
> sufficient to allow the full backup to be restored.
> 4. The full backup is also copied to tape.
> Motivation:
> I want to avoid moving large backup files around on the network. I
> want to decrease the time lag with the live data from the reporting
> perspective. (I know about transactional replication and want/have to
> stay with the current approach. Thanks)
> Background:
> There was an occasion when the full backup failed to move to the
> target machine due to a space issue there. However, I noticed that the
> incrementals post-(full backup) were still being copied across and
> being restored even though the full backup didn't make it. This kind
> of answers my question but the thing is that I was a bit confused at
> the time and just wanted to get the problem sorted asap without doing
> root cause analysis. Was it my imagination or it this possible?
> I would have thought that the LSN count would have been reset by the
> full backup. Here is a little pictorial of the sequence that I would
> like.
> Production Reporting
> Box Box
> ... ...
> i1 i1
> i2 i2
> i3 i3
> fbup i4
> i4 i5
> i5 i6
> ... ...
>
> Regards
> Liam Caffrey
|||Tibor,
Does this mean, for example, that in one scheduled job I can have
incremental backups running all day long at, say, 15 minute intervals,
and then a separate scheduled job can have a full backup running once
per night and that the two jobs won't interfere with each other (apart
from performance) as long as the transaction log is not truncated by
anything.
The full backup normally takes about 40 minutes to run.
If so, I could then run incrementals to the reporting box for weeks on
end or until the transaction log is truncated. Is this correct. Is it
a good idea to run log ships for so long. Any disadvantages?
Regards
Liam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> Liam,
> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
> when you perform a database backup. This allow you for instance to pick you second newest db backup
> and restore it and then apply *all* subsequent log backups.
> In other words, a database backup does not break the sequence of transaction log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
|||> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct.
Correct.

> Any disadvantages?
Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
truncation in the production db for some reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.c om...[vbcol=seagreen]
> Tibor,
> Does this mean, for example, that in one scheduled job I can have
> incremental backups running all day long at, say, 15 minute intervals,
> and then a separate scheduled job can have a full backup running once
> per night and that the two jobs won't interfere with each other (apart
> from performance) as long as the transaction log is not truncated by
> anything.
> The full backup normally takes about 40 minutes to run.
> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct. Is it
> a good idea to run log ships for so long. Any disadvantages?
> Regards
> Liam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
|||Interesting.
What happens if a full backup job is running and a separate
incremental log backup is kicked off during the middle of the full
backup? Or say SQLServer attempts to start a full backup just as an
incremental is running.
Will it gracefully prevent the incremental from running or will it go
ahead and create the incremental while the full is running? Are there
any issues with doing this? Is it better to create time windows for
the full backup.
Say then, in such a context, I wanted to restore a full backup plus
all subsequent incremental backups. How do I know which incremental to
apply after the full backup has been restored? The earliest? How can
the LSNs in the earliest post-(full backup) incremental file be
consistent with both the full backup file and the last pre-(full
backup) incremental file.
If you could refer me to some detailed info on this, I would
appreciate it. BOL doesn't seem to have anything in this direction.
Regards and thanks,
Liam Caffrey
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Correct.
>
> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
> truncation in the production db for some reason...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.c om...
|||Super-short background:
When backup starts, SQL Server does a checkpoint to record in the log the starting point for the
backup. Then it simply copies the pages to the storage media. At the end-time (this is what the
database will look like when you restore), the database backup also picks up the log pages produced
during the backup. This is so SQL Server can just apply all the pages and then re-do the
modifications that took place during the backup page copying. Possibly also a checkpoint is
performed at end of the backup process.
Check out RESTORE HEADERONLY and the four columns:
FirstLsn
LastLsn
CheckpintLsn
DifferentialBaseLsn
Lsn is "Log Sequence Number", each log record is given such a sequence number. My guess is that a
log backup "synchronizes" to the following log backup using the database backups CheckpointLsn.
Something like the log backups FirstLsn should match the database backup's CheckpointLsn. This is
only from looking at a couple of backups... I haven't found an elaboration of this topic, but to be
honest, I haven't looked for such either.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410070334.7ab44ccf@.posting.google.c om...[vbcol=seagreen]
> Interesting.
> What happens if a full backup job is running and a separate
> incremental log backup is kicked off during the middle of the full
> backup? Or say SQLServer attempts to start a full backup just as an
> incremental is running.
> Will it gracefully prevent the incremental from running or will it go
> ahead and create the incremental while the full is running? Are there
> any issues with doing this? Is it better to create time windows for
> the full backup.
> Say then, in such a context, I wanted to restore a full backup plus
> all subsequent incremental backups. How do I know which incremental to
> apply after the full backup has been restored? The earliest? How can
> the LSNs in the earliest post-(full backup) incremental file be
> consistent with both the full backup file and the last pre-(full
> backup) incremental file.
> If you could refer me to some detailed info on this, I would
> appreciate it. BOL doesn't seem to have anything in this direction.
> Regards and thanks,
> Liam Caffrey
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...

Backups and LSNs

Hi,
My question:
Is it possible to create the full backup so that taped copies can be
made and then rather than restoring the full backup on the target
machine, the incrementals resume and dovetail with the last
incremental prior to the full backup?
Current Scenario:
1. A sequence of incremental logs backed up and shipped to another
server (reporting) where they are restored (i.e. the target database
is in standby)
2. Each night the creation of incremental backups is suspended while a
full backup takes place. This full backup is also restored on the
other server.
3. Incrementals resume after a certain period of time which is
sufficient to allow the full backup to be restored.
4. The full backup is also copied to tape.
Motivation:
I want to avoid moving large backup files around on the network. I
want to decrease the time lag with the live data from the reporting
perspective. (I know about transactional replication and want/have to
stay with the current approach. Thanks)
Background:
There was an occasion when the full backup failed to move to the
target machine due to a space issue there. However, I noticed that the
incrementals post-(full backup) were still being copied across and
being restored even though the full backup didn't make it. This kind
of answers my question but the thing is that I was a bit confused at
the time and just wanted to get the problem sorted asap without doing
root cause analysis. Was it my imagination or it this possible?
I would have thought that the LSN count would have been reset by the
full backup. Here is a little pictorial of the sequence that I would
like.
Production Reporting
Box Box
... ...
i1 i1
i2 i2
i3 i3
fbup i4
i4 i5
i5 i6
... ...
Regards
Liam CaffreyLiam,
If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
when you perform a database backup. This allow you for instance to pick you second newest db backup
and restore it and then apply *all* subsequent log backups.
In other words, a database backup does not break the sequence of transaction log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060154.7bc1bebf@.posting.google.com...
> Hi,
> My question:
> Is it possible to create the full backup so that taped copies can be
> made and then rather than restoring the full backup on the target
> machine, the incrementals resume and dovetail with the last
> incremental prior to the full backup?
> Current Scenario:
> 1. A sequence of incremental logs backed up and shipped to another
> server (reporting) where they are restored (i.e. the target database
> is in standby)
> 2. Each night the creation of incremental backups is suspended while a
> full backup takes place. This full backup is also restored on the
> other server.
> 3. Incrementals resume after a certain period of time which is
> sufficient to allow the full backup to be restored.
> 4. The full backup is also copied to tape.
> Motivation:
> I want to avoid moving large backup files around on the network. I
> want to decrease the time lag with the live data from the reporting
> perspective. (I know about transactional replication and want/have to
> stay with the current approach. Thanks)
> Background:
> There was an occasion when the full backup failed to move to the
> target machine due to a space issue there. However, I noticed that the
> incrementals post-(full backup) were still being copied across and
> being restored even though the full backup didn't make it. This kind
> of answers my question but the thing is that I was a bit confused at
> the time and just wanted to get the problem sorted asap without doing
> root cause analysis. Was it my imagination or it this possible?
> I would have thought that the LSN count would have been reset by the
> full backup. Here is a little pictorial of the sequence that I would
> like.
> Production Reporting
> Box Box
> ... ...
> i1 i1
> i2 i2
> i3 i3
> fbup i4
> i4 i5
> i5 i6
> ... ...
>
> Regards
> Liam Caffrey|||Tibor,
Does this mean, for example, that in one scheduled job I can have
incremental backups running all day long at, say, 15 minute intervals,
and then a separate scheduled job can have a full backup running once
per night and that the two jobs won't interfere with each other (apart
from performance) as long as the transaction log is not truncated by
anything.
The full backup normally takes about 40 minutes to run.
If so, I could then run incrementals to the reporting box for weeks on
end or until the transaction log is truncated. Is this correct. Is it
a good idea to run log ships for so long. Any disadvantages?
Regards
Liam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> Liam,
> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the tlog
> when you perform a database backup. This allow you for instance to pick you second newest db backup
> and restore it and then apply *all* subsequent log backups.
> In other words, a database backup does not break the sequence of transaction log backups.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct.
Correct.
> Any disadvantages?
Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
truncation in the production db for some reason...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> Tibor,
> Does this mean, for example, that in one scheduled job I can have
> incremental backups running all day long at, say, 15 minute intervals,
> and then a separate scheduled job can have a full backup running once
> per night and that the two jobs won't interfere with each other (apart
> from performance) as long as the transaction log is not truncated by
> anything.
> The full backup normally takes about 40 minutes to run.
> If so, I could then run incrementals to the reporting box for weeks on
> end or until the transaction log is truncated. Is this correct. Is it
> a good idea to run log ships for so long. Any disadvantages?
> Regards
> Liam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> Liam,
>> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
>> tlog
>> when you perform a database backup. This allow you for instance to pick you second newest db
>> backup
>> and restore it and then apply *all* subsequent log backups.
>> In other words, a database backup does not break the sequence of transaction log backups.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>|||Interesting.
What happens if a full backup job is running and a separate
incremental log backup is kicked off during the middle of the full
backup? Or say SQLServer attempts to start a full backup just as an
incremental is running.
Will it gracefully prevent the incremental from running or will it go
ahead and create the incremental while the full is running? Are there
any issues with doing this? Is it better to create time windows for
the full backup.
Say then, in such a context, I wanted to restore a full backup plus
all subsequent incremental backups. How do I know which incremental to
apply after the full backup has been restored? The earliest? How can
the LSNs in the earliest post-(full backup) incremental file be
consistent with both the full backup file and the last pre-(full
backup) incremental file.
If you could refer me to some detailed info on this, I would
appreciate it. BOL doesn't seem to have anything in this direction.
Regards and thanks,
Liam Caffrey
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct.
> Correct.
>
> > Any disadvantages?
> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some log
> truncation in the production db for some reason...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410060629.48f1584f@.posting.google.com...
> > Tibor,
> >
> > Does this mean, for example, that in one scheduled job I can have
> > incremental backups running all day long at, say, 15 minute intervals,
> > and then a separate scheduled job can have a full backup running once
> > per night and that the two jobs won't interfere with each other (apart
> > from performance) as long as the transaction log is not truncated by
> > anything.
> >
> > The full backup normally takes about 40 minutes to run.
> >
> > If so, I could then run incrementals to the reporting box for weeks on
> > end or until the transaction log is truncated. Is this correct. Is it
> > a good idea to run log ships for so long. Any disadvantages?
> >
> > Regards
> >
> > Liam
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
> >> Liam,
> >>
> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate the
> >> tlog
> >> when you perform a database backup. This allow you for instance to pick you second newest db
> >> backup
> >> and restore it and then apply *all* subsequent log backups.
> >>
> >> In other words, a database backup does not break the sequence of transaction log backups.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>|||Super-short background:
When backup starts, SQL Server does a checkpoint to record in the log the starting point for the
backup. Then it simply copies the pages to the storage media. At the end-time (this is what the
database will look like when you restore), the database backup also picks up the log pages produced
during the backup. This is so SQL Server can just apply all the pages and then re-do the
modifications that took place during the backup page copying. Possibly also a checkpoint is
performed at end of the backup process.
Check out RESTORE HEADERONLY and the four columns:
FirstLsn
LastLsn
CheckpintLsn
DifferentialBaseLsn
Lsn is "Log Sequence Number", each log record is given such a sequence number. My guess is that a
log backup "synchronizes" to the following log backup using the database backups CheckpointLsn.
Something like the log backups FirstLsn should match the database backup's CheckpointLsn. This is
only from looking at a couple of backups... I haven't found an elaboration of this topic, but to be
honest, I haven't looked for such either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LCaffrey" <lxc@.eosys.ie> wrote in message news:4a6dded0.0410070334.7ab44ccf@.posting.google.com...
> Interesting.
> What happens if a full backup job is running and a separate
> incremental log backup is kicked off during the middle of the full
> backup? Or say SQLServer attempts to start a full backup just as an
> incremental is running.
> Will it gracefully prevent the incremental from running or will it go
> ahead and create the incremental while the full is running? Are there
> any issues with doing this? Is it better to create time windows for
> the full backup.
> Say then, in such a context, I wanted to restore a full backup plus
> all subsequent incremental backups. How do I know which incremental to
> apply after the full backup has been restored? The earliest? How can
> the LSNs in the earliest post-(full backup) incremental file be
> consistent with both the full backup file and the last pre-(full
> backup) incremental file.
> If you could refer me to some detailed info on this, I would
> appreciate it. BOL doesn't seem to have anything in this direction.
> Regards and thanks,
> Liam Caffrey
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:<OkiDVP7qEHA.1296@.TK2MSFTNGP12.phx.gbl>...
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct.
>> Correct.
>>
>> > Any disadvantages?
>> Not as far as I can see. Just be prepared to transfer a full backup, just in case you have some
>> log
>> truncation in the production db for some reason...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "LCaffrey" <lxc@.eosys.ie> wrote in message
>> news:4a6dded0.0410060629.48f1584f@.posting.google.com...
>> > Tibor,
>> >
>> > Does this mean, for example, that in one scheduled job I can have
>> > incremental backups running all day long at, say, 15 minute intervals,
>> > and then a separate scheduled job can have a full backup running once
>> > per night and that the two jobs won't interfere with each other (apart
>> > from performance) as long as the transaction log is not truncated by
>> > anything.
>> >
>> > The full backup normally takes about 40 minutes to run.
>> >
>> > If so, I could then run incrementals to the reporting box for weeks on
>> > end or until the transaction log is truncated. Is this correct. Is it
>> > a good idea to run log ships for so long. Any disadvantages?
>> >
>> > Regards
>> >
>> > Liam
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> > news:<e4cC1t4qEHA.3244@.tk2msftngp13.phx.gbl>...
>> >> Liam,
>> >>
>> >> If I understand your question, then the answer is "yes". I.e., SQL Server does not truncate
>> >> the
>> >> tlog
>> >> when you perform a database backup. This allow you for instance to pick you second newest db
>> >> backup
>> >> and restore it and then apply *all* subsequent log backups.
>> >>
>> >> In other words, a database backup does not break the sequence of transaction log backups.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>

Friday, February 24, 2012

BackupIoRequest::WaitForIoCompletion failure when restoring database

I have MS SQL 200 Server Developer Edition Installed from MSDN collection. (New installation with service pack 2). When I try to restore 7GB or bigger database, following error occurs in eventlog:

Source: MSSQLSERVER
Category: (6)
ID: 18210
Date: 11.9.2007
Time: 14:50:09
Description:
BackupIoRequest::WaitForIoCompletion: write failure on backup device ''. Operating system error 1450(here is message in local language like "here is no resources to complete requested service").

But I am not writting to backup device, I am restoring. Thank for any helpful answer.

Not sure about the cause of the error.. Check if its related to this KB : http://support.microsoft.com/default.aspx?scid=kb;en-us;304101

|||

Thank you, dinakar.

KB howto i tried, but with no success. Sad

Backup/Restore: Restoring to new server without rerunning a snapshot

I am designing a solution that utilizes replication from a front-end database to a back-end database. Historical data will be truncated from the front-end database in such a way that these truncation deletes will not be replicated - thus, the back-end database acts as a historical archive for the front-end database.

I am concerned about backup and restore scenarios for this environment.

Once we have begun truncating data from the front-end database, we will never be able to re-snapshot the database to rebuild the back-end database.

My concern comes if the servers are destroyed and the environment must be rebuilt. For SQL Server 2000, Books Online says:

"When you restore a backup of a replicated database to a server or database other than the one on which it was created, your replication settings cannot be preserved. For publishing databases and merge subscribing databases, a full restore of the database and logs is followed by an automatic removal of replication meta data from the database when the database or server you restore to differs from the one on which the backup was created."

Books Online for SQL Server 2005 has a similar statement. Does this mean that I would be unable to restore my front-end and back-end databases, and reestablish replication without taking a new snapshot? Does SQL 2005 provide any advantages for disaster recovery given this scenario? Does restoring with the KEEP_REPLICATION option override the behavior specified in the above BOL quote?

That isn't correct. You can restore a backup without preserving replication settings and then reconfigure replication in that environment. You would not want to issue a snapshot, so you would simply tell the replication engine that the subscriber already has the schema and data.

Now, there are two different behavior paths. With SQL Server 2000, you would tell it that the subscriber already has the schema and data "nosynch" and a subscription would be created, but changes would fail, because you would have to manually create the replication stored procedures that are called on the subscriber. But, once created, all of the data would flow normally.

With SQL Server 2005, you create the subscription with replication support only. This would not cause a snapshot to be sent to the subscriber, but it would create all of the necessary replication objects.

|||

Hello,

I have read your comments about restoring backup in a diferent server. This is my case and I am really desesperated becuase I am totally unable to do it . I only have a backup from the old server and I have no idea about scripts procedures or programming I am only trying from the SQL SERVER Enterprise Manager. Please let me know if it does really exist the posibility.

Thanks a lot

Carlos

|||did you found any solution for this?

Backup/Restore: Restoring to new server without rerunning a snapshot

I am concerned about backup and restore scenarios for this environment.

Once we have begun truncating data from the front-end database, we will never be able to re-snapshot the database to rebuild the back-end database.

My concern comes if the servers are destroyed and the environment must be rebuilt. For SQL Server 2000, Books Online says:

"When you restore a backup of a replicated database to a server or database other than the one on which it was created, your replication settings cannot be preserved. For publishing databases and merge subscribing databases, a full restore of the database and logs is followed by an automatic removal of replication meta data from the database when the database or server you restore to differs from the one on which the backup was created."

|||

Hello,

Thanks a lot

Carlos

Backup/Restore SQL 6.5 databases

Group,
If i remember correct, when restoring an SQL 6.5 database file that had been
extended in size, you first had to re-create the database file on the same
chuck sizes and there also were a SQL command that showed the sizes of that
"chunks".
1. Is this correct or does my mind play with me?
2. What was this SQL command (stored procedure or?)
Regards /PeterYou have to get the same database fragment allocation (the sequence and size of allocation of
database and log fragments from devices). The allocations doesn't have to be from the same device as
it was originally, but the number, size and type (data or log)of allocations has to be the same. On
the source server, run sp_help_revdatabase.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <Peter.nospam@.news.com> wrote in message news:OYpMwlWxDHA.1704@.TK2MSFTNGP10.phx.gbl...
> Group,
> If i remember correct, when restoring an SQL 6.5 database file that had been
> extended in size, you first had to re-create the database file on the same
> chuck sizes and there also were a SQL command that showed the sizes of that
> "chunks".
> 1. Is this correct or does my mind play with me?
> 2. What was this SQL command (stored procedure or?)
> Regards /Peter
>

Sunday, February 19, 2012

Backup/Restore

What is the best document for restoring databases from one SQL 2000 server to a new SQL 2000 server?
What do you mean by document?
Refer to Books Online (within the SQL Server program group). It will =
tell you how to use the Transact-SQL commands BACKUP and RESTORE. =20
You might also find these KB articles helpful:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=3D307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=3D224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=3D314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=3D221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=3D168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is =
Incomplete
http://www.support.microsoft.com/?id=3D274188
How to Resolve Permission Issues When a Database Is Moved Between SQL =
Servers
http://www.support.microsoft.com/?id=3D240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases =
When Database Is Moved
http://www.support.microsoft.com/?id=3D298897
--=20
Keith
"new2sql" <anonymous@.discussions.microsoft.com> wrote in message =
news:E22BCD34-B235-420E-848E-9C2A63576020@.microsoft.com...
> What is the best document for restoring databases from one SQL 2000 =
server to a new SQL 2000 server?

Thursday, February 16, 2012

Backup/restore

We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...That's not the way to do it. First, use DBCC SHRINKFILE on the larger file.
Next use ALTER DATABASE to increase the size of the same file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
We have a large database (30 gigs) that we want to break
up into multiple files. I tried backing it up, and
restoring it to a database created with multiple data
files, but just dumps 30 gigs into the first data file,
and leaves the second one blank.
Any suggestions on breaking up a large database?
Fred...|||The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||OK, let's say that You have 2 files - File1 and File2 - both in the same
filegroup. File1 has all of your data and is 10 GB. (Assume File1 is
full.) File2 is also 10 GB and has no data. Run DBCC SHRINKFILE (File1,
5210). What should happen is 5GB moves from File1 to File2, since they are
both in the same filegroup. Now, FIle1 is 5GB and full, while File2 is 10GB
and half-empty. At this point, you can shrink File2 and have two 5GB files
or you can expand File1 to, say, 10 GB and have 2 equally empty files.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<fkutz@.surebridge.com> wrote in message
news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
The database is already shrunk.
Why would I increase the single large datafile to make it
larger? I need to break this single large datafile into
multiple parts, so that I can put it on multiple disks.
Fred...
>--Original Message--
>That's not the way to do it. First, use DBCC SHRINKFILE
on the larger file.
>Next use ALTER DATABASE to increase the size of the same
file.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>We have a large database (30 gigs) that we want to break
>up into multiple files. I tried backing it up, and
>restoring it to a database created with multiple data
>files, but just dumps 30 gigs into the first data file,
>and leaves the second one blank.
>Any suggestions on breaking up a large database?
>Fred...
>.
>|||Cool!
Thank you *very* much. That makes much more sense.
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||I'll check into that. Could you please confirm that neither of these files
is the primary file and that the filegroup is not the primary filegroup? If
we're not talking about the primary file/filegroup, then I have an
alternative.
Let's say that you have a filegroup that has the one 30GB file - File1.
Now, add two more files to the filegroup - File1 and File2 - each 15GB in
size. Here's the clincher. Now run:
DBCC SHRINKFILE (File1, EMPTYFILE)
This will force the move and then disallow any further addition of data to
File1. Now run:
ALTER DATABASE MyDB
REMOVE FILE File1
At this point, you can rename File3 to File1:
ALTER DATABASE MyDB
MODIFY FILE (NAME = File3, NEWNAME = File1)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
Hey Tom, I tried your suggestion, and it's not working.
Here is the command I'm using and the results:
DBCC SHRINKFILE (EPMROPRD_Data, 15360);
11 1 3925712 128 3925648 3925648
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
No data is moving, and the file is not shrinking. My
second file, in the same file group is set to 15360MB.
Am I missing something?
Fred...
>--Original Message--
>OK, let's say that You have 2 files - File1 and File2 -
both in the same
>filegroup. File1 has all of your data and is 10 GB.
(Assume File1 is
>full.) File2 is also 10 GB and has no data. Run DBCC
SHRINKFILE (File1,
>5210). What should happen is 5GB moves from File1 to
File2, since they are
>both in the same filegroup. Now, FIle1 is 5GB and full,
while File2 is 10GB
>and half-empty. At this point, you can shrink File2 and
have two 5GB files
>or you can expand File1 to, say, 10 GB and have 2
equally empty files.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
><fkutz@.surebridge.com> wrote in message
>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>The database is already shrunk.
>Why would I increase the single large datafile to make it
>larger? I need to break this single large datafile into
>multiple parts, so that I can put it on multiple disks.
>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC SHRINKFILE
>on the larger file.
>>Next use ALTER DATABASE to increase the size of the same
>file.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>.
>|||Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||It's a good practice to use the primary file and filegroup just for system
objects. What you're now left with is to move data from your primary
filegroup to your application filegroup. This means rebuilding indexes:
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\temp\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\temp\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\temp\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\temp\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
go
use Sales
go
create table t
(
id int constraint PK_t primary key
)
go
insert t
select id
from Northwind..sysobjects
go
create unique clustered index PK_t on t (id) with drop_existing on
SalesGroup1
go
sp_help t
go
use master
go
drop database Sales
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:578601c42d30$82c6e710$a301280a@.phx.gbl...
Unfortunately they are the primaries. The big one is the
primary file on the primary group. Till now, we've just
been running with a single large file. I had thought of
your alternative as well, but I wasn't sure if it could
be done with the primary file. Though the help files
seem to imply it could. Right now I'm just playing with
a test db, so I can do anything with no worries.
Fred...
>--Original Message--
>I'll check into that. Could you please confirm that
neither of these files
>is the primary file and that the filegroup is not the
primary filegroup? If
>we're not talking about the primary file/filegroup, then
I have an
>alternative.
>Let's say that you have a filegroup that has the one
30GB file - File1.
>Now, add two more files to the filegroup - File1 and
File2 - each 15GB in
>size. Here's the clincher. Now run:
>DBCC SHRINKFILE (File1, EMPTYFILE)
>This will force the move and then disallow any further
addition of data to
>File1. Now run:
>ALTER DATABASE MyDB
>REMOVE FILE File1
>At this point, you can rename File3 to File1:
>ALTER DATABASE MyDB
>MODIFY FILE (NAME = File3, NEWNAME = File1)
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>Hey Tom, I tried your suggestion, and it's not working.
>Here is the command I'm using and the results:
>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>11 1 3925712 128 3925648 3925648
>(1 row(s) affected)
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>No data is moving, and the file is not shrinking. My
>second file, in the same file group is set to 15360MB.
>Am I missing something?
>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and full,
>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2 and
>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>equally empty files.
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
it
>>larger? I need to break this single large datafile into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
same
>>file.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>.
>|||In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||When I built the original table t, it was in the default filegroup. Since I
did not explicitly change the default filegroup when I built the DB, it was
the primary filegroup. I implicitly created a clustered index by adding a
primary key to the CREATE TABLE script. If you ran sp_help right after that
table was built, you'd see that the table existed in the primary filegroup.
When I re-created the index, using the drop_existing option, I explicitly
created it on SalesGroup1 by adding "on SalesGroup1". This forces the
clustered index - and hence, the data - to be moved to the new filegroup.
As for doing this on an entire database, you can use EM to script out your
tables, including indexes. Throw away the table stuff and keep the index
scripts. Change all references from the primary filegroup to the new
filegroup. For primary keys, the following will generate your script:
create function CreateIndexScript
(
@.Table sysname
, @.FileGroup sysname
)
returns varchar (8000)
as
begin
declare
@.str varchar (8000)
, @.constraint sysname
, @.IsClustered char (1)
, @.Column sysname
select
@.constraint = CONSTRAINT_NAME
, @.IsClustered = case when indexproperty (object_id (@.Table),
CONSTRAINT_NAME, 'IsClustered') = 1
then 'Y' else 'N' end
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
TABLE_NAME = @.Table
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
select
@.str = 'create unique ' + case when @.IsClustered = 'Y' then 'clustered '
else '' end
+ 'index ' + @.constraint + ' on [' + @.Table + '] ('
declare c cursor fast_forward for
select
COLUMN_NAME
from
information_schema.key_column_usage
where
TABLE_NAME = @.Table
and CONSTRAINT_NAME = @.constraint
order by
ORDINAL_POSITION
open c
fetch c into
@.Column
while @.@.fetch_status = 0
begin
set
@.str = @.str + '[' + @.Column + '], '
fetch c into
@.Column
end
close c
deallocate c
set
@.str = left (@.str, len (@.str) - 1) + ') with drop_existing on [' +
@.FileGroup + ']'
return (@.str)
end
go
select
dbo.CreateIndexScript (TABLE_NAME, 'MyFG')
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
go
drop function CreateIndexScript
You don't need to do anything for your views.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Fred" <fkutz@.surebridge.com> wrote in message
news:59bd01c42d3e$a4603fd0$a101280a@.phx.gbl...
In your script, I see nothing indicating a preference for
where your table is built, nor where the data will be
put. Only on the index build do you specify a file
group. How are you proposing I move data between file
groups? (keeping in mind, between tables and views I have
close to 40,000 objects in the database).
>--Original Message--
>It's a good practice to use the primary file and
filegroup just for system
>objects. What you're now left with is to move data from
your primary
>filegroup to your application filegroup. This means
rebuilding indexes:
>CREATE DATABASE Sales
>ON PRIMARY
>( NAME = SPri1_dat,
> FILENAME = 'c:\temp\SPri1dat.mdf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 15% ),
>FILEGROUP SalesGroup1
>( NAME = SGrp1Fi1_dat,
> FILENAME = 'c:\temp\SG1Fi1dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 ),
>( NAME = SGrp1Fi2_dat,
> FILENAME = 'c:\temp\SG1Fi2dt.ndf',
> SIZE = 10,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
>LOG ON
>( NAME = 'Sales_log',
> FILENAME = 'c:\temp\salelog.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
>go
>use Sales
>go
>create table t
>(
> id int constraint PK_t primary key
>)
>go
>insert t
>select id
>from Northwind..sysobjects
>go
>create unique clustered index PK_t on t (id) with
drop_existing on
>SalesGroup1
>go
>sp_help t
>go
>use master
>go
>drop database Sales
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Fred" <fkutz@.surebridge.com> wrote in message
>news:578601c42d30$82c6e710$a301280a@.phx.gbl...
>Unfortunately they are the primaries. The big one is the
>primary file on the primary group. Till now, we've just
>been running with a single large file. I had thought of
>your alternative as well, but I wasn't sure if it could
>be done with the primary file. Though the help files
>seem to imply it could. Right now I'm just playing with
>a test db, so I can do anything with no worries.
>Fred...
>
>>--Original Message--
>>I'll check into that. Could you please confirm that
>neither of these files
>>is the primary file and that the filegroup is not the
>primary filegroup? If
>>we're not talking about the primary file/filegroup, then
>I have an
>>alternative.
>>Let's say that you have a filegroup that has the one
>30GB file - File1.
>>Now, add two more files to the filegroup - File1 and
>File2 - each 15GB in
>>size. Here's the clincher. Now run:
>>DBCC SHRINKFILE (File1, EMPTYFILE)
>>This will force the move and then disallow any further
>addition of data to
>>File1. Now run:
>>ALTER DATABASE MyDB
>>REMOVE FILE File1
>>At this point, you can rename File3 to File1:
>>ALTER DATABASE MyDB
>>MODIFY FILE (NAME = File3, NEWNAME = File1)
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:57cf01c42d28$007dc240$a001280a@.phx.gbl...
>>Hey Tom, I tried your suggestion, and it's not working.
>>Here is the command I'm using and the results:
>>DBCC SHRINKFILE (EPMROPRD_Data, 15360);
>>11 1 3925712 128 3925648
3925648
>>(1 row(s) affected)
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>No data is moving, and the file is not shrinking. My
>>second file, in the same file group is set to 15360MB.
>>Am I missing something?
>>Fred...
>>--Original Message--
>>OK, let's say that You have 2 files - File1 and File2 -
>>both in the same
>>filegroup. File1 has all of your data and is 10 GB.
>>(Assume File1 is
>>full.) File2 is also 10 GB and has no data. Run DBCC
>>SHRINKFILE (File1,
>>5210). What should happen is 5GB moves from File1 to
>>File2, since they are
>>both in the same filegroup. Now, FIle1 is 5GB and
full,
>>while File2 is 10GB
>>and half-empty. At this point, you can shrink File2
and
>>have two 5GB files
>>or you can expand File1 to, say, 10 GB and have 2
>>equally empty files.
>>--
>>Tom
>>----
-
>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>><fkutz@.surebridge.com> wrote in message
>>news:523e01c42c91$94f65c30$a101280a@.phx.gbl...
>>The database is already shrunk.
>>Why would I increase the single large datafile to make
>it
>>larger? I need to break this single large datafile
into
>>multiple parts, so that I can put it on multiple disks.
>>Fred...
>>--Original Message--
>>That's not the way to do it. First, use DBCC
>SHRINKFILE
>>on the larger file.
>>Next use ALTER DATABASE to increase the size of the
>same
>>file.
>>--
>>Tom
>>---
-
>-
>>-
>>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Fred" <fkutz@.surebridge.com> wrote in message
>>news:4f7201c42c8f$28216840$a601280a@.phx.gbl...
>>We have a large database (30 gigs) that we want to
>break
>>up into multiple files. I tried backing it up, and
>>restoring it to a database created with multiple data
>>files, but just dumps 30 gigs into the first data
file,
>>and leaves the second one blank.
>>Any suggestions on breaking up a large database?
>>Fred...
>>.
>>
>>.
>>
>>.
>.
>|||take a look at SQL LiteSpeed for backup and recovery. Thats what I use. Take a look at www.imceda.com

Backup/Restore

What is the best document for restoring databases from one SQL 2000 server to a new SQL 2000 server?What do you mean by document?
Refer to Books Online (within the SQL Server program group). It will =tell you how to use the Transact-SQL commands BACKUP and RESTORE.
You might also find these KB articles helpful:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=3D307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=3D224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=3D314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=3D221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=3D168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is =Incomplete
http://www.support.microsoft.com/?id=3D274188
How to Resolve Permission Issues When a Database Is Moved Between SQL =Servers
http://www.support.microsoft.com/?id=3D240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases =When Database Is Moved
http://www.support.microsoft.com/?id=3D298897
-- Keith
"new2sql" <anonymous@.discussions.microsoft.com> wrote in message =news:E22BCD34-B235-420E-848E-9C2A63576020@.microsoft.com...
> What is the best document for restoring databases from one SQL 2000 =server to a new SQL 2000 server?

Backup/Restore

What is the best document for restoring databases from one SQL 2000 server t
o a new SQL 2000 server?What do you mean by document?
Refer to Books Online (within the SQL Server program group). It will =
tell you how to use the Transact-SQL commands BACKUP and RESTORE. =20
You might also find these KB articles helpful:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=3D307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=3D224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=3D314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=3D221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=3D168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=3D246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is =
Incomplete
http://www.support.microsoft.com/?id=3D274188
How to Resolve Permission Issues When a Database Is Moved Between SQL =
Servers
http://www.support.microsoft.com/?id=3D240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases =
When Database Is Moved
http://www.support.microsoft.com/?id=3D298897
--=20
Keith
"new2sql" <anonymous@.discussions.microsoft.com> wrote in message =
news:E22BCD34-B235-420E-848E-9C2A63576020@.microsoft.com...
> What is the best document for restoring databases from one SQL 2000 =
server to a new SQL 2000 server?

Monday, February 13, 2012

backup win98 files and restoring to winxp?

I have some bookkeeping files that were saved using win98 backup that i need to restore to my accounting program on my winxp computer.. i have no idea how to do that. The files that are backed up belong to the same program on both the win98 and winxp.

What is the format of the backed up files? Are they SQL Server files?

Thanks,
Sam Lester (MSFT)

Sunday, February 12, 2012

Backup Transaction Log to 2 different location

hi,
is there a possibility do backup the transaction log to 2 different
locations, and to handle them separatly when restoring a database?
The thing i want to do is, to be able to restore the database on 2
different location without doing logshipping.
thx for your help.
Hi
No. After the backup, do a XCOPY to the alternative location.
Regards
Mike
"Donald" wrote:

> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.
>
|||When I've done this I've always implemented it as an external app (VB
based). Not too hard to do.
"Donald" <martin.ha@.gmx.net> wrote in message
news:enCcG$QDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.

Backup Transaction Log to 2 different location

hi,
is there a possibility do backup the transaction log to 2 different
locations, and to handle them separatly when restoring a database?
The thing i want to do is, to be able to restore the database on 2
different location without doing logshipping.
thx for your help.Hi
No. After the backup, do a XCOPY to the alternative location.
Regards
Mike
"Donald" wrote:

> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.
>|||When I've done this I've always implemented it as an external app (VB
based). Not too hard to do.
"Donald" <martin.ha@.gmx.net> wrote in message
news:enCcG$QDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.

Backup Transaction Log to 2 different location

hi,
is there a possibility do backup the transaction log to 2 different
locations, and to handle them separatly when restoring a database?
The thing i want to do is, to be able to restore the database on 2
different location without doing logshipping.
thx for your help.Hi
No. After the backup, do a XCOPY to the alternative location.
Regards
Mike
"Donald" wrote:
> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.
>|||When I've done this I've always implemented it as an external app (VB
based). Not too hard to do.
"Donald" <martin.ha@.gmx.net> wrote in message
news:enCcG$QDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> hi,
> is there a possibility do backup the transaction log to 2 different
> locations, and to handle them separatly when restoring a database?
> The thing i want to do is, to be able to restore the database on 2
> different location without doing logshipping.
> thx for your help.

Sunday, March 11, 2012

Backward compatibility of Backups from 2005 to 2000

Backward compatibility of Backups from 2005 to 2000

Backward compatibility of Backups from 2005 to 2000

Wednesday, March 7, 2012

Backups and LSNs

Backups and LSNs

Friday, February 24, 2012

BackupIoRequest::WaitForIoCompletion failure when restoring database

Backup/Restore: Restoring to new server without rerunning a snapshot

Backup/Restore: Restoring to new server without rerunning a snapshot

Backup/Restore SQL 6.5 databases

Sunday, February 19, 2012

Backup/Restore

Thursday, February 16, 2012

Backup/restore

Backup/Restore

Backup/Restore

Monday, February 13, 2012

backup win98 files and restoring to winxp?

Sunday, February 12, 2012

Backup Transaction Log to 2 different location

Backup Transaction Log to 2 different location

Backup Transaction Log to 2 different location

Backup to Network Drive

Blog Archive

About Me