Friday, March 30, 2012

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

No comments:

Post a Comment