| Author |
 Topic  |
|
arlene
Starting Member
12 Posts |
 Posted - 10/04/2004 : 14:10:23
|
Hello all,
I have a database on a hosted server in which the administartor
made a backup copy of my database and put it in my root FTP folder
so I could download it.
I need to restore it to my own server but have no option to point
to the .bak file using the restore wizard. (since it was never on this server yet ??) Can someone tell me how I can use the .bak file to copy the database to my new server?
Thanks in advance,
Arlene |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/04/2004 : 14:15:01
|
RESTORE DATABASE DBName
FROM DISK = 'C:\Backup.BAK'
WITH REPLACE, MOVE 'DBName_Data' TO 'C:\MSSQL\DATA\DBName_Data.MDF',
MOVE 'DBName_Log' TO 'C:\MSSQL\DATA\DBName_Log.LDF'
So IOW, use RESTORE DATABASE in Query Analyzer. To do it inside Enterprise Manager, select from device on the first screen.
Tara |
 |
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 14:47:39
|
I get the following error:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How can I put the DB in user only mode? Is this what I need to do?
Thanks |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/04/2004 : 15:37:24
|
You need to disconnect whoever is connected to it. You can issue a KILL under the spid. Do a sp_who in Query Analyzer to see who is connected.
Tara |
|
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 17:16:09
|
Killed the spid and it did not work, still said the same thing. Stopped the server, rebooted and it killed the process.
however, no I get the following error:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'storeshop_Data' is not part of database 'storeshop'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I do not have a database named storeshop yet so I do not know what
it is referring to.
any ideas?
Thanks for all your help |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/04/2004 : 17:19:17
|
You'll need to run RESTORE FILELISTONLY in order to work this out:
RESTORE FILELISTONLY
FROM DISK = 'C:\Backup.BAK'
Post the results here and we'll be able help write the correct RESTORE DATABASE command for you.
Tara |
|
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 17:24:46
|
Thanks, here are the results from Query Analyzer:
computerscripts E:\Webspace\support\storeshop\storeshop_Data.mdf D PRIMARY 1441792 35184372080640
computerscripts_log E:\Webspace\support\storeshop\storeshop_log.LDF L NULL 1048576 35184372080640
The path above is the old path from my other web hosting company.
|
|
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 17:27:10
|
SORRY, THE ABOVE POST WAS WRONG FROM CUTTING OTHER CLIPBOARD ACTIVITY: HERE IS THE CORRECT ONE:
Thanks, here are the results from Query Analyzer:
storeshop E:\Webspace\support\storeshop\storeshop_Data.mdf D PRIMARY 1441792 35184372080640
storeshop_log E:\Webspace\support\storeshop\storeshop_log.LDF L NULL 1048576 35184372080640
The path above is the old path from my other web hosting company.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/04/2004 : 17:29:32
|
So here is what you need to do based upon the above results:
RESTORE DATABASE Storeshop
FROM DISK = 'C:\Backup.BAK'
WITH REPLACE, MOVE 'storeshop' TO 'C:\MSSQL\DATA\Storeshop_Data.MDF'
MOVE 'storeshop_log' TO 'C:\MSSQL\DATA\Storeshop_Log.LDF'
In the above, you will need to modify three things: the path and filename to your backup file, the path where you want the MDF file to be located, and the path where you want the LDF file to be located. For the last two edits, these directories must exist in order for it to work, so create them if you need to.
Tara |
Edited by - tkizer on 10/04/2004 17:30:11 |
|
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 18:22:50
|
Get the following error. I triple checked it and its correct so I do not know
why it is doing this:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'MOVE'. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 10/04/2004 : 18:28:18
|
Oops, missing a comma:
RESTORE DATABASE Storeshop
FROM DISK = 'C:\Backup.BAK'
WITH REPLACE, MOVE 'storeshop' TO 'C:\MSSQL\DATA\Storeshop_Data.MDF',
MOVE 'storeshop_log' TO 'C:\MSSQL\DATA\Storeshop_Log.LDF'
Tara |
|
|
|
arlene
Starting Member
12 Posts |
Posted - 10/04/2004 : 19:49:14
|
Fixed, thanks for all your help.
|
|
|
|
Slammed
Starting Member
3 Posts |
Posted - 06/20/2006 : 23:35:01
|
I know this is an old thread but I just want to know if anybody can help me figure out why this restore script wont work
RESTORE FILELISTONLY
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
RESTORE DATABASE vernonstoragecenter
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
WITH RECOVERY,
MOVE 'vernonstoragecenter_data' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_data.mdf',
MOVE 'vernonstoragecenter_log' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_log.ldf'
GO
This is the error I am getting
Error -2147217900
Cannot open backup device 'D:\temp\vernonstoragecenter_db_200601270401.BAK'. Device error or device off-line. See the SQL Server error log for more details.
RESTORE FILELISTONLY
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
RESTORE DATABASE vernonstoragecenter
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
WITH RECOVERY,
MOVE 'vernonstoragecenter_data' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_data.mdf',
MOVE 'vernonstoragecenter_log' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_log.ldf'
GO
According to my host I am on the d:/ drive
Now I should also mention that this is a bak file given to me and I ftp ed it onto the site into the temp folder so I can do the restore
I KNOW NOTHING ABOUT SQL (not my field but I might have to learn)
|
|
|
|
gageroth
Starting Member
1 Posts |
|
|
virkmasood81
Starting Member
Saudi Arabia
1 Posts |
Posted - 09/03/2009 : 04:20:20
|
quote:
Originally posted by Slammed
I know this is an old thread but I just want to know if anybody can help me figure out why this restore script wont work
RESTORE FILELISTONLY
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
RESTORE DATABASE vernonstoragecenter
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
WITH RECOVERY,
MOVE 'vernonstoragecenter_data' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_data.mdf',
MOVE 'vernonstoragecenter_log' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_log.ldf'
GO
This is the error I am getting
Error -2147217900
Cannot open backup device 'D:\temp\vernonstoragecenter_db_200601270401.BAK'. Device error or device off-line. See the SQL Server error log for more details.
RESTORE FILELISTONLY
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
RESTORE DATABASE vernonstoragecenter
FROM DISK = 'D:\temp\vernonstoragecenter_db_200601270401.BAK'
WITH RECOVERY,
MOVE 'vernonstoragecenter_data' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_data.mdf',
MOVE 'vernonstoragecenter_log' TO 'D:\MSSQL\Datafiles\data\vernonstoragecenter_log.ldf'
GO
According to my host I am on the d:/ drive
Now I should also mention that this is a bak file given to me and I ftp ed it onto the site into the temp folder so I can do the restore
I KNOW NOTHING ABOUT SQL (not my field but I might have to learn)
HERE is Solution:
STep 1:
RESTORE FILELISTONLY FROM DISK = 'D:\YourDatabase.bak'
this will generate a logical file e.g XYZDatabase
Step 2:
RESTORE DATABASE NEWDatabaseName FROM DISK = 'D:\YourDatabase.bak'
WITH MOVE 'XYZDatabase' TO 'c:\XYZDatabase.mdf',
MOVE 'XYZDatabase_log' TO 'c:\XYZDatabase.ldf'
So, It will restore and create new database.
Plz check and reply (if u dont mind)
Have a Nice Day.
Masood H. Virk |
|
|
|
prabhakar
Starting Member
2 Posts |
Posted - 10/14/2009 : 07:18:30
|
Hi Every one
RESTORE DATABASE IndiaStudyChannelDatabase FROM DISK = 'C:\Documents and Settings\Administrator\Desktop\prabhakar\india study channel\IndiaStudyChannelDatabase.bak'
with MOVE 'IndiaStudyChannelDatabase' TO 'C:\Documents and Settings\Administrator\Desktop\prabhakar\india study channel\IndiaStudyChannelDatabase.mdf',
MOVE'IndiaStudyChannelDatabase_log' TO 'C:\Documents and Settings\Administrator\Desktop\prabhakar\india study channel\IndiaStudyChannelDatabase.ldf'
i am trying this query for backup the data from Backup file.But i am getting the error RESTORE DATABASE is terminating abnormally.will you please rectify the problem. |
|
|
|
sassydatachic
Starting Member
USA
1 Posts |
Posted - 06/07/2010 : 15:49:45
|
I have tried all listed methods above.
The error I am getting after I run any of the following attempts below is:
Msg 3132, Level 16, State 1, Line 2
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
What I have attempted:
RESTORE FILELISTONLY
FROM DISK = 'c:\stratagen_dw.bak'
RESTORE DATABASE Stratagen_DW
FROM DISK = 'c:\stratagen_dw.bak'
WITH MOVE 'Stratagen_dw' TO 'c:\stratagen_dw.mdf',
MOVE 'Stratagen_dw_log' TO 'c:\stratagen_dw_log.ldf'
Or I have tried to:
RESTORE DATABASE Stratagen_DW
FROM DISK = 'c:\stratagen_dw.bak'
WITH REPLACE, MOVE 'Stratagen_DW' TO 'C:\MSSQL\DATA\Stratagen_DW.MDF',
MOVE 'Stratagen_DW_Log' TO 'C:\MSSQL\DATA\Stratagen_DW_Log.LDF'
Cheers,
Laura |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
SQL Red68
Starting Member
8 Posts |
Posted - 07/23/2010 : 09:43:41
|
Hi all,
Could you please take a look at the following query and error message and help me get the query right?
RESTORE DATABASE [DreamMedia_Live_V2] FROM
DISK 'E:\Downloads\MSSQL10.MSSQLSERVER\MSSQL\Backup\DreamMediaLive_1207210.bak'
WITH FILE = 1, MOVE N'DreamMedia_Live_log'
TO N'g:\Log\DreamMedia_Log_V2.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "H:\Log\DreamMedia_LIVE_log2.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 2
File 'DreamMedia_LIVE_log2' cannot be restored to 'H:\Log\DreamMedia_LIVE_log2.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Thanks,
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
Topic |
|
Restoring .BAK file to a different server?
