'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
Jeff
You can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf '
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff
sql

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONda
ta_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

'not a valid Microsoft Tape Format backup set' error when trying to restore DB

Hello
I have a point in time Microsoft SQL Server 2000 .mdf data
file which was taken from a snapshot. Basically, it is the
database at a point in time, which I can attach to and
view data no problem in a sql server.
I also have hourly transaction logs which occured after
the above .mdf data file snapshot. I would like to apply
those transaction log files to the .mdf data file. I've
tried many many varieties, but seem to get farthest with
the following code:
ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
RESTORE DATABASE JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
WITH NORECOVERY
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_7.00.00__JDE_PRODUCTION.trb'
WITH NORECOVERY
[...MORE RESTORE HOURLY LOG STATEMENTS...]
RESTORE LOG JDE_PRODUCTION
FROM DISK='F:\mssql7\jdedata\07-22-
2004_13.00.01__JDE_PRODUCTION.trb'
WITH RECOVERY
/****end****/
My problem seems to be the following error:
Server: Msg 3242, Level 16, State 1, Line 2
The file on device 'F:\mssql7
\jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
Microsoft Tape Format backup set.
Can't seem to get around the fact that Microsoft is
looking for the restore database file as a Tape format,
which it isn't, it's a .mdf database file.
Any clues?
Thanks in advance...
JeffYou can't RESTORE from an 'mdf' file because a RESTORE command expects a
file created with BACKUP. The backup file will be in MTF format regardless
of the underlying media type.
The proper way to accomplish your task is to start with a full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff" <jblanding@.nwpasta.com> wrote in message
news:76fd01c47672$4443f470$a401280a@.phx.gbl...
> Hello
> I have a point in time Microsoft SQL Server 2000 .mdf data
> file which was taken from a snapshot. Basically, it is the
> database at a point in time, which I can attach to and
> view data no problem in a sql server.
> I also have hourly transaction logs which occured after
> the above .mdf data file snapshot. I would like to apply
> those transaction log files to the .mdf data file. I've
> tried many many varieties, but seem to get farthest with
> the following code:
> ALTER DATABASE JDE_PRODUCTION SET SINGLE_USER WITH
> ROLLBACK IMMEDIATE
> GO
> RESTORE DATABASE JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\jde_PRODUCTIONdata_new.mdf'
> WITH NORECOVERY
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_7.00.00__JDE_PRODUCTION.trb'
> WITH NORECOVERY
> [...MORE RESTORE HOURLY LOG STATEMENTS...]
> RESTORE LOG JDE_PRODUCTION
> FROM DISK='F:\mssql7\jdedata\07-22-
> 2004_13.00.01__JDE_PRODUCTION.trb'
> WITH RECOVERY
> /****end****/
> My problem seems to be the following error:
> Server: Msg 3242, Level 16, State 1, Line 2
> The file on device 'F:\mssql7
> \jdedata\jde_PRODUCTIONdata_new.mdf' is not a valid
> Microsoft Tape Format backup set.
> Can't seem to get around the fact that Microsoft is
> looking for the restore database file as a Tape format,
> which it isn't, it's a .mdf database file.
> Any clues?
> Thanks in advance...
> Jeff

Noob here, dumb, but quick question

Ok, I know a LITTLE about SQL 2000. I am just starting to get my feet wet in the area. I know how to install SQL, backup DB's and Restore them. Can even do them over the network now. YAY ME! Anyway, here is my question. I have a production server with 4 RAID arrays. I have one for my OS, one for the Main SQL DB's (heavy transaction DB's), one for my log files, and one that is supposed to be for my temp DB. Well when I installed SQL it asked me for the default data path, which would be where my main SQL db's go. I dont remember it asking me where I want the temp DB to go. How do I change the location of the tempdb.mdf and tempdb.ldf to the drive arrays I want them to go to, even though I have already installed SQL.

Thanks. And sorry ahead of time for the noobiness of the question. I did do a search first, but didnt really see anything that would help me.To change where the data files are located for a given database, just right-click the database and change the Data Files and Transaction Log 'Properties'.

With that said, I don't remember if you can change the data file location for 'system' databases. So you might want to check into that...even so, I wouldn't expect any speed benefit from move the tempdb to a different location.

BTW, good question with some solid background for someone claiming to be noob.

Late,

Alexander|||We use this script for DR to relocate the tempdb to an array large enough to allow it to grow as needed. Change the paths and size to fit your situation, and don't forget to stop and restart the sever after altering the database.


use tempdb
alter database tempdb modify file (name = tempdev,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf', size = 10000 MB)
alter database tempdb modify file (name = templog,
filename = 'T:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

As for me .. this link is permanently in my IE favourites folder ...

non-identity Primary Key

I need to be able to increment a Primary Key of type int without using
IDENTITY.
The reason is that I need to be able to Archive and restore data to this
table, and maintain the Primary Key.
Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
inserting single records into Table1.
The problem is when I need to insert new records from a select statement.
The trigger doesn't work for a "set" type insert.
I searched examples, but everything I found only supported single inserts.
Here's an example of the table structure.
CREATE TABLE Table1(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
CREATE TABLE Table2(
MyIDfield int NOT NULL PRIMARY KEY,
MyText varchar(50)
)
As I explained, I need to..
[1] have unique Primary Keys [MyIDfield] across both tables
[2] be able to insert thousands of records into Table1 using an
insert/select query [trigger solution preferred]
[3] NOT use IDENTITY, in case I need to restore archived records [move back
from Table2 to Table1]
Thanks for any help,
ChrisYou can use the IDENTITY property, archive and restore data and still
maintain the primary key. Deleting records does not reset the IDENTITY
property (unless you use TRUNCATE).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Chris" wrote:

> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
> Here's an example of the table structure.
> CREATE TABLE Table1(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> CREATE TABLE Table2(
> MyIDfield int NOT NULL PRIMARY KEY,
> MyText varchar(50)
> )
> As I explained, I need to..
> [1] have unique Primary Keys [MyIDfield] across both tables
> [2] be able to insert thousands of records into Table1 using an
> insert/select query [trigger solution preferred]
> [3] NOT use IDENTITY, in case I need to restore archived records [move ba
ck
> from Table2 to Table1]
> Thanks for any help,
> Chris
>
>|||And forgot to mention that you can restore records maintaining their primary
key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity insert'
checked).
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> You can use the IDENTITY property, archive and restore data and still
> maintain the primary key. Deleting records does not reset the IDENTITY
> property (unless you use TRUNCATE).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Chris" wrote:
>|||That's the ticket! I've heard of that, but it never came to mind. Never
had a case where I had to use SET IDENTITY_INSERT ON.
Thanks for the help! Perfect solution.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:217620C1-DAF2-45E5-8660-DD23BE1A8B3C@.microsoft.com...
> And forgot to mention that you can restore records maintaining their
primary
> key using SET IDENTITY_INSERT ON (or DTS/SSIS with 'Enable identity
insert'
> checked).
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Ben Nevarez" wrote:
>
this
when
statement.
inserts.
[move back|||Chris (rooster575@.hotmail.com) writes:
> I need to be able to increment a Primary Key of type int without using
> IDENTITY.
> The reason is that I need to be able to Archive and restore data to this
> table, and maintain the Primary Key.
> Anyway, I setup a trigger to get the Max(MyIDfield) which works fine when
> inserting single records into Table1.
> The problem is when I need to insert new records from a select statement.
> The trigger doesn't work for a "set" type insert.
> I searched examples, but everything I found only supported single inserts.
Bounce the data over a temp table with an IDENTITY column, and the
MAX value to the IDENTITY column.
If you are on SQL 2005, you could use the Row_number() function and be
saved the temp table.
I assume that the trigger is an INSTEAD OF trigger?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

No sync - No Initialize & scripts

If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
|||It is Sql 2000, Thanx for the link to the kb! You're a great help!!!
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uSamV6J5FHA.2524@.TK2MSFTNGP10.phx.gbl...
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS