Restoring DB with a back up file

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

SQL Server Administration (2000)

Restoring DB with a back up file

Author

Topic

gk70
Starting Member

11 Posts

Posted - 2004-10-19 : 05:12:06

Hi all,
I had used Tara Duggan's script for creating a backup of a DB(with some modifications).So how do I restore the same
Regards
Girish

CanadaDBA

583 Posts

Posted - 2004-10-19 : 10:05:18

As long as you have a BAK file, you can restore it with RESTORE command. Here is a sample:


USE Master
GO

DECLARE @BakFile VarChar(255)

SET @BakFile = 'E:\MSSQL\Backups\MyDB_db_200408032045.BAK'
 
RESTORE FILELISTONLY FROM DISK = @BakFile

RESTORE DATABASE MyDB
   FROM DISK = @BakFile
   WITH MOVE 'MyDB_dat' TO 'C:\MSSQL\Data\MyDB.MDF',
        MOVE 'MyDB_log' TO 'C:\MSSQL\Data\MyDB.LDF'

Canada DBA

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 14:27:05

You might need a little bit more detail to do a "normal circumstances" restore:


RESTORE DATABASE MyDatabaseName
	FROM DISK = 'D:\MSSQL\BACKUP\MyDatabaseName_yymmd_hhmm.BAK'
	WITH
		REPLACE,
--		NORECOVERY,	-- Use if more T/Logs to recover
		RECOVERY,	-- Use if no more T/Logs to recover
		STATS = 10,	--Show progress (every 10%)
	MOVE 'MyDatabaseName_data' TO 'x:\MSSQL\DATA\MyDatabaseName.mdf', 
	MOVE 'MyDatabaseName_log' TO 'x:\MSSQL\DATA\MyDatabaseName.ldf'
GO

Kristen

gk70
Starting Member

11 Posts

Posted - 2004-10-20 : 10:26:57

Thanx toall !
But in BOL they have the 'l0gical file name' to 'operating system file_name' for MOVE command.
I don't under stand the logical filename concept here as I have the path of backup file as 'C:\..\.bak'
Can any of you clarify?

gk70
Starting Member

11 Posts

Posted - 2004-10-20 : 10:36:46

Iam getting the following error:
Server: Msg 3201, Level 16, State 2, Line 6
Cannot open backup device 'C:\AnishTest\Test_09.07.2004.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE FILELIST is terminating abnormally.
Server: Msg 3201, Level 16, State 2, Line 8
Cannot open backup device 'C:\AnishTest\Test_09.07.2004.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
When I use EM ,Iam able to restore fromthe same .bak file
regards
Girish

CanadaDBA

583 Posts

Posted - 2004-10-20 : 10:43:15

Post your command that you use in QA.

quote:
Originally posted by gk70

Iam getting the following error:
Server: Msg 3201, Level 16, State 2, Line 6
Cannot open backup device 'C:\AnishTest\Test_09.07.2004.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE FILELIST is terminating abnormally.
Server: Msg 3201, Level 16, State 2, Line 8
Cannot open backup device 'C:\AnishTest\Test_09.07.2004.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
When I use EM ,Iam able to restore fromthe same .bak file
regards
Girish

Canada DBA

gk70
Starting Member

11 Posts

Posted - 2004-10-20 : 10:48:29

Hi Farhadr,

USE Master
GO

DECLARE @BakFile VarChar(255)

SET @BakFile = 'C:\AnishTest\Test_09.07.2004.bak'

RESTORE FILELISTONLY FROM DISK = @BakFile

RESTORE DATABASE Test
FROM DISK = @BakFile
WITH MOVE 'Test_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Data.mdf',
MOVE 'Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Log.ldf'

CanadaDBA

583 Posts

Posted - 2004-10-20 : 11:01:20

I see even RESTORE FILELIST is terminated abnormally. Probably you have a problem with file name or path.
Rename your BAK file as Test_09_07_2004.bak and try again.

quote:
Originally posted by gk70

Hi Farhadr,

USE Master
GO

DECLARE @BakFile VarChar(255)

SET @BakFile = 'C:\AnishTest\Test_09.07.2004.bak'

RESTORE FILELISTONLY FROM DISK = @BakFile

RESTORE DATABASE Test
FROM DISK = @BakFile
WITH MOVE 'Test_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Data.mdf',
MOVE 'Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Log.ldf'

Canada DBA

gk70
Starting Member

11 Posts

Posted - 2004-10-20 : 11:14:43

Same error again pops up! But I don't understand the MOVE command stuff as I had earlier commemented in oneof my posts

CanadaDBA

583 Posts

Posted - 2004-10-20 : 11:43:09

Try the following to find out if this part is working.


USE Master
GO
RESTORE FILELISTONLY FROM DISK = 'C:\AnishTest\Test_09_07_2004.bak'
GO

But before running the code, is the C:\AnishTest on the same place where SQL-Server is running? I mean this file should be where the SQL-Server is installed.

Canada DBA

gk70
Starting Member

11 Posts

Posted - 2004-10-20 : 11:48:26

Yes Iam trying to restore it in same machine,The above code throws up therror
Server: Msg 3201, Level 16, State 2, Line 6
Cannot open backup device 'C:\AnishTest\Test_09.07.2004.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE FILELIST is terminating abnormally.

CanadaDBA

583 Posts

Posted - 2004-10-20 : 11:56:39

I am not comfort with 'Test_09.07.2004.bak' change it to Test_09_07_2004.bak and try again.
Double check, if your QA is pointing to same machine?
Were you able to restore the file in EM?

Canada DBA

sphadke
Yak Posting Veteran

55 Posts

Posted - 2004-10-20 : 13:56:46

If you were able to restore the db using EM try running a trace to capture the restore t-sql using profiler and compare it with your t-sql command

Sachin

gk70
Starting Member

11 Posts

Posted - 2004-10-21 : 03:07:40

Sachin,
Comparison done and I justcopied from msdb..backupmediafamily tbl the physical_device_name(which was last backed uplast) and I was able to run RESTORE FILELISTONLY successfully,indicating that file name should be like this N'C:\AnishTest\Test_07_10_2004 .BAK'.
Earlier I tried to change filename,little realising that that is not
reflected in msdb..backupmediafamily (for that I correcetd bckup sp)
That was the problem
Thanx for the SQLProfiler input
Regards
Girish

CanadaDBA

583 Posts

Posted - 2004-10-21 : 08:18:24

DO you mean you have used the command as:


RESTORE FILELISTONLY FROM DISK = N'C:\AnishTest\Test_09_07_2004.bak'
GO

and it worked?

quote:
Originally posted by gk70

Sachin,
Comparison done and I justcopied from msdb..backupmediafamily tbl the physical_device_name(which was last backed uplast) and I was able to run RESTORE FILELISTONLY successfully,indicating that file name should be like this N'C:\AnishTest\Test_07_10_2004 .BAK'.
Earlier I tried to change filename,little realising that that is not
reflected in msdb..backupmediafamily (for that I correcetd bckup sp)
That was the problem
Thanx for the SQLProfiler input
Regards
Girish

Canada DBA

gk70
Starting Member

11 Posts

Posted - 2004-10-25 : 04:26:52

Hi Farhadr,
yes as u said filename has something todo , along with the fact that in msdb..backupmediafamily tbl,the filename stored id 'c"\Myfolder\bckupfile. BAK'.Iam sure that spaces before fileextn are required .When u do restore using EM,I suspect that msdb..backupmediafamily has a role to play.hence stick to what is stored in this tbl as it is ie retain the spaces as stored in tbl,even though Iam not able to explain why it is so.
Thanx for the help
Regards
girish

gk70
Starting Member

11 Posts

Posted - 2004-10-27 : 07:42:38

Hi All,
Referring to tha above problem ,If I hardcode the file name (Iam using an Instance of SQLDMO'S Restore Interface)<i am able to restore.
But when I take the filename from where user has selected valid one,it says' cannot open backup device....'
The code is as below;
Dim SQLDMOOBJ As New SQLDMO.SQLServer()
Dim trhas As Threading.Thread
Dim restore As New SQLDMO.Restore()
Dim pro As System.Diagnostics.Process = New Process()
Dim path As System.IO.Path

Dim test,restfile As String
'Getting the file name
restfile= txtfilename.text
'Connect as sa required
SQLDMOOBJ.Connect("(local)", "sa", "")
'Stop the service temporarily
pro.StartInfo.FileName = "cmd.exe"
pro.StartInfo.Arguments = "/C net stop mssqlserver"

pro.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
pro.StartInfo.CreateNoWindow = True
pro.Start()
'Provide a delay before setting restoring parameters
trhas.Sleep(10000)
restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database
restore.Database = "Test"
'secondpart = restfile.Substring(restfile.IndexOf("."))
firstpart = restfile.Substring(0, restfile.IndexOf(".")) + Space(4) + restfile.Substring(restfile.IndexOf("."))
'MsgBox(firstpart)
'test = "[" & restfile & "]"
test = path.GetFullPath(firstpart)

restore.Files = path.GetFullPath(firstpart)
restore.ReplaceDatabase = True
'Restart DB

pro.StartInfo.FileName = "cmd.exe"
pro.StartInfo.Arguments = "/C net start mssqlserver"
pro.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
pro.StartInfo.CreateNoWindow = True
pro.Start()
'System.Diagnostics.Process.Start("CMD.exe", "/C net start mssqlserver")
'Start restoration
trhas.Sleep(10000)
'pro.WaitForExit(10000)
restore.SQLRestore(SQLDMOOBJ)<-- ERROR-- "cannot open device name"

I tried setting files property of SQLDMO's restore instance with
test = "[" & restfile & "]" to account for space in the .bak file.
This is as see msdb..backupmediafamily table
Pls help
regards
Girish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources