.bak file problems - SQL Server Forums

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)

.bak file problems

Author

Topic

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 17:24:40

I am new to SQL server

Can you pls help me with this:

I only have a .bak file of a database. when I try this:
RESTORE DATABASE SMV
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

I get this:

Server: Msg 3135, Level 16, The backup set in fiState 2, Line 1
le 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak' was created by BACKUP DATABASE...FILE=<name> and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Why is this and what should I do.

Thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 18:21:15

What does this show:

RESTORE FILELISTONLY
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

And how about:

RESTORE HEADERONLY
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

Tara

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 19:14:39

Thank for the reply. This is what follows:
RESTORE FILELISTONLY
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

result=
LogicalName PhysicalName Type FileGroupName Size MaxSize
SMV_Data f:\smv\MSSQL\data\SMV_Data.MDF D PRIMARY 20971520000 35184372080640

and
RESTORE HEADERONLY
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

result=

SMV backup NULL 4 NULL 0 1 2 MYDBS\Administrator MYDBS SMV 539 2004-08-28 16:14:55.000 7865309696 271000002951300001 271000002958700001 271000002958500001 0 2004-08-29 08:13:59.000 2004-08-29 08:35:46.000 52 228 1033 196609 80 4608 8 0 534 INECDBS-YB 0 {06995306-5C68-4C00-893D-2F846DBA1BAE} {06995306-5C68-4C00-893D-2F846DBA1BAE} SQL_Latin1_General_CP1_CI_AS

and if I do
RESTORE DATABASE SMV
FROM DISK = 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak'

I get the same error again
Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'F:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\smv.bak' was created by BACKUP DATABASE...FILE=<name> and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What can I do?
Thanks for the help so far.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 19:18:47

Was the backup command done on this same server that you are trying to restore to?

Were there two lines of data from RESTORE FILELISTONLY? You've only displayed one.

Tara

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 19:25:07

No the restore is not on the same server.
This is a new server that I need to install the same database on.
Should I do it some other way?

"Were there two lines of data from RESTORE FILELISTONLY?"
Nope only that what I have shown you...?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 19:31:21

Your backup file only contains the MDF file. Can you run the backup command again but like this?:

BACKUP DATABASE SMV
TO DISK = 'F:\MSSQL\BACKUP\SMV.BAK'
WITH INIT

Change the path information if need be. Then copy that .BAK file to your new server. Then run RESTORE command. If F:\smv\MSSQL\DATA doesn't exist on this new server or if you want to change locations of the MDF and LDF files, then you'll need to use WITH MOVE option of RESTORE command. Here is an example:

RESTORE DATABASE SMV
FROM DISK = 'F:\MSSQL\BACKUP\SMV.BAK'
WITH REPLACE,
MOVE 'SMV_Data' TO 'F:\MSSQL\DATA\SMV_Data.MDF',
MOVE 'SMV_Log' TO 'F:\MSSQL\DATA\SMV_Log.LDF'

Tara

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 19:39:18

I have a problem...

The original server is about 4000km from me and is not on the internet

So if I do not do the backup again with "WITH INIT" I am stuffed?
I used the Enterprise Manager to back up the database.

So there is no way around it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 19:41:58

WITH INIT just means overwrite the file if it exists. The backup that was done isn't enough to restore the entire database. So you can either do the backup command again like I suggested or sp_detach_db the backup, either way though someone is going to have to get access to this other server and copy the .BAK file to you or the .MDF file if you chose sp_detach_db.

Tara

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 19:53:09

Thank you Tara,

Only problem is: the .bak file is 7Gig in size (730Mb compressed with rar)
Will the .MDF file also be this big?

If I recreate the table (there is only one) will that not work?

Thank you again for you help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 19:55:30

The MDF file will be that big too.

Is there 7GB of data in the table? If so, then no recreating the table will not help. You might consider dropping the indexes to make it smaller. But I'm sure you'll still have a few gigabytes of data.

When we need to move large files, we ship a USB drive that has the file on it. 7GB isn't that huge though, so you should be able to copy it over in a few hours if you have the bandwidth.

Tara

bluefox8080
Starting Member

6 Posts

Posted - 2004-08-31 : 20:01:43

There is only 8 fields in the Table and only one index(Primary key)
It is big because there is 50 million rows in the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources