All I have is the .bak file - 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)

All I have is the .bak file

Author

Topic

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 13:59:21

We have a client who purchased hosting with us and in their database folder they have a .BAK file. Is this all I need to get them going?

We do have SQL Server 2000 installed on the system, however I've tried to RESTORE and IMPORT (won't look for .BAK extensions) to no avail.

Do I need to ask them for a different type of file?

Thanks,

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 14:01:24

.BAK is all you need. Run the RESTORE command in Query Analyzer. Here is an example:

RESTORE DATABASE DBName
FROM DISK = 'F:\MSSQL\BACKUP\DBName_05022004.BAK'
WITH REPLACE, MOVE 'DBName_Data' TO 'F:\MSSQL\DATA\DBName_Data.MDF,
MOVE 'DBName_Log' TO 'F:\MSSQL\DATA\DBName_Log.LDF', STATS

So I guess this means that the hosting company doesn't have a DBA? Who's going to keep the system optimized?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 14:03:57

Use code....first thing...

Do a RESTORE FILELISTONLY first

Brett

8-)

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 14:30:46

I ran:

RESTORE DATABASE info
FROM DISK='D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_sql_042204.bak'
WITH REPLACE, MOVE 'info_Data' TO 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_Log.MDF',
MOVE 'info_Log' TO 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_Log.LDF', STATS

Result:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'info_Data' is not part of database 'info'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I replaced "DATABASE" with FILELISTONLY and then tried running it again:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FILELISTONLY'.

I read the first command and it is supposed to create the database file(s) where the .BAK file is now correct? I want to keep the database in their website so we can keep track of how much total storage space they are using.

As far as the DBA? He's right here typing to you. LOL.

Thanks for all your help,

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 14:34:31

Do you have SQL Server Books Online? I would look up RESTORE DATABASE and RESTORE FILELISTONLY to get the syntax. The syntax that I posted is correct, but you might not need to run the MOVE options.

What do you mean keep the databse in their website? The database has to be installed on the local database server.

The database files will be created in the default MSSQL\Data directory or you can move them with the MOVE option like in my example.

RESTORE FILELISTONLY will show you which logical names to use for info database in the MOVE option.

The most basic RESTORE command is (I showed you a non-basic one in case you need to move the files):

RESTORE DATABASE DBName
FROM DISK = 'F:\MSSQL\BACKUP\DBName.BAK'

For RESTORE FILELISTONLY:

RESTORE FIELISTONLY DBName
FROM DISK = 'F:\MSSQL\BACKUP\DBName.BAK'

You can not use MOVE option in RESTORE FILELISTONLY.

Tara

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 14:53:06

Thanks Tara for your quick replies and your help. I will take your advice regarding looking up SQL books online for syntax.

As far as the RESTORE FILELISTONLY, I ran the most basic of commands:
RESTORE FILELISTONLY info_sql
FROM DISK = 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_sql_042204.bak'

to no avail because I got the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FILELISTONLY'.

Even the following does not work:
RESTORE DATABASE info_sql
FROM DISK = 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_sql_042204.bak'

RESPONSE:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\SQLDATA\MSSQL\Data\infoscriber_Data.MDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'infoscriber_Data' cannot be restored to 'C:\SQLDATA\MSSQL\Data\infoscriber_Data.MDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\SQLDATA\MSSQL\Data\infoscriber_Log.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'infoscriber_Log' cannot be restored to 'C:\SQLDATA\MSSQL\Data\infoscriber_Log.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Do I need to create a database first before I do this? Like I said, all I have is the .BAK file in the website.

As far as what I said, "keep the database in the website", The webserver is also running SQL Server and I wanted to keep the database files in a database directory on the website to gauge the size of the site.

Thanks again Tara, I hope you're enjoying the weather out there (I'm in Jersey... YUCK!)

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-03 : 14:56:45

Put he dump on the sql server box itself....

Are you doing this from a client or at the box itself?

When you do the restorte, it will see the config as the server, not the client....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 14:56:47

I had the RESTORE FILELISTONLY command incorrect in my post:

RESTORE FILELISTONLY
FROM DISK = 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_sql_042204.bak'

Does that work now? What is the output?

Tara

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 14:59:24

I see... I didn't have to specify the database name.

Ok, ran it:

infoscriber_Data C:\SQLDATA\MSSQL\Data\infoscriber_Data.MDF D PRIMARY 3407872 35184372080640
infoscriber_Log C:\SQLDATA\MSSQL\Data\infoscriber_Log.LDF L NULL 18677760 35184372080640

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 15:15:20

Hello X002548,

I'm doing this at the box itself.

Thanks,

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 16:10:13

So it appears that the database was named infoscriber. So run this to restore it:

RESTORE DATABASE infoscriber
FROM DISK = 'D:\webs\webppliance\conf\domains\infoscriber\InetPub\wwwroot\database\info_sql_042204.bak'
WITH REPLACE

If you don't want the MDF and LDF files to be located in the C:\SQLDATA\MSSQL\DATA directories, then you'll need the MOVE option.

BTW, doesn't your server have other arrays for the database files? It is highly not recommended to install the database on the same array as the OS.

Tara

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 16:16:54

Thanks so much Tara,

Someone came over and they ran DTS to the current site and got the db that way. It is, however on the C drive. Do you recommend moving it to a directory within the website? I was thinking of moving it to keep track of how large the database grows to.

Thanks again for all your help,

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 16:21:32

I do not recommend it being on the C drive. IMO, the database files should be standardized to <SomeDriveLetter>:\MSSQL\DATA. We use F drive for MDFs and G drive for LDFs. We furthermore have an H drive for backups and E drive to install the applications. On database servers, ONLY SQL Server is installed. We have put the page file on E on some systems and on others on another drive altogether.

Tara

white300z
Starting Member

7 Posts

Posted - 2004-05-03 : 16:25:05

Sounds like you really have it together over there. We obviously need a separate SQL box in the future. I'll take your advice and get this thing off of the C drive.

Thanks again for all your help, your knowledge certainly shows.

Jim

Finarfin
Starting Member

28 Posts

Posted - 2004-05-27 : 13:27:06

Hi all,

I am very interested in Jim's problem, because I have the same. But the solution doesn't look like the same...
To explain my strange situation,

my query:
restore database OSCQD00
from disk = 'F:\Projects\Psa.Osc\BDD\20-DonnéesInit\OSCQD00_BK.BAK' with replace, medianame = 'MEDIA_OSC', mediapassword ='pwdsa'

errors for this query:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'g:\osc00\base\sql2k\data\OSCQDDA00.MDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'OSCQB00_Data' cannot be restored to 'g:\osc00\base\sql2k\data\OSCQDDA00.MDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'g:\osc00\base\sql2k\log\OSCQDLG00.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'OSCQB00_Log' cannot be restored to 'g:\osc00\base\sql2k\log\OSCQDLG00.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I tried to MOVE those files like this:
move 'g:\osc00\base\sql2k\data\OSCQB00_Data' to 'F:\Projects\Psa.Osc\BDD\20-DonnéesInit\OSCQDDA00.MDF',
move 'g:\osc00\base\sql2k\data\OSCQDLG00.LDF' to 'F:\Projects\Psa.Osc\BDD\20-DonnéesInit\OSCQDLG00.LDF'

but the error is:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'g:\osc00\base\sql2k\data\OSCQB00_Data' is not part of database 'OSCQD00'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I did it because I am obeying people:
restore filelistonly
from disk = 'F:\Projects\Psa.Osc\BDD\20-DonnéesInit\OSCQD00_BK.BAK' with medianame = 'MEDIA_OSC', mediapassword ='pwdsa'

The answer was:
OSCQB00_Data g:\osc00\base\sql2k\data\OSCQDDA00.MDF D PRIMARY 1005584384 3145728000
OSCQB00_Log g:\osc00\base\sql2k\log\OSCQDLG00.LDF L NULL 1662451712 2097152000

Now, I don't know what to do, what are those files: log and data?
My customer sent me 3 others data bases, and no error occured...

Please help, I need somebody

Thanks,

Romain (going back home because its 7.30 pm here in Paris)

Thank you all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-27 : 14:16:02

In the move option, you need to specify the logical names and not the physical. For yours, they are OSCQB00_Data and OSCQB00_Log.

restore database OSCQD00
from disk = 'F:\Projects\Psa.Osc\BDD\20-DonnéesInit\OSCQD00_BK.BAK' with replace,
move 'OSCQB00_Data' TO 'C:\WhereverYouWantIt\OSCQB00_Data.MDF',
move 'OSCQB00_Log' TO ''C:\WhereverYouWantIt\OSCQB00_Log.LDF'

Tara

Finarfin
Starting Member

28 Posts

Posted - 2004-05-28 : 06:30:57

Thank you very much,
But I was asking myself: why do SQL need to create those two files? What are they used for?

Romain

Thank you all

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-28 : 06:42:12

The MDF is the datafile, the LDF is the logfile...

Finarfin
Starting Member

28 Posts

Posted - 2004-05-28 : 09:15:51

Thank you, but it is not very explicit like answer. Doesnt the .bak file contains informations that compose .LDF and .MDF files? And what is the purpose of that 2 files?

Thank you all

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-28 : 09:22:57

Those two files are the logical filenames the backup was created from, the restore will try to run back to these files unless the WITH MOVE syntax is used, otherwise the .BAK file wouldn't know where to restore to...

Hope this helps...

mr_mist
Grunnio

1870 Posts

Posted - 2004-05-28 : 09:55:19

quote:

Thank you, but it is not very explicit like answer. Doesnt the .bak file contains informations that compose .LDF and .MDF files? And what is the purpose of that 2 files?

The LDF is logs of transactions. If you like, you can think of it as the stuff that hasn't quite happened yet. The MDF is your data, it's the tables and everything else. They are seperate because they provide two distinct functions.

For the other poster, you should definitely split your IIS and SQL boxes. Running the two together will be a recipe for disaster later on in performance, scalability and security terms.

-------
Moo. :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources