Restoring .BAK file to a different server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Restoring .BAK file to a different server?

Author

Topic

arlene
Starting Member

12 Posts

Posted - 2004-10-04 : 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

38200 Posts

Posted - 2004-10-04 : 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 - 2004-10-04 : 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

38200 Posts

Posted - 2004-10-04 : 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 - 2004-10-04 : 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

38200 Posts

Posted - 2004-10-04 : 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 - 2004-10-04 : 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 - 2004-10-04 : 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

38200 Posts

Posted - 2004-10-04 : 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

arlene
Starting Member

12 Posts

Posted - 2004-10-04 : 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

38200 Posts

Posted - 2004-10-04 : 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 - 2004-10-04 : 19:49:14

Fixed, thanks for all your help.

Slammed
Starting Member

3 Posts

Posted - 2006-06-20 : 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 Post

Posted - 2007-03-19 : 18:09:35

i have the same problem. we want to move our site to another server but the one made the system is not available anymore. so i took the .bak file and tried to restore it. But in my numerous attempts i got these errors.

http://img406.imageshack.us/img406/1381/sql1zq3.jpg

http://img181.imageshack.us/img181/2699/sql2kr1.jpg

http://img58.imageshack.us/img58/3915/sql3ru6.jpg

virkmasood81
Starting Member

1 Post

Posted - 2009-09-03 : 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 - 2009-10-14 : 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

1 Post

Posted - 2010-06-07 : 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

38200 Posts

Posted - 2010-06-07 : 15:56:43

sassydatachic, please start a new topic for your issue. Make sure you include the output of RESTORE FILELISTONLY so that we can help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-06-07 : 18:32:17

This post may help if you are trying to work out the correct RESTORE command:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#214941

SQL Red68
Starting Member

8 Posts

Posted - 2010-07-23 : 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

22859 Posts

Posted - 2010-07-23 : 12:03:28

Duplicate post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147723

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources