Restoring a database (without effecting original) - to test the retore process...

Question

Restoring a database (without effecting original) - to test the retore process...

jellybean

SSCrazy

Points: 2014
More actions
June 13, 2016 at 2:12 am

#308351

Hi,
Hope this is the right forum. I using SQL Server 2014 and want to test the "restore" process is working correctly. What I wan to do is restore an existing database (lets call it "DatabaseA") to a completely new name like "DatabaseA_RESTORE_TEST"
I do not want DatabaseA effected in any way - it has to keep continuing to work ie I do not want it overwritten. At the end of the restore I check the restored DB and then delete it.
I am slightly confused as to what are the best options on the "Restore Database" window:
1. On the General tab I am using the new database name
2. On the Fields tab - the Restore As point to a different .mdf path for both the rows and logs
3. On the "Options" I'm unsure what options to select.
Can you help with point #3 please? I would greatly appreciate it.
Thanks,
J.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36096 More actions · Answer 1

jellybean (6/13/2016)
Hi,
Hope this is the right forum. I using SQL Server 2014 and want to test the "restore" process is working correctly. What I wan to do is restore an existing database (lets call it "DatabaseA") to a completely new name like "DatabaseA_RESTORE_TEST"
I do not want DatabaseA effected in any way - it has to keep continuing to work ie I do not want it overwritten. At the end of the restore I check the restored DB and then delete it.
I am slightly confused as to what are the best options on the "Restore Database" window:
1. On the General tab I am using the new database name
2. On the Fields tab - the Restore As point to a different .mdf path for both the rows and logs
3. On the "Options" I'm unsure what options to select.
Can you help with point #3 please? I would greatly appreciate it.
Thanks,
J.

You dont need to select any in 'Options' Page.

Super Cat SSCertifiable Points: 7332 More actions · Answer 2

I have had this issue with SQL 2012. Is there a yellow warning on the top of the page above backing up the tail end of the transaction log.

If there is un-tick the option on the option Page if I remember correctly.

Make sure the warning has gone and then restore.

jellybean SSCrazy Points: 2014 More actions · Answer 3

Well it defaults to "Restore with Recovery" - is this correct?

Super Cat SSCertifiable Points: 7332 More actions · Answer 4

Super Cat

SSCertifiable

Points: 7332

June 13, 2016 at 3:50 am

#1884581

Yes, That's fine. Were you getting the warning

jellybean SSCrazy Points: 2014 More actions · Answer 5

I think so - Above the words :"Tail-Log backup" it says :"Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored"

However I feel this is an informational message associated with the Recovery state option (Restore with recovery)

Super Cat SSCertifiable Points: 7332 More actions · Answer 6

If it is in a yellow warning triangle it is the cause of your problem. Remove by how I previously stated.

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 7

For testing purposes, I'd suggest you restore to a different SQL Server instance other than your production instance. Make mistakes elsewhere. You don't want to screw up production.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Super Cat SSCertifiable Points: 7332 More actions · Answer 8

Good point Grant, I assumed you were on a test system and are simply making a copy of the same db.

jellybean SSCrazy Points: 2014 More actions · Answer 9

Thanks for the advice. Forgive me but how do you create a different SQL instance lets call it "TestInstance"

Phil Parkin SSC Guru Points: 246955 More actions · Answer 10

jellybean (6/13/2016)
Thanks for the advice. Forgive me but how do you create a different SQL instance lets call it "TestInstance"

Another instance = another install of SQL Server.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Super Cat SSCertifiable Points: 7332 More actions · Answer 11

Are you looking after PRODUCTION SQL servers?

Are you considering installing an instance on a PRODUCTION SQL Server?

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 12

Talib123 (6/13/2016)

Are you looking after PRODUCTION SQL servers?

Are you considering installing an instance on a PRODUCTION SQL Server?[/quote

Exactly. We don't want to do this. Instead, install a copy of SQL Server on your local machine, or on another machine, or best of all, on a virtual machine locally (this way you can mess it up without ever messing up your local machine, great way to learn and test).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 13

jellybean (6/13/2016)
Hi,
Hope this is the right forum. I using SQL Server 2014 and want to test the "restore" process is working correctly. What I wan to do is restore an existing database (lets call it "DatabaseA") to a completely new name like "DatabaseA_RESTORE_TEST"
I do not want DatabaseA effected in any way - it has to keep continuing to work ie I do not want it overwritten. At the end of the restore I check the restored DB and then delete it.
I am slightly confused as to what are the best options on the "Restore Database" window:
1. On the General tab I am using the new database name
2. On the Fields tab - the Restore As point to a different .mdf path for both the rows and logs
3. On the "Options" I'm unsure what options to select.
Can you help with point #3 please? I would greatly appreciate it.
Thanks,
J.

I'm going to echo the recommendations that you really need to practice before you even think of doing anything like this anywhere near your production box. You need to spend some time in "Books Online" and then practice on a Dev server... not a production server.

Here are some links to get you started using SSMS. There are more links available for doing it all using T-SQL but let's peel one potato at a time ;-).

https://msdn.microsoft.com/en-us/library/ms190436.aspx

https://msdn.microsoft.com/en-us/library/ms177429.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 14

in order to restore the database which is already exits you need to check the mdf ,ldf and ndf (if any) check where is is storing and check the file name should not be same as the old file other wise you will have an issue the file is exist