RESTORE DATABASE

I need to restore a database. I have never done it before. I get an error on this command: Syntax Error.

What is wrong with it?

RESTORE DATABASE [intraDay] FROM DISK = 'J:\SqlServerBackUps\IntraDay\intraDayFullRM.BAK' WITH RECOVERY,

TO 'C:\intraDay.mdf'; -- this is just to try the RESTORE in principle. I will restore the DB to a different folder.

GO

Thanks.

If the database is being newly created from a backup file proceed as shown below,

RESTORE DATABASE dbname FROM DISK='Path of your backup file'

If the databasealready existing and you are going to overwrite it then proceed as shown below,

RESTORE DATABASE dbname
FROM DISK = 'path of backup file'
WITH MOVE 'logical filename' TO 'path.mdf',
MOVE 'logical filename' TO 'path.ldf'

by default it will be in recovery only unless you explicitly specify it as norecovery

hope this helps

|||

Deepak Rangarajan wrote:

If the database is being newly created from a backup file proceed as shown below,

RESTORE DATABASE dbname FROM DISK='Path of your backup file'

If the databasealready existing and you are going to overwrite it then proceed as shown below,

RESTORE DATABASE dbname
FROM DISK = 'path of backup file'
WITH MOVE 'logical filename' TO 'path.mdf',
MOVE 'logical filename' TO 'path.ldf'

by default it will be in recovery only unless you explicitly specify it as norecovery

hope this helps

Thank you,

There is more confusion than I can handle. It is a situation when the database is restored to the folder where it does not exist. Secondly, I just installed Vista OS and the backups were all done from my XP partition. I copied all my databases except this one which appears to be screwed up. The mdf file cannot be found on XP although I can view all tables and the database itself in Server Explorer in SSMS in XP. Also for your info it is SQLEXPRESS. I posted this question over there but nobody seems to be able to handle it.

OK, now it this: I do not have logical name. When I tried recovery with a similar command I got an error that ... I do not quite remember the format but the content was that I should run RESTORE FILESLISTONLY to get logical names? Does it sound right?

I did this and got a table with some of the ndf's and one mdf file but the mdf is not this database name. It seems to be a name of database that I created months ago but then deleted.

Also I tried to RESTORE from SSMS (Sql Express) by pointing to Databases in Server Explorer and rightCLick==>Restore.

The darn thing is with this security obsessed Vista I cannot find a way to cancel this persistent error that "Access is not allowed" when I point later to the backup device.

I am the administrator everywhere. I gave permission to a bunch of user groups (for the .BAK file, etc.) but it all seems to be in vain. It is just a horror story with this Vista.

I guess my question is: how can I get the logical name. You see, I can read the stuff you showed me in MSDN help myself but I need real life examples to implement them. I do not seem to have them. The examples they give do not seem to fit my particular situation.

Even if you explain to me how to get the logical name for this DB most likely it will be wasted since the Vista (or SSMS) again will say that "Access is not allowed.." This is a comprehensive run around.

I am the only user of this workstation with low security DBs used primarily for crunching floating point numbers and I am facing such a hard time doing simple things. It is preposterous.

Help me if anybody can.

Thanks.

|||1 - Try executing SSMS as the administrator account. You know, right click - Run as

administrator, then try to do the restore from SSMS

2 - If point 1 did not work, then execute first:

restore headeronly

from disk = ...

This will give you the list of backup sets in that device, the type, etc. Choose the backup set you are interested in (position), let us say 3, then execute:

restore filelistonly

from disk = ...

with file = 3

to see the list (logical and physical) files in that backup set, then apply the "restore database" and use the logical names with the option "move" to move the physical file names to a specific folder.

Let us know how it goes.

AMB

|||

hunchback wrote:

1 - Try executing SSMS as the administrator account. You know, right click - Run as

administrator, then try to do the restore from SSMS

2 - If point 1 did not work, then execute first:

restore headeronly

from disk = ...

This will give you the list of backup sets in that device, the type, etc. Choose the backup set you are interested in (position), let us say 3, then execute:

restore filelistonly

from disk = ...

with file = 3

to see the list (logical and physical) files in that backup set, then apply the "restore database" and use the logical names with the option "move" to move the physical file names to a specific folder.

Let us know how it goes.

AMB

Thank you very much, I appreciate this.

Running as Administrator (which curiously I've done before a number of times, although not with SSMS) resulted in this:

TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'DDMNVT61'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Stoxx\intraDay.mdf'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

BUTTONS:

OK

IN other words it is the same stuff.

I want to make sure that I am not creating a wrong impression about Vista. In a week or so with it I simply fell in love with it and will never go back to XP. It is a wonderful OS in many respects. I was forced to installl it when I lost my Internet conn in XP and could not restore it in a week. I had all my dev work in there. Installation was a piece of cake. My network showed up in a blink as before with XP and 2K I had to spend hours troubleshooting it and frequently would just give up. But this security stuff is just too much. It is an overkill. They overdid it.

I actually do have a workaround in mind. What I need is just my stored procedures. I have never done restore before and now I am beginning to realize that the SP's will not be resurrected. Right?

I am planning to go back to XP, copy all my SP's and reCREATE then in here (in Vista's SSMS). I actually can live without the data but cannot live without the tables since a number of my apps are trying to dump some records in them and the DB is not there. I get exceptions. My stored procedures automatically create the tables if they are not found and also create other stored procedures to insert and update records. It has been working for a long time.

It is the least of my worries.

What I really need now after I comprehended the threat: I need to understand how I can do RESTORE in case when I really need it. You guys should take care of it please. It is just scary.

I will try the second method and will report back.

Please, find out how those messages could be cancelled. It may be a bug. I do not know. It is not the first time I had to struggle with them. Somehow I could work around them when I copied my folders to Vista and could not attach DBs to SSMS for the same reason. It took two days to find out. Other people helped but this situation is different.

Thanks.

|||

hunchback wrote:

2 - If point 1 did not work, then execute first:

restore headeronly

from disk = ...

This will give you the list of backup sets in that device, the type, etc. Choose the backup set you are interested in (position), let us say 3, then execute:

restore filelistonly

from disk = ...

with file = 3

to see the list (logical and physical) files in that backup set, then apply the "restore database" and use the logical names with the option "move" to move the physical file names to a specific folder.

Let us know how it goes.

AMB

OK, again I appreciate your help but it did not work either. In this case I have no idea whose fault it is, most lilkely, mine. When I first set this DB up (about a year ago) I just began working on the project and did not know what I was doing. I created a DB which logically did not make sense and I destroyed it. There is a chance I simply deleted it from the folder, It is hard to recall.

What I got after executing your second suggestion (and in my first post I said that I did it already): I got this DB in position #1. Why it is there I have no idea. It is not in the folder in XP, it is not in SSMS in XP anymore but it is still in this BAK file!

Besides I got three other.ndf files (tables) perhaps from that deleted DB (do not remember). They are also useless to me.

The last file is the LOG (intraDay_log) file for the DB in question (the one I am after). That't it.

When I open the BAK file in the RESTORE wizard after you point to the backup device, etc, I do see all my backups from Jan 2007 on. I figured I could have restored one of the latest but it does not seem to be the case: Access is not allowed.

I do not know maybe this intraDay_log fiile is what you called the logical name (device), so I did this:

RESTORE FILELISTONLY FROM DISK = 'J:\SqlServerBackUps\IntraDay\intraDayFullRM.BAK'

WITH FILE = 5

GO

It was in the resulting table when I executed the query. It was in the position #5 all right. AfterI executed the last query I got the same table but no DB was restored as far as I could tell. I did refresh for it to show up but it does not seem to exist.

Many thanks.

Any other ideas will be appreciated. nonetheless.

|||

It turns out I cannot even CREATE a new DB in SSMS. I get this error:

TITLE: Microsoft SQL Server Management Studio Express

Create failed for Database 'intraDay'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Stoxx\intraDay.mdf'. (Microsoft SQL Server, Error: 1802)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1802&LinkId=20476

BUTTONS:

OK

What the hell is it?

I ran as Administrator, set up permissions in the folder for myself and administrator who is me. WHAT ELSE DO YOU WANT?

|||

Hi Alex,

Did you already applied Service Pack 2?

If not, please do it.

AMB

|||

hunchback wrote:

Hi Alex,

Did you already applied Service Pack 2?

If not, please do it.

AMB

I think I did, otherwise it would have reminded me many times. How can I find out for sure? It is not in Control Panel anywhere.

Anyhow, the way I finally did it is as follows:

I went to XP, created a blank DB intraDay2, copied .mdf and .ldf files to the Sql Server Data folder and attached this DB, again after some additional trouble to SSMS. Then I renamed the DB to intraDay and copied all my SP's to queries and reCREATEd them. It seems to work now. My apps do not protest although not the full range of options have nee exploited yet. I will see it later today.

Isn't it all weird? It is just absurd to the highest degree.

I do have another question for you, somewhat related.

After I attached the intraDay2 DB to the SSMS and changed the DB name to intraDay I found one thing I did not like:

The files themselved remained unchanged: intraDay2.mdf and intraDay2_log.ldf. I am afraid to change them for safety reason but if you have any idea, tell me if it could be done.

Thanks.

|||

Alex,

To change the db name and logical files name, you can use "alter database". To change the physical files name, I usually use "restore database", but you can use also "alter database" and then stop and restart the instance of SS.

Code Snippet

use master

go

backup database northwind

to disk = 'c:\temp\northwind.bak'

go

-- to see logical files name

restore filelistonly

from disk = 'c:\temp\northwind.bak'

go

restore database Northwind_1

from disk = 'c:\temp\northwind.bak'

with

move 'Northwind' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Northwind_1.mdf',

move 'Northwind_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Northwind_1_log.mdf'

go

alter database Northwind_1

modify file(name=Northwind, newname=Northwind_1)

go

alter database Northwind_1

modify file(name=Northwind_log, newname=Northwind_1_log)

go

exec sp_helpdb 'Northwind_1'

go

drop database Northwind_1

go

AMB|||

hunchback wrote:

Hi Alex,

Did you already applied Service Pack 2?

If not, please do it.

AMB

Hi hunchback,

Thanks for help. How can I doublecheck that I did install SP2. I am pretty certain I did but I want to have a definitive proof. It was a very hectic time when I did all the installations. Something could have gone wrong and I do not want to do an hour long download to find out later that it is already there.

Thanks.

|||

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

|||

hunchback wrote:

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

Mine is 9.0.3042. That makes it SP2, I guess.

So, does it exhaust all options for answering my excessive/obsessive security concerns by Vista?

Is there any other place I could appeal?

To the best of your knowledge, how many other people have similar problems?

I've read a thread when a person had to give up on Vista. Sql Server Enterprise Edition, I think, was involved but that was networking access.

Any other cues?

Thanks.

|||

Sorry, I am new to Vista also. BTW, SS 2005 Enterprise Edition can not be installed on Vista.

Hardware and Software Requirements for Installing SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms143506.aspx

AMB

|||

hunchback wrote:

Sorry, I am new to Vista also. BTW, SS 2005 Enterprise Edition can not be installed on Vista.

Hardware and Software Requirements for Installing SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms143506.aspx

AMB

Mine is Sql Express with Advanced Features, I mentioned this in the first post, I believe.