Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.
> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.
|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>
|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegr oups.com...
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way t
o
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for ea
ch file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind o
f
> a pain to go through all those steps. I was wondering if anyone knew a way
to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (
existing) destination
database. Won't help, since SQL Server need to create each file with same si
ze as the originating
database has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This won't help. I assume you mean shrink the transaction log file for the
(existing) destination
> database. Won't help, since SQL Server need to create each file with same
size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

Restore without big transaction log

All,
Our production database is growing quite rapidly and I'm having trouble
refreshing our QA and Dev environments due to limited disk space on those
servers. My process now is
- Restore the production DB with a new name
- Set to simple and shrink the log file to 0
- Back up the shrunk db to a new .bak file
- Restore to dev or QA from that .bak file
Our production DB is way to busy to shrink the log file on and it's kind of
a pain to go through all those steps. I was wondering if anyone knew a way to
restore from a backup with an empty .trn file? I don't see that this is
possible, but thought I'd ask.
Thanks in advance.> I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
Your assumption is correct. A restored database need as big file size for each file as the one you
were restoring from.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:3357D5E4-5001-42C7-8ED5-E854B7E90711@.microsoft.com...
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.|||On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
wrote:
> All,
> Our production database is growing quite rapidly and I'm having trouble
> refreshing our QA and Dev environments due to limited disk space on those
> servers. My process now is
> - Restore the production DB with a new name
> - Set to simple and shrink the log file to 0
> - Back up the shrunk db to a new .bak file
> - Restore to dev or QA from that .bak file
> Our production DB is way to busy to shrink the log file on and it's kind of
> a pain to go through all those steps. I was wondering if anyone knew a way to
> restore from a backup with an empty .trn file? I don't see that this is
> possible, but thought I'd ask.
> Thanks in advance.
One solution might be once you backup your production database (full
backup) to truncate your transaction log and shrink it, then perform
the restore after that. I would think that would clear up quite a few
space issues.|||> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that.
This won't help. I assume you mean shrink the transaction log file for the (existing) destination
database. Won't help, since SQL Server need to create each file with same size as the originating
database has.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
> On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> wrote:
>> All,
>> Our production database is growing quite rapidly and I'm having trouble
>> refreshing our QA and Dev environments due to limited disk space on those
>> servers. My process now is
>> - Restore the production DB with a new name
>> - Set to simple and shrink the log file to 0
>> - Back up the shrunk db to a new .bak file
>> - Restore to dev or QA from that .bak file
>> Our production DB is way to busy to shrink the log file on and it's kind of
>> a pain to go through all those steps. I was wondering if anyone knew a way to
>> restore from a backup with an empty .trn file? I don't see that this is
>> possible, but thought I'd ask.
>> Thanks in advance.
> One solution might be once you backup your production database (full
> backup) to truncate your transaction log and shrink it, then perform
> the restore after that. I would think that would clear up quite a few
> space issues.
>|||On Jul 12, 2:58 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that.
> This won't help. I assume you mean shrink the transaction log file for the (existing) destination
> database. Won't help, since SQL Server need to create each file with same size as the originating
> database has.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "acorcoran" <acorco...@.gmail.com> wrote in message
> news:1184209592.791721.108060@.k79g2000hse.googlegroups.com...
>
> > On Jul 10, 2:20 pm, sqlboy2000 <sqlboy2...@.discussions.microsoft.com>
> > wrote:
> >> All,
> >> Our production database is growing quite rapidly and I'm having trouble
> >> refreshing our QA and Dev environments due to limited disk space on those
> >> servers. My process now is
> >> - Restore the production DB with a new name
> >> - Set to simple and shrink the log file to 0
> >> - Back up the shrunk db to a new .bak file
> >> - Restore to dev or QA from that .bak file
> >> Our production DB is way to busy to shrink the log file on and it's kind of
> >> a pain to go through all those steps. I was wondering if anyone knew a way to
> >> restore from a backup with an empty .trn file? I don't see that this is
> >> possible, but thought I'd ask.
> >> Thanks in advance.
> > One solution might be once you backup your production database (full
> > backup) to truncate your transaction log and shrink it, then perform
> > the restore after that. I would think that would clear up quite a few
> > space issues.- Hide quoted text -
> - Show quoted text -
This is true, however, I guess I was thinking of backing up the
production database, truncting the log file, then re-backing up the
database (just for day 1). When you restore, you will be restoring a
very limited size log file. Of course, I guess this simply depends on
how large your log file grows between your full backups. As all
future backups would consist of a full backup then a truncation,
maintaining the size on your source database.
Just a though.

restore the replication system

Database A and B are replicated to each other. Both db reside on the same box.
The box has been rebuild due to a system crash. All data base files have
been preserved.
NT SA restored all user databases by attaching the datafiles except
master and msdb. Now, the replication system is out of sync.
Can I restore the master and msdb using attach db to restore the
entire replication system?
Any other better or correct way to restore the entire system when there is a
system crash?
Appreciate any help.
Wen
Wen,
provided you also restore the distribution database this may be possible. It
partly depends on 2 factors:
firstly, is the server name the same as the original one? If not then the
recovery strategy won't work and you'll need to start again - recreate the
publications and initialize.
If the servername is identical, then it may be possible - have a look in BOL
for "replication, backup and restore operations".
The second question is what type of replication are you using? In most cases
it is ok but I'd mention that if the distribution database's backup has been
allowed to get ahead of the publisher's backup and you are using
transactional replication there are likely to be problems.
Anyway, the first port of call is the BOL reference above, and please post
back if you have any issues which arise from that.
Regards,
Paul Ibison
|||Paul.
1. Yes, the servername is the same as the original one. and the
Distribution db
has been restored.
2. We have transactional and merge replication.
I will attache back the master and msdb and see if it works.
Thanks for the help.
Wen
"Paul Ibison" wrote:

> Wen,
> provided you also restore the distribution database this may be possible. It
> partly depends on 2 factors:
> firstly, is the server name the same as the original one? If not then the
> recovery strategy won't work and you'll need to start again - recreate the
> publications and initialize.
> If the servername is identical, then it may be possible - have a look in BOL
> for "replication, backup and restore operations".
> The second question is what type of replication are you using? In most cases
> it is ok but I'd mention that if the distribution database's backup has been
> allowed to get ahead of the publisher's backup and you are using
> transactional replication there are likely to be problems.
> Anyway, the first port of call is the BOL reference above, and please post
> back if you have any issues which arise from that.
> Regards,
> Paul Ibison
>
>
|||Wen,
there should be no problem with the merge database restoration, and you
might want to synchronize with the most upto date subscriber after the
restore.
As for the transactional, there could be issues. Did the publisher have the
sync with backup option set to true? If not, the distribution backup could
be ahead of the publisher restore. After the restore you will get an error
from the Log Reader Agent because it will detect that the Distributor is
ahead of the Publisher. The recommendation is to run sp_replrestart in the
publication database with no parameters. and ensure that that the
distribution agent, which could now deliver duplicate rows to Subscribers,
can continue despite these failures. Choose the -SkipError Distribution
Agent profile, or you can manually add the -SkipError parameter to the
runtime parameters of the Distribution Agents and supply the errors you want
the Distribution Agents to ignore.
You'll need to use linked servers to fix the inconsistencies, or you could
of course resort to reinitializing.
HTH,
Paul Ibison
|||Paul, the sync with backup option is fairly useless unless you are shipping
the transaction logs to a standby server.
The sequence to get this to work is
1) use the sync with backup option on your publication database and possibly
your distribution database
2) restore the msdb, publication database, and distribution database backups
on the standby server with the keep_replication switch.
3) then ship the publication and distribution databases tlogs and without
the keep_replication switch.
4) when the publisher goes offline you rename the standby server with the
name of the original publisher, and then do a sp_replrestart,
and -skiperrors switch.
In the case of this user you can't really use any of these options as they
have not been shipping the database. With a new master database, the user
probably does not have any of the subscribers listed as remote servers and
restoring or attaching any databases will be highly problematic.
The user is best to drop all replication and create publications and
subscriptions from scratch as their data is likely out of sync.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uY4Vt9RfEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Wen,
> there should be no problem with the merge database restoration, and you
> might want to synchronize with the most upto date subscriber after the
> restore.
> As for the transactional, there could be issues. Did the publisher have
the
> sync with backup option set to true? If not, the distribution backup could
> be ahead of the publisher restore. After the restore you will get an error
> from the Log Reader Agent because it will detect that the Distributor is
> ahead of the Publisher. The recommendation is to run sp_replrestart in the
> publication database with no parameters. and ensure that that the
> distribution agent, which could now deliver duplicate rows to Subscribers,
> can continue despite these failures. Choose the -SkipError Distribution
> Agent profile, or you can manually add the -SkipError parameter to the
> runtime parameters of the Distribution Agents and supply the errors you
want
> the Distribution Agents to ignore.
> You'll need to use linked servers to fix the inconsistencies, or you could
> of course resort to reinitializing.
> HTH,
> Paul Ibison
>
|||Hilary,
from a practical point of view I accept your point - log-shipping, the 'sync
with backup' option and keep_replication go well together to minimize the
latency involved if you ship every few minutes.
However in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
implementation:
- they advise 'keep_replication' for the last log.
- and sp_replrestart for semi-synchronous mode only - ie when the 'sync with
backup' option is false.
Are these just alternative methods?
Regards,
Paul Ibison
|||BOL lies.
Try to restore a tlog with the keep_replication switch.
I have reported this doc bug to Microsoft.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23PFoxqifEHA.3520@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> from a practical point of view I accept your point - log-shipping, the
'sync
> with backup' option and keep_replication go well together to minimize the
> latency involved if you ship every few minutes.
> However in BOL
>
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\re
> plsql.chm::/replbackup_3js7.htm) the recommendation is different to your
> implementation:
> - they advise 'keep_replication' for the last log.
> - and sp_replrestart for semi-synchronous mode only - ie when the 'sync
with
> backup' option is false.
> Are these just alternative methods?
> Regards,
> Paul Ibison
>
|||Hilary,
thanks for pointing this out, and for posting up a bonafide working
sequence.
As far as I remember, when I tested this I followed a different methodology
where I used "WITH KEEP_REPLICATION, RECOVERY" on the last transaction log
and this seemed to work. Unfortunately I didn't make a proper note of it and
I really need 3 boxes to repeat the test. Later this week it might be
possible to set it up and I'll post back then.
Regards,
Paul Ibison

Restore MSSQL7 Backup data to MSSQL2000

Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this? Thanks
Rdgs
Ellis
Hi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this? Thanks
> Rdgs
> Ellis
>
|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to[vbcol=seagreen]
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do
>

Restore MSSQL7 Backup data to MSSQL2000

Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> > Dear all,
> > I want to transfer data from SQL7 to SQL2000, so I backup the data
and
> > restore it in SQL2000. But it fails to do so due to different version.
Do
> > everyone know how to do this' Thanks
> >
> > Rdgs
> > Ellis
> >
> >
>

Restore MSSQL7 Backup data to MSSQL2000

Dear all,
I want to transfer data from SQL7 to SQL2000, so I backup the data and
restore it in SQL2000. But it fails to do so due to different version. Do
everyone know how to do this' Thanks
Rdgs
EllisHi,
Did you tried to restore a SYSTEM database ? System database can not be
restored from SQL 7 to SQL 2000. How ever all the
user databases can be backed up from SQL 7 and can be restored to SQL 2000
with out any issues. During the restore itself it
does a version conversion. But execute the procedure "SP_UPDATESTATS" after
the restoration to get the optimal performance.
Note:
FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option to
copy the data is use DTS.
Thanks
Hari
MCDBA
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> I want to transfer data from SQL7 to SQL2000, so I backup the data and
> restore it in SQL2000. But it fails to do so due to different version. Do
> everyone know how to do this' Thanks
> Rdgs
> Ellis
>|||Thanks a lot !!! It's very useful
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#JxmhPmhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi,
> Did you tried to restore a SYSTEM database ? System database can not be
> restored from SQL 7 to SQL 2000. How ever all the
> user databases can be backed up from SQL 7 and can be restored to SQL 2000
> with out any issues. During the restore itself it
> does a version conversion. But execute the procedure "SP_UPDATESTATS"
after
> the restoration to get the optimal performance.
>
> Note:
> FYI, You can not restor a SQL 2000 database backup to SQL 7. Only option
to
> copy the data is use DTS.
> Thanks
> Hari
> MCDBA
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:eLIJgImhEHA.3140@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
Do[vbcol=seagreen]
>

restore master without sqlserver running?

due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?This is a multi-part message in MIME format.
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You can use the rebuildm.exe utility to rebuild master and then restore your
backup once that is done.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FB39E43.BBB2A260@.dontemailme.com...
due to a snafu on the reconfiguring of a server, a raid with the
database files was reformatted. unfortunately, our backup software does
not backup the db files since they are in use at the time of backup.
now, sqlserver won't start because there is no master.mdf file. i do
have a backup of the master db. is there any way i can restore that
without sqlserver running? or will i have to reinstall sqlserver and
then restore master along with all the other db's?
--=_NextPart_000_0135_01C3A9CE.AE173720
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You can use the rebuildm.exe utility =to rebuild master and then restore your backup once that is done.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"chxxx" =wrote in message news:3FB39E43.BBB2A260@.do=ntemailme.com...due to a snafu on the reconfiguring of a server, a raid with thedatabase =files was reformatted. unfortunately, our backup software doesnot =backup the db files since they are in use at the time of backup.now, sqlserver =won't start because there is no master.mdf file. i dohave a backup =of the master db. is there any way i can restore thatwithout =sqlserver running? or will i have to reinstall sqlserver andthen restore =master along with all the other db's?

---

--=_NextPart_000_0135_01C3A9CE.AE173720--|||Ch
Look in books on line for how to restore your master
database. If you have a copy of the master.mdf, you may be
able to copy it to the location it should be in (d:mssql
or similar) and start sql server without needing to do
anything else.
If you can't restore master either way. If you still have
your user database you can do a fresh install and then
attach your databases. You will need to add your user
logins to the server before you can use it.
If you can not recover the msdb database you will also
need to recreate any jobs you had.
Hope this helps
John|||chxxx wrote:
> due to a snafu on the reconfiguring of a server, a raid with the
> database files was reformatted. unfortunately, our backup software
> does not backup the db files since they are in use at the time of
> backup. now, sqlserver won't start because there is no master.mdf
> file. i do have a backup of the master db. is there any way i can
> restore that without sqlserver running? or will i have to reinstall
> sqlserver and then restore master along with all the other db's?
when you say you have a backup of the master file is it a sql backup or
a standard file backup. If latter then you should be able to restore
that to its location and restart the server. If you are saying you have
a sql backup then you will need sql started in order to restore. You
can rebuild the master (rebuildm) and once sql is started you can
restore back from your sql backup to return to the original one.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============|||Mark Broadbent wrote:
> chxxx wrote:
> > due to a snafu on the reconfiguring of a server, a raid with the
> > database files was reformatted. unfortunately, our backup software
> > does not backup the db files since they are in use at the time of
> > backup. now, sqlserver won't start because there is no master.mdf
> > file. i do have a backup of the master db. is there any way i can
> > restore that without sqlserver running? or will i have to reinstall
> > sqlserver and then restore master along with all the other db's?
> when you say you have a backup of the master file is it a sql backup or
> a standard file backup. If latter then you should be able to restore
> that to its location and restart the server. If you are saying you have
> a sql backup then you will need sql started in order to restore. You
> can rebuild the master (rebuildm) and once sql is started you can
> restore back from your sql backup to return to the original one.
the only backups i have are the sql backup files (.bak). the .mdf and .ldf
files are gone due to backup software not being able to back those up.
after reading bol, looks like i'll be able to use rebuildm to get sqlserver
running again.
then i'll be able to restore master and all other db's from the .bak files
i have on disk.|||chxxx wrote:
>
> Mark Broadbent wrote:
> > chxxx wrote:
> >
> > > due to a snafu on the reconfiguring of a server, a raid with the
> > > database files was reformatted. unfortunately, our backup
> > > software does not backup the db files since they are in use at
> > > the time of backup. now, sqlserver won't start because there is
> > > no master.mdf file. i do have a backup of the master db. is
> > > there any way i can restore that without sqlserver running? or
> > > will i have to reinstall sqlserver and then restore master along
> > > with all the other db's?
> >
> > when you say you have a backup of the master file is it a sql
> > backup or a standard file backup. If latter then you should be able
> > to restore that to its location and restart the server. If you are
> > saying you have a sql backup then you will need sql started in
> > order to restore. You can rebuild the master (rebuildm) and once
> > sql is started you can restore back from your sql backup to return
> > to the original one.
> the only backups i have are the sql backup files (.bak). the .mdf
> and .ldf files are gone due to backup software not being able to back
> those up.
> after reading bol, looks like i'll be able to use rebuildm to get
> sqlserver running again.
> then i'll be able to restore master and all other db's from the .bak
> files i have on disk.
Correct :?)
--
Br,
Mark Broadbent
mcdba , mcse+i
=============