I am using SQL Server Management Studio 12 to try to restore a backup to a new database.  I know I have done this before, but cannot seem to make it happen now.  Her is what I have.

Backup file is "C:\Temp\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak"  and it is for sure there.  This came from a customer of mine.

I launch SQL Server Management Studio 12 and log in as SA.  I right click on Databases and chose Restore Database.  I choose Device then file then Add and try to browse the the named location. 

When I get there, regardless if I choose All files or Bak, I see no files.

I tried to upload an image of the "Upload Backup File" dialog, but MSDN would let it happen.  When I get to the c:\temp folder, no files appear in the window.

What am I doing wrong?  Help would be much appreciated.

Why have you even kept any .bak files in System folder and drives. Specially in C:\Windows\ please move the backup file from C drive to some non system drive and then try restoring your account probably wont have access on system drive

Thanks for your response.  Not sure I get your response.  This is a standalone computer, it has a local copy of sql server running on it.  All drives are really the "system" drive.  (i.e. mapped drives.)  There is no network server. 

After further perusal,I do have a partition on the drive that contains enough space to place the backup file.  I don't understand why I cannot get it from c:\temp, is that a limitation of SQL Server Management Studio 12?

At any rate, I browsed to that location, chose the file and typed in the name of the new database (test)  that I wanted to restore to.  It failed.  I then created test and tried to restore to it from the backup file, but it failed again. 

How can I restore "Q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak to a database named test?

First close SSMS and now locate SSMS again right click on it and select run as administrator.

The account you are using to log on to windows machine, does this account had complete privilege on Q drive ?

So again even after moving backup file to Q drive while locating it via SSMS you are not able to see your backup file correct ?

Ok why not restore it using TSQL

use master
go
alter database db_name set single_user with rollback immediate
go
restore database db_name from disk='Q\location.bak' with recovery--replace name and location 

Shanky,

Again, thanks for your assistance.  No, once I moved the bak file to Q:\, SMSS allows me to see it and choose it.  I then type in the name test for the database thinking that would create a new database since test doesn't exist.  When it ran, it said cannot restore to test.  So, then I created a database named Test and tried again.  Same result.

I am now trying to do it with your TSQL example.  Here is my query:

use master
go
alter database test set single_user with rollback immediate
go
restore database test from disk='Q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak' with recovery--replace name and location

I ran debug and it doesn't seem to complete, tab says Debugging ...*

Should I replace the alter database test . . . with Create database Test . . .

• Edited by tkosel 13 hours 17 minutes ago

Change last words to be 'WITH REPLACE'. You don't need to debug, just try running as is - if you get an error, just post it.

Check this (the first blog post I found on the topic):

https://www.mssqltips.com/sqlservertutorial/121/restore-sql-server-database-and-overwrite-existing-database/

Again,  Thanks for the information

my query

use master
go
alter database test set single_user with rollback immediate
go
restore database test from disk='Q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak' with replace

Errors

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\InProcessInspection_DataSQL.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_dat' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\InProcessInspection_DataSQL.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\InProcessInspection_DataSQL.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\InProcessInspection_DataSQL.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Ok, here is what you need - check this post

http://forum.lessthandot.com/viewtopic.php?f=22&t=8401&sid=97009eb0f54479e53de02471f226f4f1

suggestion by Denis Gobo in the middle of the topic. I use this technique all the time when I am in the same situation.

You need to use the MOVE option for the restore command. First use RESTORE FILELISTONLY to investigate the database files used by the database. Then use the MOVE option of the RESTORE command to spcify what physical filename you want each of those files to have.

I have mentioned this brieflyin http://www.karaszi.com/sqlserver/info_moving_database.asp , in the section named "How to do the actual move".

That exactly what I suggested using the link I posted.

I though so, Naomi. But there were 9 posts by Denis in that thread, and when I read it, I had troubles finding what you referred to. And I know that I'm looking for. So, I thought that an additional post, an extra option for the OP, wouldn't hurt.

Yes, good point. The post I referred to is this one:

Yes and I had something like that that would move the files for me since it was just doing a replace on drive letter and directory

script was very similar to the one I posted here

here this will generate the moves for you

found it  

type this into a window
TSQLLINE NUMBER OFF | HIDE | SELECT ALL

1. RESTORE DATABASE [testabc] FROM  DISK = N'D:\Backup.BAK'
2. WITH  FILE = 1,  

paste the output of this below the code above but above the code after the query
run the query below on the server that you want to do the restore at
TSQLLINE NUMBER OFF | HIDE | SELECT ALL

1. SELECT 'MOVE N''' + RTRIM(name) + ''' TO N''' +RTRIM(filename)  +''','
2. FROM master..sysaltfiles
3. WHERE dbid =DB_ID()

type this
TSQLLINE NUMBER OFF | HIDE | SELECT ALL

1.  NOUNLOAD,  REPLACE,  STATS = 10
2. GO
3.  

then your whole statement will look like this
TSQLLINE NUMBER OFF | HIDE | SELECT ALL

1. RESTORE DATABASE [testabc] FROM  DISK = N'D:\Backup.BAK'
2. WITH  FILE = 1,  
3.  
4. MOVE N'testabc' TO N'f:\MSSQL\data\testabc.mdf',
5. MOVE N'testabc_log' TO N'f:\MSSQL\data\testabc_log.LDF',
6. MOVE N'Testdata2' TO N'f:\mssql\Testdata2.NDF',
7.  
8.   NOUNLOAD,  REPLACE,  STATS = 10

Naomi and others,

Thanks for your suggestions, but I cannot garner enough information from the posts to get it to make sense.  Perhaps it is because I am relatively inexperienced with SQL server.

Here is what I have.

I have a backup file named  "q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak".  It was generated on a customer site server and transferred to me.  I have a local install of SQL Server 12.  I already have a functional database named "InProcessInspectionDataSQL" on my local machine which is the name of the database that the backup was from, that I do not want to affect, so I would like to restore this data to a brand new database with a different name.

I thought I could do it using the GUI interface in SSMS, but couldn't make that work.  A previous post suggest sql Query Script  to do it, but that didn't work either.  Several other posts indicated various sources of information with which to make it work, but I cannot structure it sufficiently to make that work.

Can someone post the proper sql script such that I can make it work?

We can't because what you need to execute in the end depends on the output from RESTORE FILELISTONLY, the desired database name for this rested database and also what folder you want the database files to be created in . But as a first step, execute below:

RESTORE HEADERONLY FROM DISK = 'q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak'

Now please provide us the name you want for the rested database, the location for the database files (path) and the result from above (the logical filenames are enough).

Also, never use debug in a query window. Use the Execute button. And if something fails, please copy and paste the exact and full error messages.

First execute the below statement:

RESTORE FILELISTONLY FROM DISK='q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak'


if for example the logical names are:
>LogicalName
>--------------
>InProcessInspection
>InProcessInspection_log


then replace the logical names as follows:


RESTORE DATABASE MyNewDB FROM DISK='q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak'
WITH
   MOVE 'InProcessInspection'  TO 'c:\mssql\MyNewDB.mdf',
   MOVE 'InProcessInspection_log' TO 'c:\mssql\MyNewDB_log.ldf'

If your existing database is OK, just as the post I quoted suggests, you can run this command:

SELECT 'MOVE N''' + RTRIM(name) + ''' TO N''' +RTRIM(filename)  +''','
FROM master..sysaltfiles
WHERE dbid =DB_ID()

after you selected the database which is your current database you want to keep. Also, prior to running this script chose Results to Text from the Query menu.

The output of this select statement should be the middle part of your RESTORE command. Check that post I quoted one more time, this is exactly what I do in the situation like yours. I don't memorize this script, that's why I always open that particular topic when I am in need.

Thanks for your input.

The name of the  Rested (restored) database can be "TEST".

When you say the location for the database files (path) do you mean the name of the backup database file?  If so, it is "q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak"

I didn't exactly know what you mean by the logical filenames in the output, but I saved the output as a CSV file and pasted it in below.

BackupName,BackupDescription,BackupType,ExpirationDate,Compressed,Position,DeviceType,UserName,ServerName,DatabaseName,DatabaseVersion,DatabaseCreationDate,BackupSize,FirstLSN,LastLSN,CheckpointLSN,DatabaseBackupLSN,BackupStartDate,BackupFinishDate,SortOrder,CodePage,UnicodeLocaleId,UnicodeComparisonStyle,CompatibilityLevel,SoftwareVendorId,SoftwareVersionMajor,SoftwareVersionMinor,SoftwareVersionBuild,MachineName,Flags,BindingID,RecoveryForkID,Collation,FamilyGUID,HasBulkLoggedData,IsSnapshot,IsReadOnly,IsSingleUser,HasBackupChecksums,IsDamaged,BeginsLogChain,HasIncompleteMetaData,IsForceOffline,IsCopyOnly,FirstRecoveryForkID,ForkPointLSN,RecoveryModel,DifferentialBaseLSN,DifferentialBaseGUID,BackupTypeDescription,BackupSetGUID,CompressedBackupSize,Containment
InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393,NULL,1,01:33.0,0,1,2,CRETEX\rmssqlspc,RMS-SQLSPC,InProcessInspection_DataSQL,661,41:37.0,4590761984,4.1024E+19,4.1024E+19,4.1024E+19,4.0784E+19,01:33.0,02:20.0,52,0,1033,196609,90,4608,10,50,4000,RMS-SQLSPC,512,CFE394F2-0621-46B5-9D3E-99B20FBD37E1,7E28D9CB-AD86-4A74-9669-2B063E158FD3,SQL_Latin1_General_CP1_CI_AS,6B37DCAE-DA7F-4E47-B8E6-C4AFCE535E06,0,0,0,0,0,0,0,0,0,0,7E28D9CB-AD86-4A74-9669-2B063E158FD3,NULL,SIMPLE,NULL,NULL,Database,898A8CFC-1D44-412A-A47E-F6618DAB95B7,4590761984,0

Last question here is if you shold never use debug, why do they provide it?

Berimi,

Thanks for you input, I think I am learning and making progress.

I got this output from your sql

InProcessInspection_DataSQL_dat
InProcessInspection_DataSQL_log

Here is my sql

RESTORE DATABASE TEST FROM DISK='q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak'
WITH
   MOVE 'InProcessInspection_DataSQL_Dat'  TO 'c:\mssql\Test.mdf',
   MOVE 'InProcessInspection_DataSQL_log' TO 'c:\mssql\Test_log.ldf'

Here is the errors

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\mssql\Test.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_dat' cannot be restored to 'c:\mssql\Test.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\mssql\Test_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_log' cannot be restored to 'c:\mssql\Test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Naomi,

I don't have an existing DB, want to make a new one.

Berimi,

Thanks for you input, I think I am learning and making progress.

I got this output from your sql

InProcessInspection_DataSQL_dat
InProcessInspection_DataSQL_log

Here is my sql

RESTORE DATABASE TEST FROM DISK='q:\InProcessInspection_DataSQL_backup_2015_02_23_000012_4551393.bak'
WITH
   MOVE 'InProcessInspection_DataSQL_Dat'  TO 'c:\mssql\Test.mdf',
   MOVE 'InProcessInspection_DataSQL_log' TO 'c:\mssql\Test_log.ldf'

Here is the errors

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\mssql\Test.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_dat' cannot be restored to 'c:\mssql\Test.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "c:\mssql\Test_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'InProcessInspection_DataSQL_log' cannot be restored to 'c:\mssql\Test_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Have you created the folder "mssql" in c drive?

You can change it by any folder.

Berimi,

Thanks for the hint.  I have changed my script to reflect the location I wanted the files to be placed and it worked fine.  There were lots of people who contributed to my solution, and I will certainly give them credit when I have time.  Thanks to all.

Berimi,

Thanks for your input, however I am quite not there yet.  I am able to see my newly created "TEST" database now and access the tables  I want to see using using SSMS , but now I want to create an ODBC data source to connect to it to use Access to manipulate the data but unable to do so.  When I try to create the DSN, I get the following error.

Can you help with this, thanks.

Looks like you didn't correctly specify the server's name. 

Naomi,

Thanks for your attention.  I did specify the servers name. SSMS object explorer window snipped below.

Creation of ODBC process snips listed below.

Am I close or not?