| Author |
Topic |
|
magmo
Aged Yak Warrior
558 Posts |
 Posted - 2014-12-19 : 09:58:20
|
| HiI 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 |
 |
|
|
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? |
|
|
|
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) |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2014-12-19 : 12:17:13
|
| Thanks |
|
|
|
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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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".. |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2015-01-03 : 09:09:58
|
| Is there Another way that I can try? |
|
|
|
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 = 5RESTORE 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 = 5GO 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? |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
magmo
Aged Yak Warrior
558 Posts |
Posted - 2015-01-06 : 04:22:17
|
| Thanks, that did the trick. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
restore fail
Tara Kizer