March 9, 2014 at 12:55 pm
I made a script to restore .bak or backup files. It works for some databases, but not for one. How do I make it work for any type of .bak file ? This is in sql server 2008. The error message is -
Msg 3234, Level 16, State 1, Line 1
Logical file 'Northwind_Data' is not part of database 'Northwind'.
Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Script -
IF DB_ID('Northwind') IS NULL
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data'
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH FILE = 1,
MOVE N'Northwind_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
MOVE N'Northwind_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF',
NOUNLOAD, STATS = 10
END
March 10, 2014 at 6:37 am
At first glance I think you've added an extra line that doesn't belong there:
IF DB_ID('Northwind') IS NULL
BEGIN
RESTORE DATABASE [Northwind]
FILE = N'Northwind_Data' -- this line needs to be removed
FROM
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\Backup\Northwind.bak'
WITH FILE = 1,
MOVE N'Northwind_Data'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind.mdf',
MOVE N'Northwind_Log'
TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SS2008\MSSQL\DATA\Northwind_0.LDF',
NOUNLOAD, STATS = 10
END
December 28, 2016 at 5:06 pm
Hello all;
SQL pseudo noob here...running into same issue, but only 2008R2. We have a canned script that's been around for years; so in short, DB X is the primary, DB Y is for training/dev/testing. I perform a backup of X, restore as Y and get the error. So, where am I wrong?
*/
set @src_db = 'Eatx'
set @dest_db = 'Eaty'
set @bak_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatxTemp.bak'
set @data_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty.mdf'
set @log_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty_log.ldf'
TIA!
December 28, 2016 at 5:35 pm
Mystic1287 (12/28/2016)
Hello all;SQL pseudo noob here...running into same issue, but only 2008R2. We have a canned script that's been around for years; so in short, DB X is the primary, DB Y is for training/dev/testing. I perform a backup of X, restore as Y and get the error. So, where am I wrong?
*/
set @src_db = 'Eatx'
set @dest_db = 'Eaty'
set @bak_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatxTemp.bak'
set @data_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty.mdf'
set @log_addr = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty_log.ldf'
TIA!
The part that is wrong is that you never posted the script so no one will be able to tell you what you are doing wrong. 🙂
All you got there is some variables you have set. That wouldn't tell us how you are doing the restore, using the variables, etc. which would be the part of why you get the error. So...could you post the script your using to do this?
Sue
December 28, 2016 at 9:30 pm
This was removed by the editor as SPAM
December 29, 2016 at 8:46 am
Thanks Jason/Sue...I know it has something to do with the logical file name. I don't have this issue with SQL 2012 and I kinda bit off more than I could chew with this gig. Good company, but man, taking me outside my comfort zone!
December 29, 2016 at 11:16 am
Mystic1287 (12/29/2016)
Thanks Jason/Sue...I know it has something to do with the logical file name. I don't have this issue with SQL 2012 and I kinda bit off more than I could chew with this gig. Good company, but man, taking me outside my comfort zone!
Your issue has nothing to do with 2012 - or the version of SQL Server being used. The problem is that you are attempting to restore a database where the logical file name (for the transaction log) is NOT the value you have identified.
To insure that your restore scripts work appropriately you have to either:
1) Insure the storage locations are absolutely the same on both systems - remove any references to MOVE file and include the REPLACE option. SQL Server will then attempt to restore (and replace) the database using the storage locations defined in the backup file.
2) Insure that you have queried either the BAK or the source system to identify logical file names for that database and include every file to be moved and where to move the files.
If you want to automate this process to refresh a DEV/TEST environment - I would recommend creating code that generates the restore script using the tables in the MSDB database on the source system. If you are copying the backup files to a new location - make sure your script accounts for that location and file name. I would also recommend that you configure your DEV/TEST environment to have the same drives and paths so restoring can be done without specifying logical file names to MOVE the files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 29, 2016 at 3:23 pm
Hello all; thanks for the input thus far; so here's the entire script that is documented; sanity check please...I'm more of a systems guy than a SQL DEV/DBA.
USE [msdb]
GO
/****** Object: Job [Copy Production DB to Test DB (single server)] Script Date: 07/25/2008 14:36:27 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/25/2008 14:36:28 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Copy Eatec Production DB to Test DB (single server)',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Copies entire Eatec production database onto an Eatec test database, while leaving test database user data intact.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [copy Eatec production db to Eatec test db] Script Date: 07/25/2008 14:36:28 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'copy Eatec production db to Eatec test db',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @src_db varchar(64), @dest_db varchar(64)
declare @data_addr varchar(512), @log_addr varchar(512), @bak_addr varchar(512)
declare @s-2 varchar(2000)
/* Uses dynamic sql -- replace variables with appropriate values.
Example:
set @src_db = ''eatecy''
set @dest_db = ''eatecx33_new''
set @bak_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\eatecy_copy.bak''
set @data_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\eatecx33_new_data.mdf''
set @log_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\eatecx33_new_log.ldf''
*/
set @src_db = ''Eatecx''
set @dest_db = ''Eatecy''
set @bak_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatecxTemp.bak''
set @data_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eatecy.mdf''
set @log_addr = ''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eatecy_log.ldf''
-- Step 1: Create table in tempdb on server that holds destination db,
-- then copy ui_eusers table from dest db to tempdb
if exists(select * from tempdb.sys.objects where name = ''ui_eusers'' and type = ''u'')
drop table tempdb..ui_eusers
-- copy data from dest db to temp db
exec(''select * into tempdb..ui_eusers from '' + @dest_db + ''..ui_eusers'')
-- Step 2: From src db''s server, create a backup of src db.
-- Backup is "copy_only", so that log files are left intact.
exec(''BACKUP DATABASE '' + @src_db + '' TO DISK = '''''' + @bak_addr + '''''' WITH COPY_ONLY, INIT'')
-- Step 3: From dest db''s server, restore dest db from src db, copy
-- ui_eusers data from tempdb into dest db.
--set to single user mode to ensure no other open connections
use master
set @s-2 = ''ALTER DATABASE '' + @dest_db + '' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;''
-- this statement assumes logical db name is same as physical name, as will be the case 99.9% of time
set @s-2 = @s-2 + (''RESTORE DATABASE '' + @dest_db + '' FROM DISK = '''''' + @bak_addr + '''''' WITH MOVE ''''''
+ @src_db + '''''' TO '''''' + @data_addr + '''''', MOVE '''''' + @src_db + ''_log'''' TO ''''''
+ @log_addr + '''''', REPLACE'')
exec(@s)
-- Step 4: using the newly restored dest db, replace ui_euser data with
-- data from tempdb, and clean up.
-- reset dest db to multi-user mode
use master
exec( ''ALTER DATABASE '' + @dest_db + '' SET MULTI_USER;'')
exec(''use '' + @dest_db + ''; truncate table ui_eusers;
insert into ui_eusers select * from tempdb..ui_eusers'')
drop table tempdb..ui_eusers
go',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Steve
December 29, 2016 at 4:18 pm
Thanks for getting back and posting that. Now we can see what you are actually executing.
So I used the set statements you originally posted and was able to get the restore command that is executing.
Based on your set statements, this is the command you are executing for the restore:
RESTORE DATABASE Eaty FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatxTemp.bak'
WITH MOVE 'Eatx' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty.mdf',
MOVE 'Eatx_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty_log.ldf',
REPLACE
So if you got the exact same error, execute the following to see what the Logical and Physical file names are for the backup are trying to restore in the script (and check if the file is there):
restore filelistonly
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatxTemp.bak'
The script is assuming that the LogicalName for the data File (Type will be D from running the above) to be Eatx
and the LogicalName for the log file (Type will be L from running the above) to be Eatx_log
If that doesn't match up with what the results you see when you run the restore filelistonly, then that's the problem.
Do they match up?
You also want to check to make sure you are pointing to the right locations for all the files. Based on what you first posted with the set statements,
Does @bak_addr point to the right location for the EatxTemp.bak backup? C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Backup\EatxTemp.bak
Check the date, timestamp on the file to make sure it's the one you created when you ran the job.
Since you didn't get an error on the backup, I'm guessing it completed.
Are the data files (@data_addr)for the Y server located in
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\DataAre the log files (@log_addr) for the Y server located in
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL08\MSSQL\Data\Eaty_log.ldf
Sue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply