Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 restore fail

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-19 : 09:58:20
Hi

I have a database backup (.bak). The backup file is from a database called "MyDatabase" and I want to restore that to Another database called "MyDatabase2", but when I do that the "MyDatabase" is restored instead, and that even after I change the filename of the database file to restore. Is this not possible or am I doing it wrong?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 11:07:23
You need to restore with move option. Please post your restore script. If you used SSMS GUI, it can generate a script for you
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-19 : 11:23:25
I do use the SSMS GUI, where do I find the "with mode" option?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 11:41:37
Under the Files tab, in the column Restore As, you will find the destination file paths. When using Restore to clone a db, you need to change these to something new (and unique)
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-19 : 12:17:13
Thanks
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-23 : 05:56:51
Hi

A weird thing happends when I restore the "MyDatabase2" from the "MyDatabase" bak file, the "MyDatabase2" is restored from the old "MyDatabase" and get all data, but the "MyDatabase" is stuck in restoring, this can be fixed with RESTORE DATABASE [databasnamn] WITH RECOVERY, but why does it occur?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-23 : 13:02:08
quote:
Originally posted by magmo

Hi

A weird thing happends when I restore the "MyDatabase2" from the "MyDatabase" bak file, the "MyDatabase2" is restored from the old "MyDatabase" and get all data, but the "MyDatabase" is stuck in restoring, this can be fixed with RESTORE DATABASE [databasnamn] WITH RECOVERY, but why does it occur?



If that occurred, then you do something wrong. For that to have happened, you would have ran the restore on MyDatabase and selected the WITH NORECOVERY option. I can't think of another reason for it occur.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-24 : 08:08:46
I select "Overwrite the exsisting database (with replace)" and use "Restore with recovery"..
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-03 : 09:09:58
Is there Another way that I can try?
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-04 : 04:46:15
If I make a script using the Sql server manager I get this ...



USE [master]
BACKUP LOG [Survey] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Survey_LogBackup_2015-01-04_10-36-04.bak' WITH NOFORMAT, NOINIT, NAME = N'Survey_LogBackup_2015-01-04_10-36-04', NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [SurveyTest] FROM DISK = N'D:\Work-Projects\Survey.2\Survey.bak' WITH FILE = 1, MOVE N'Survey' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SurveyTest.mdf', MOVE N'Survey_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SurveyTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5

GO



The "SurveyTest" database is restored from "survey" database but it also put the "survey" database in restoring, and its stuck there until I run RESTORE DATABASE [Survey] WITH RECOVERY



Can someone please se what could be wrong?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-05 : 13:13:57
It's because you selected the option to backup the tail of the log. Don't do that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-01-06 : 04:22:17
Thanks, that did the trick.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-06 : 11:58:20


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -