SQLTeam.com | Weblogs | Forums

Sql script to restore .bak file

#1

I have an act! installed. I have a zip file in which I have a backup file(.bak)
I need to write a script to restore data from .bak to .adf and .alf
I need to restore data to sql server. Need to write the script.

First of all I tried to write a sql script to restore data. But I am not able to restore data using that script.
I have a zip file in which I have the .bak file
So script that I have written is
RESTORE DATABASE demoDatabse FROM DISK='"C:\actdatabases\Backup\database.bak"

But using this script I am only able to restore data that has .mfd extension not adf extension

When I try to restore the database inside the zip file I get this exception.

{"Directory lookup for the file "V:\ACT\ARAMaster.ADF" failed with the operating system error 3(The system cannot find the path specified.).\r\nFile 'ACT_DATA' cannot be restored to 'V:\ACT\ARAMaster.ADF'. Use WITH MOVE to identify a valid location for the file.\r\nDirectory lookup for the file "V:\ACT\ARAMaster.ALF" failed with the operating system error 3(The system cannot find the path specified.).\r\nFile 'ACT_LOG' cannot be restored to 'V:\ACT\ARAMaster.ALF'. Use WITH MOVE to identify a valid location for the file.\r\nProblems were identified while planning for the RESTORE statement. Previous messages provide details.\r\nRESTORE DATABASE is terminating abnormally."}

Dont know ehy this issue is coming and I don't have any dirive as V drive.

Please help me with this. I really need to figure this out.

Also I tried to folow the steps mentioned on http://kb.swiftpage.com/app/answers/detail/a_id/27607

So I wrote the following command to Restore As SQL .BAK backup file
ACTDiag RestoreAs act2011demoNew C:\actdatabases\Backup\database.bak c:\ActDatabases
But again the exception is coming.

Need Help!
Thanks

#2

I think you might want to post your question in an Act forum or contact the vendor directly

#3

The error is actually telling you what to do ---> Use WITH MOVE

Best action would probably be, contacting the vendor, as @gbritton suggested.

#4

Here is my sample script which covers all the options I normally use:

First get details of the Logical Names within the database and where the original files were backed up to (Path and Filename etc.) in case that is helpful in decided what-goes-where during the restore

RESTORE FILELISTONLY FROM DISK
	= 'x:\Mypath\MyBackupFilename_Full.BAK'

RESTORE HEADERONLY FROM DISK 
	= 'x:\Mypath\MyBackupFilename_Full.BAK'

Then make the actual restore using the script below. Be VERY careful not to unintentionally overwrite an existing database!! Use Find & Replace to change names such as "MyDatabaseName" globally in this script:

USE master	-- (Can't sit in the database whilst its being Restored / Dropped!)
GO

-- ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

-- Restore Full Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyBackupFilename_Full.BAK'
	WITH
		REPLACE,
--		RECOVERY,	-- Use if NO more T/Logs to recover
		NORECOVERY,	-- Use if 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

-- Optional restore Differential Backup
RESTORE DATABASE MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_Diff.BAK'
	WITH
--		RECOVERY	-- Use if NO more file to recover
		NORECOVERY	-- Use if there are T/Logs to recover
GO

-- Optional restore Transaction Log Backup
RESTORE LOG MyDatabaseName
	FROM DISK = 'x:\Mypath\MyDatabaseName_yyyymmdd_hhmm_Trans.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover
		NORECOVERY	-- Use if more T/Logs to recover
GO
-- REPEAT FOR EACH Transaction Log Backup file - in chronoloical order

-- Now activate the database (NOT required if RECOVERY was used earlier)
RESTORE DATABASE MyDatabaseName WITH RECOVERY
GO

-- Rename logical names (only if restoring from a backup for a Different database)
ALTER DATABASE MyDatabaseName 
	MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabaseName_data')
GO
ALTER DATABASE MyDatabaseName 
	MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabaseName_log')
GO

-- Optional - if backup-file was from a database set to SINGLE_USER or READ_ONLY
ALTER DATABASE MyDatabaseName SET MULTI_USER, READ_WRITE
GO