|
|
| morphius 2006-12-12, 7:12 pm |
| 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 Socol 2006-12-12, 7:12 pm |
| 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.
| |
| Kevin3NF 2006-12-12, 7:12 pm |
| 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@microso
ft.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.
>
>
| |
| Uri Dimant 2006-12-12, 7:12 pm |
| 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:
>
| |
| Tracy McKibben 2006-12-12, 7:12 pm |
| 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
| |
| morphius 2006-12-12, 7:12 pm |
| 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@microso
ft.com...
>
>
>
| |
| Hari Prasad 2006-12-12, 7:12 pm |
| 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@microso
ft.com...[color=darkred]
> 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:
>
| |
| morphius 2006-12-12, 7:12 pm |
| 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...
>
>
>
| |
| Uri Dimant 2006-12-12, 7:12 pm |
| 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@microso
ft.com...[color=darkred]
> 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:
>
| |
| Tracy McKibben 2006-12-12, 7:12 pm |
| 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
|
|
|
|