Restore?

I am trying to restore a .bak to a test server in order to test the integrity
of my production DB backups but the test server rejects the .bak file because
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.
What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>
|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
.....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>
|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integrity
> of my production DB backups but the test server rejects the .bak file because
> it is a file used by the production server. A full and transactional backups
> are used in the production DB. Any insights on how to perform a test restore
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||It is a totally different server. Maybe I am doing something wrong. How would
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:

> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>
|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:

> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegro ups.com...
>
>
|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the test
> server and execute the backup statement. By log.bak did you mean .trn files?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Restore?

I am trying to restore a .bak to a test server in order to test the integrit
y
of my production DB backups but the test server rejects the .bak file becaus
e
it is a file used by the production server. A full and transactional backups
are used in the production DB. Any insights on how to perform a test restore
is highly appreciated.What error message are you getting ?
Razvan
morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.|||Please post the exact error message.
Is your test machine a separate instance on the same hardware, or a
completely different server?
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>I am trying to restore a .bak to a test server in order to test the
>integrity
> of my production DB backups but the test server rejects the .bak file
> because
> it is a file used by the production server. A full and transactional
> backups
> are used in the production DB. Any insights on how to perform a test
> restore
> is highly appreciated.
>|||Razvan
I think the OP is asking how to restore .bak file and are not getting any
errors :-)
morphius
RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
....
RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
> What error message are you getting ?
> Razvan
> morphius wrote:
>|||morphius wrote:
> I am trying to restore a .bak to a test server in order to test the integr
ity
> of my production DB backups but the test server rejects the .bak file beca
use
> it is a file used by the production server. A full and transactional backu
ps
> are used in the production DB. Any insights on how to perform a test resto
re
> is highly appreciated.
>
My guess is you're restoring over an existing database, and the message
you're seeing is telling you that there are existing files, in which
case you need to use the WITH MOVE option on the RESTORE command.
Posting the full error message will tell us for sure.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It is a totally different server. Maybe I am doing something wrong. How woul
d
you specifically restore a copy of the production db to the test server?
Thanks...
"Kevin3NF" wrote:

> Please post the exact error message.
> Is your test machine a separate instance on the same hardware, or a
> completely different server?
> --
> Kevin Hill
> 3NF Consulting
> http://www.3nf-inc.com/NewsGroups.htm
> Real-world stuff I run across with SQL Server:
> http://kevin3nf.blogspot.com
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:0AA8DE03-924A-41D8-A706-87E9F047B945@.microsoft.com...
>
>|||Hello,
1. Copy the Full database backup file to test server
2. Copy the Transaction log backup files to test servr
3. Open Query Analyzer (SQL2000) or SSMS (SQL 2005) in test server
4. Use RESTORE DATABASE Command with NORECOVERY and MOVE option to restore
the Full database
5. Resttore all the transaction log backups using RESTORE LOG WITH
NORECOVERY until last file
6. Restore the Last Log backup using RESTORE LOG WITH Recovery option. THsi
will make the database online
See the commands usage in books online...
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:63F7B1D4-F3F2-43EB-81DD-065B7D6FF35B@.microsoft.com...[vbcol=seagreen]
> It is a totally different server. Maybe I am doing something wrong. How
> would
> you specifically restore a copy of the production db to the test server?
> Thanks...
> "Kevin3NF" wrote:
>|||So, basically i need to copy the data.bak files to the local HD of the test
server and execute the backup statement. By log.bak did you mean .trn files?
"Uri Dimant" wrote:

> Razvan
> I think the OP is asking how to restore .bak file and are not getting any
> errors :-)
> morphius
> RESTORE DATABASE dbname FROM DISK='C:\db.bak' WITH FILE = 1,NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 1, NORECOVERY
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = 2, NORECOVERY
> .....
> RESTORE LOG dbname FROM DISK = 'C:\Log.bak' WITH FILE = n, RECOVERY
>
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1165930086.612341.260330@.16g2000cwy.googlegroups.com...
>
>|||Yes, take a look at WITH MOVE option in the BOL as well
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:7C00AC18-E4EB-48EB-8134-8544FDE6FCD1@.microsoft.com...[vbcol=seagreen]
> So, basically i need to copy the data.bak files to the local HD of the
> test
> server and execute the backup statement. By log.bak did you mean .trn
> files?
> "Uri Dimant" wrote:
>|||morphius wrote:
> So, basically i need to copy the data.bak files to the local HD of the tes
t
> server and execute the backup statement. By log.bak did you mean .trn file
s?
>
Technically, NO, you don't HAVE to copy the BAK files to the local HD.
You can restore from a remote share:
RESTORE DATABASE foo FROM DISK = '\\server\sharename'
However, the SQL Server service account must have permission to read
from this network share. This is NOT, repeat, NOT the account that YOU
login to SQL with, this is the account that the SERVICE runs under.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

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 with Percent free space

Usually you have huge databases in production with atleast 50-60% free space
....buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense ?
Thanks,
Ranga
Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:

> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>

Restore with Percent free space

Usually you have huge databases in production with atleast 50-60% free space
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db
?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramification
s on the restore
process.
Consider a database file in which you have pages. Any page can be in use, an
d any page in there can
have the address for this page in the page header (for instance). The restor
e process cannot
"compact" these pages to move them towards the beginning of the file, becaus
e of the linkage etc
between pages. Sure, it might be physically possible, but that restore would
be much much slower.
And it might not even be possible at all, since recovery is part of the rest
ore process and physical
page addresses might be in the transaction log records (might be, I'm not 10
0% certain of this).
Perhaps is it possible to have two types of restore: what we have today and
a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free spa
ce
> ...buth when you want to restore the db in dev...you need to have simila
r
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention h
ow
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, S
o,
> all I need is 45GB instead of 100 GB in my dev environment to restore the
db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLSe...=1252
87
"Tibor Karaszi" wrote:

> The request makes sense, but the implementation would have huge ramificati
ons on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use,
and any page in there can
> have the address for this page in the page header (for instance). The rest
ore process cannot
> "compact" these pages to move them towards the beginning of the file, beca
use of the linkage etc
> between pages. Sure, it might be physically possible, but that restore wou
ld be much much slower.
> And it might not even be possible at all, since recovery is part of the re
store process and physical
> page addresses might be in the transaction log records (might be, I'm not
100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today an
d a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/s...aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>|||I almost figured that MS wouldn't be too keen on re-writing large portions o
f the restore code.
Thanks for the update, Ranga.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...[vbcol=seagreen]
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLSe...=12
5287
> "Tibor Karaszi" wrote:
>

Restore with Percent free space

Usually you have huge databases in production with atleast 50-60% free space
...buth when you want to restore the db in dev...you need to have similar
storage size as in prod. Would it be nice if we have a option like
"FreeSpacePercent" parameter in the restore command where we can mention how
much free space is good enough.
For Example, if a DB is 100 GB size, with only 40% of data and 60% free
space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
all I need is 45GB instead of 100 GB in my dev environment to restore the db?
Makes sense '
Thanks,
Ranga> Makes sense '
The request makes sense, but the implementation would have huge ramifications on the restore
process.
Consider a database file in which you have pages. Any page can be in use, and any page in there can
have the address for this page in the page header (for instance). The restore process cannot
"compact" these pages to move them towards the beginning of the file, because of the linkage etc
between pages. Sure, it might be physically possible, but that restore would be much much slower.
And it might not even be possible at all, since recovery is part of the restore process and physical
page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> Usually you have huge databases in production with atleast 50-60% free space
> ...buth when you want to restore the db in dev...you need to have similar
> storage size as in prod. Would it be nice if we have a option like
> "FreeSpacePercent" parameter in the restore command where we can mention how
> much free space is good enough.
> For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> Makes sense '
> Thanks,
> Ranga|||Thanks...There was a similar request...but declined by MS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
"Tibor Karaszi" wrote:
> > Makes sense '
> The request makes sense, but the implementation would have huge ramifications on the restore
> process.
> Consider a database file in which you have pages. Any page can be in use, and any page in there can
> have the address for this page in the page header (for instance). The restore process cannot
> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
> between pages. Sure, it might be physically possible, but that restore would be much much slower.
> And it might not even be possible at all, since recovery is part of the restore process and physical
> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
> Perhaps is it possible to have two types of restore: what we have today and a "compact-and-restore".
> You might want to vent such a request at http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
> > Usually you have huge databases in production with atleast 50-60% free space
> > ...buth when you want to restore the db in dev...you need to have similar
> > storage size as in prod. Would it be nice if we have a option like
> > "FreeSpacePercent" parameter in the restore command where we can mention how
> > much free space is good enough.
> >
> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
> >
> > Makes sense '
> >
> > Thanks,
> > Ranga
>|||I almost figured that MS wouldn't be too keen on re-writing large portions of the restore code.
Thanks for the update, Ranga.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:6010C190-C1DB-4980-8FF8-E6CC37C75913@.microsoft.com...
> Thanks...There was a similar request...but declined by MS
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125287
> "Tibor Karaszi" wrote:
>> > Makes sense '
>> The request makes sense, but the implementation would have huge ramifications on the restore
>> process.
>> Consider a database file in which you have pages. Any page can be in use, and any page in there
>> can
>> have the address for this page in the page header (for instance). The restore process cannot
>> "compact" these pages to move them towards the beginning of the file, because of the linkage etc
>> between pages. Sure, it might be physically possible, but that restore would be much much slower.
>> And it might not even be possible at all, since recovery is part of the restore process and
>> physical
>> page addresses might be in the transaction log records (might be, I'm not 100% certain of this).
>> Perhaps is it possible to have two types of restore: what we have today and a
>> "compact-and-restore".
>> You might want to vent such a request at
>> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:13E008DD-4049-441E-84F5-8A972D9198CE@.microsoft.com...
>> > Usually you have huge databases in production with atleast 50-60% free space
>> > ...buth when you want to restore the db in dev...you need to have similar
>> > storage size as in prod. Would it be nice if we have a option like
>> > "FreeSpacePercent" parameter in the restore command where we can mention how
>> > much free space is good enough.
>> >
>> > For Example, if a DB is 100 GB size, with only 40% of data and 60% free
>> > space. I should be able to restore the DB using "FreeSpacePercent" = 5%, So,
>> > all I need is 45GB instead of 100 GB in my dev environment to restore the db?
>> >
>> > Makes sense '
>> >
>> > Thanks,
>> > Ranga
>>

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?
You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?
|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>> My production database files total about 100GB, of which about 35GB is
>> used. My test machine has about 60GB available, so has plenty of room to
>> accommodate the entire database. When I try to restore a full backup
>> though, it screams at me that there is not enough disk space.
>> I created a new database with 40GB of data files, and that should be
>> enough to hold all the data. How can I keep the restore process from
>> trying to create exactly the same file configuration as the original
>> database?
>

Restore to different file size

My production database files total about 100GB, of which about 35GB is used.
My test machine has about 60GB available, so has plenty of room to
accommodate the entire database. When I try to restore a full backup though,
it screams at me that there is not enough disk space.
I created a new database with 40GB of data files, and that should be enough
to hold all the data. How can I keep the restore process from trying to
create exactly the same file configuration as the original database?You can't.
The SQL restore process requires the creation of exactly the same file sizes
on the target system as existed on the originating system. I suggest
purchasing an external HDD, restorign to that, shrinking, then moving the
data files.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
> My production database files total about 100GB, of which about 35GB is
> used. My test machine has about 60GB available, so has plenty of room to
> accommodate the entire database. When I try to restore a full backup
> though, it screams at me that there is not enough disk space.
> I created a new database with 40GB of data files, and that should be
> enough to hold all the data. How can I keep the restore process from
> trying to create exactly the same file configuration as the original
> database?|||I was afraid of that. Thanks Geoff.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OcDFQuH0HHA.1208@.TK2MSFTNGP03.phx.gbl...
> You can't.
> The SQL restore process requires the creation of exactly the same file
> sizes on the target system as existed on the originating system. I
> suggest purchasing an external HDD, restorign to that, shrinking, then
> moving the data files.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Daniel Rimmelzwaan (MVP - Dynamics NAV)" <daniel@.nadaspam.risplus.com>
> wrote in message news:Ow$oUoH0HHA.1168@.TK2MSFTNGP02.phx.gbl...
>

restore to another server of db with FT catalogs

If we restore a backup copy of a production db having FT catalogs onto a
laptop running SQL Server Developer version, the restored db will contain
references to FT catalogs whose underlying system files are not yet present
on the laptop (because backup doesn't grab the FT system files). The index
rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
move the FT system files. In that scenario, is it safe for the db on the
laptop to deactivate and drop the FT catalogs? Or can the missing system
files cause SQL Server to become unstable during the deactivation/drop
process? I'm thinking it shouldn't cause a problem, because if it did,
there'd be no way to drop a catalog whose system files had gotten corrupted.
But I lack the mental fortitude to experiment after the 7342 error consumed
most of my weekend, and am hoping you FTS gurus could handle this scenario
with your eyes closed and one arm tied behind your back. :-)
Thanks
Timo
consult this kb article for more information.
http://support.microsoft.com/default...b;en-us;240867
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
> present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
> corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
> consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||Timo,
Yes, it is best to re-create the small FT Catalogs via normal procedures on
your laptop. However, successfully restoring a FT-enabled SQL Server 2000
database and then being able to re-create the lost FT Catalog depends upon
several factors - are the disk drives (drive letter & path) exactly the same
on the source server as the destination server? Are you restoring over an
existing database or are you restoring the database as a new database on the
laptop? The first factor will cause you problems (that can be overcome), the
second factor should work successfully. Overall, for SQL Server 2000 when
backing up FT-enabled databases, its best to disenable Full Text and then
backup the database...
You can also use the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 to help you
as well understand these issues.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||FYI, SQL 2K5 backups (as with sp_attach/detach_db) will contain FT indexes

"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR
1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>
|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>
|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process
will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command
and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl.
.
>I have backed up our production database to a file and would like to restor
e it to a test database
>I created. This seems very simple but I seem to be missing something. I am
using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to resto
re
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
--
Paul G
Software engineer.
"TRD" wrote:
> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>> I have backed up our production database to a file and would like to
>> restore
>> it to a test database I created. This seems very simple but I seem to be
>> missing something. I am using query analyzer. Could someone please assist
>> me?
>>
>> TIA
>> TR
>>

Restore table from tape

I have a static table that I restored from tape to a new db name on same server as production db. I'm trying to restore one table that was accidently deleted from production db. However, when I run a "select 'tablename' into 'production db' from'backed up db' in Query Analyzer I get invalid object name. I also cannot browse (all rows) this table in Enterprise Manager but I CAN return all rows when I do a 'select * from 'tablemname' in query analyzer.
I'm stumped. Can someone help? I stopped and re-stared SQL Server with no success after I restored with no success.
Can anyone help?Nathank,
You pseudo-code syntax is incorrect. It should be "select 'column list' into 'production db..tablename' from'backed up db..tablename'. If this is not the issue, please supply the actual query being used.
Norman|||If the object name has spaces ,etc in it you must use sqaure braces ie
select * into [funny table] from otherdb.dbo.[funny table]
Regarding seeing the rows in SEM, if there are a LOT of rows, sometimes SEM
has a problem showing them (especially if memory is low)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nathank" <anonymous@.discussions.microsoft.com> wrote in message
news:E7BCB601-AABD-47D0-9FF2-C4F520A80278@.microsoft.com...
> I have a static table that I restored from tape to a new db name on same
server as production db. I'm trying to restore one table that was accidently
deleted from production db. However, when I run a "select 'tablename' into
'production db' from'backed up db' in Query Analyzer I get invalid object
name. I also cannot browse (all rows) this table in Enterprise Manager but I
CAN return all rows when I do a 'select * from 'tablemname' in query
analyzer.
> I'm stumped. Can someone help? I stopped and re-stared SQL Server with no
success after I restored with no success.
> Can anyone help?

Restore table from tape

I have a static table that I restored from tape to a new db name on same ser
ver as production db. I'm trying to restore one table that was accidently de
leted from production db. However, when I run a "select 'tablename' into 'pr
oduction db' from'backed up
db' in Query Analyzer I get invalid object name. I also cannot browse (all r
ows) this table in Enterprise Manager but I CAN return all rows when I do a
'select * from 'tablemname' in query analyzer.
I'm stumped. Can someone help? I stopped and re-stared SQL Server with no su
ccess after I restored with no success.
Can anyone help?Nathank,
You pseudo-code syntax is incorrect. It should be "select 'column list' into
'production db..tablename' from'backed up db..tablename'. If this is not th
e issue, please supply the actual query being used.
Norman|||If the object name has spaces ,etc in it you must use sqaure braces ie
select * into [funny table] from otherdb.dbo.[funny table]
Regarding seeing the rows in SEM, if there are a LOT of rows, sometimes SEM
has a problem showing them (especially if memory is low)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Nathank" <anonymous@.discussions.microsoft.com> wrote in message
news:E7BCB601-AABD-47D0-9FF2-C4F520A80278@.microsoft.com...
> I have a static table that I restored from tape to a new db name on same
server as production db. I'm trying to restore one table that was accidently
deleted from production db. However, when I run a "select 'tablename' into
'production db' from'backed up db' in Query Analyzer I get invalid object
name. I also cannot browse (all rows) this table in Enterprise Manager but I
CAN return all rows when I do a 'select * from 'tablemname' in query
analyzer.
> I'm stumped. Can someone help? I stopped and re-stared SQL Server with no
success after I restored with no success.
> Can anyone help?sql

RESTORE SQL Server to msde?

Is it possible?
How about all dev in sql server then migrate to "production msde" using either DTS or bcp and DDL scripts?
Can you bcp in to msde? And is msde true client server?This is a start!


http://groups.google.com/groups?q=msde+database+restore&hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.*&selm=2l7qo3F9s4e1U1%40uni-berlin.de&rnum=3|||MSDE is the desktop version of SQL Server. It is limited in terms of only 2 Gb per database, and only 5 simultaneous active spids per instance, but those are the only significant limitations.

I don't remember if BCP is included in the executables, although OSQL.EXE definitely is. The Transact-SQL BULK INSERT command works just fine, and is simply a different interface to the same API that BCP uses.

DTS works on an MSDE instance without any reservations that I know of, other than the limitations imposed by MSDE itself.

-PatP|||Cool...so if they want this application to be distributable...should be no problem...right?|||No problem I can see. Don't overlook the easy answer if you can just detach the database and zip it though!

-PatP

Restore Production database to Development

I have a production database with a backup job that creates files with the
naming convention dbname_db_200503291800.bak. I want to schedule a restore
job that will retire yesterdays backup. How can I write my restore statement
so that it will specify the backup file with yesterdays date.

ThanksTerri (terri@.cybernets.com) writes:
> I have a production database with a backup job that creates files with
> the naming convention dbname_db_200503291800.bak. I want to schedule a
> restore job that will retire yesterdays backup. How can I write my
> restore statement so that it will specify the backup file with
> yesterdays date.

DECLARE @.filename sysname
SELECT @.filename = 'dbname_db' +
convert(char(8), dateadd(DAY, -1, getdate()), 112) +
'.bak'
RESTORE DATABASE db FROM disk=@.filename

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

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

Restore production database into development database problem - Ur

I restored the production database backup(170GB) onto a development system.
I first tried to do the following command:
restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
Then stopped it after 30 minutes realized that I need to use the MOVE
command because the log and data file have different location on the
development system.
I then initiated the following:
restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
with move 'EEMSuite_model_Data' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
move 'EEMSuite_model_Log' to
'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
replace
It ran for two days and finally came back with the following After two days
of restore process:
Server: Msg 5173, Level 16, State 1, Line 1
Cannot associate files with different databases.
Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
does not match the primary file. It may be from a different database or the
log may have been rebuilt previously.
Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
file 1.
Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
1.
RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
(1.036 MB/sec).
I am concerned about the frist error message but was wondering if that came
from first failed attempt. By looking at the following two messages it looks
like the restore was a successful one?
My user can not access the database and the newly restored user database
does not show up in the Object Browser. What am missing? Do I need a
"recovery" command somewhere? Please help!!
--Ling
After the restore, I also can not see the Permissions under properties for
MYDB. And, I while I tried to open the properties for other database such as
master, it flashes and goes away. Is this a permission problem after
restore? Thanks in advance.
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling
|||Log in as a system administrator and try to access the database.. It is
common to have user/login mismatches when restoring a database to a
different server..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5176807C-4D79-4DD2-B074-C9D010E51FB0@.microsoft.com...
> After the restore, I also can not see the Permissions under properties for
> MYDB. And, I while I tried to open the properties for other database such
as[vbcol=seagreen]
> master, it flashes and goes away. Is this a permission problem after
> restore? Thanks in advance.
> Ling
> "Ling" wrote:
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]
|||I rebooted the server and now I can query the tables within the database.
But, while my user trying to connect, they can not! Please advise how to fix
this problem. Thank you!!
Ling
"Ling" wrote:

> I restored the production database backup(170GB) onto a development system.
> I first tried to do the following command:
> restore filelistonly from disk='f:\EEMSuite_db_200411111600.BAK';
> Then stopped it after 30 minutes realized that I need to use the MOVE
> command because the log and data file have different location on the
> development system.
> I then initiated the following:
> restore database MYDB from disk='f:\EEMSuite_db_200411111600.BAK'
> with move 'EEMSuite_model_Data' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite.mdf',
> move 'EEMSuite_model_Log' to
> 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\EEMSuite_log.LDF',
> replace
> It ran for two days and finally came back with the following After two days
> of restore process:
> Server: Msg 5173, Level 16, State 1, Line 1
> Cannot associate files with different databases.
> Log file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\EEMSuite_log.LDF'
> does not match the primary file. It may be from a different database or the
> log may have been rebuilt previously.
> Processed 22272096 pages for database 'MYDB', file 'EEMSuite_model_Data' on
> file 1.
> Processed 18042 pages for database 'MYDB', file 'EEMSuite_model_Log' on file
> 1.
> RESTORE DATABASE successfully processed 22290138 pages in 176133.332 seconds
> (1.036 MB/sec).
> I am concerned about the frist error message but was wondering if that came
> from first failed attempt. By looking at the following two messages it looks
> like the restore was a successful one?
> My user can not access the database and the newly restored user database
> does not show up in the Object Browser. What am missing? Do I need a
> "recovery" command somewhere? Please help!!
> --Ling
|||First verify if the logins exist in the new server, then run
sp_change_users_login. See BOL for more details.
Gary
"Ling" <Ling@.discussions.microsoft.com> wrote in message
news:5FD532FE-7547-4018-9177-6B2A5D9A693F@.microsoft.com...
> I rebooted the server and now I can query the tables within the database.
> But, while my user trying to connect, they can not! Please advise how to
fix[vbcol=seagreen]
> this problem. Thank you!!
>
> Ling
> "Ling" wrote:
system.[vbcol=seagreen]
days[vbcol=seagreen]
Server\MSSQL\data\EEMSuite_log.LDF'[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
file[vbcol=seagreen]
seconds[vbcol=seagreen]
came[vbcol=seagreen]
looks[vbcol=seagreen]