We are using SQL Server 2005.
To do db backup and restore from 1 machine to another, do I need to already
have the database created on the 2nd machine ? Do I need to have the tables
and stored procedures and views created already on the 2nd machine ?
Thank you
Hi,
You do not need to create anything on the second server. Just restore the
database from your backup files and the database and everything inside
(tables, stored procedures, views, etc.) will be created for you.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"fniles" wrote:
> We are using SQL Server 2005.
> To do db backup and restore from 1 machine to another, do I need to already
> have the database created on the 2nd machine ? Do I need to have the tables
> and stored procedures and views created already on the 2nd machine ?
> Thank you
>
>
Saturday, February 25, 2012
Backup and Restore
Hi everyone
I have this situation with backup and restore.
I have two instances of sql, sql1 and sql2.
Production server is sql1 and test server is sql2.
I have this situation with backup and restore.
I have two instances of sql, sql1 and sql2.
Production server is sql1 and test server is sql2.
I created database maintenance plan that makes full backup every night
on tape.
I would like to make some kind of job or to add steps to job that was
created with DMplan which will make restore of backuped database to sql2
server.
How can i do this? How can i read which is the last file backuped on
tape?
Thanks very much
Alex
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!"acko bogicevic" <aconi2002@.yahoo.com> wrote in message
news:40e3cace$0$16501$c397aba@.news.newsgroups.ws.. .
> Hi everyone
> I have this situation with backup and restore.
> I have two instances of sql, sql1 and sql2.
> Production server is sql1 and test server is sql2.
> I created database maintenance plan that makes full backup every night
> on tape.
> I would like to make some kind of job or to add steps to job that was
> created with DMplan which will make restore of backuped database to sql2
> server.
> How can i do this? How can i read which is the last file backuped on
> tape?
You can adapt the following. (note, in this case we backup and restore from
disk.)
create procedure restore_FOO as
declare @.backup_file as varchar(255)
select @.backup_file=physical_device_name from
nell.msdb.dbo.backupmediafamily where media_set_id in (select
max(media_set_id) from nell.msdb.dbo.backupset where database_name='FOO')
restore database FOO from disk=@.backup_file with
move 'FOO_Data' to 'f:\sql_data\FOO_data.mdf',
move 'FOO_Log' to 'e:\SQL_LOGs\FOO_log.ldf',
replace
GO
> Thanks very much
> Alex
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||thanks
alex
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Backup and Restore
I have a backup of all my databases schedule to run every night but the problem is that I have to take the latest backup of database A and restore it into database B every morning. I have been doing this every morning manually but is there anyway by which I can schedule this to run automatically every morning bearing in mind that I cannot do DTS to transfer data. Database A must not be touched. The restore to database B has to be from the backup of database A.
Please any response will be greatly appreciated.I have a backup of all my databases schedule to run every night but the problem is that I have to take the latest backup of database A and restore it into database B every morning. I have been doing this every morning manually but -
Q1 Is there anyway by which I can schedule this to run automatically every morning?
- bearing in mind that I cannot do DTS to transfer data. Database A must not be touched. The restore to database B has to be from the backup of database A. Please any response will be greatly appreciated.
A1 Yes. (Create a job with a backup step and a restore step scheduled to run each morning.
For example:
-- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: -- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Please any response will be greatly appreciated.I have a backup of all my databases schedule to run every night but the problem is that I have to take the latest backup of database A and restore it into database B every morning. I have been doing this every morning manually but -
Q1 Is there anyway by which I can schedule this to run automatically every morning?
- bearing in mind that I cannot do DTS to transfer data. Database A must not be touched. The restore to database B has to be from the backup of database A. Please any response will be greatly appreciated.
A1 Yes. (Create a job with a backup step and a restore step scheduled to run each morning.
For example:
-- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: -- PubsDumpABackUp --> PubsBRestore Job Script
-- By: dba
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'PubsDumpABackUp --> PubsBRestore') > 0
PRINT N'The job "PubsDumpABackUp --> PubsBRestore" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'PubsDumpABackUp --> PubsBRestore', @.owner_login_name = N'sa', @.description = N'No description available.', @.category_name = N'Database Maintenance', @.enabled = 1, @.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Step 1', @.command = N'BACKUP DATABASE [Pubs]
TO DISK = ''C:\PubsDumpA.Bkp''
WITH INIT ,
NAME = ''PubsDumpABackUp'', SKIP , STATS = 1 --, FORMAT', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 4, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Step 2', @.command = N'RESTORE DATABASE [PubsB]
FROM
DISK = ''C:\PubsDumpA.Bkp''
WITH FILE = 1, NOUNLOAD,
STATS = 1, RECOVERY, REPLACE,
MOVE ''pubs''
TO ''C:\PubsB.mdf'',
MOVE ''pubs_log''
TO ''C:\PubsB_log.ldf''', @.database_name = N'master', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'C:\PubsDumpABackUp_PubsBRestore.txt', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Schedule 1', @.enabled = 1, @.freq_type = 4, @.active_start_date = 20021025, @.active_start_time = 50000, @.freq_interval = 1, @.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval = 0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time = 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Backup and Restore
Running SQL Server 2K. I use transactional Replication.
It is sometimes necessary to take precautionary backups during
business hours for various reasons. However, I have noted
that backups made while replication is running can not be made
without causing an error during Restore. Is there a
workaround for this, or does Replication need to be diabled
prior to
backup?
Thanks,
Tom
Tom,
if you mean the error from the Log Reader Agent because it detects that the
Distributor is ahead of the Publisher, you can run sp_replrestart in the
publication database with no parameters. This forces replication to continue
even if the Distributor and some Subscribers may now have data that the
Publisher no longer has. If not, please can you post up the error message.
HTH,
Paul Ibison
|||exactly what is the error you get during the restore?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<tkane@.yamner.com> wrote in message
news:c14f01c4385d$32a6e950$a401280a@.phx.gbl...
> Running SQL Server 2K. I use transactional Replication.
> It is sometimes necessary to take precautionary backups during
> business hours for various reasons. However, I have noted
> that backups made while replication is running can not be made
> without causing an error during Restore. Is there a
> workaround for this, or does Replication need to be diabled
> prior to
> backup?
> Thanks,
> Tom
It is sometimes necessary to take precautionary backups during
business hours for various reasons. However, I have noted
that backups made while replication is running can not be made
without causing an error during Restore. Is there a
workaround for this, or does Replication need to be diabled
prior to
backup?
Thanks,
Tom
Tom,
if you mean the error from the Log Reader Agent because it detects that the
Distributor is ahead of the Publisher, you can run sp_replrestart in the
publication database with no parameters. This forces replication to continue
even if the Distributor and some Subscribers may now have data that the
Publisher no longer has. If not, please can you post up the error message.
HTH,
Paul Ibison
|||exactly what is the error you get during the restore?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<tkane@.yamner.com> wrote in message
news:c14f01c4385d$32a6e950$a401280a@.phx.gbl...
> Running SQL Server 2K. I use transactional Replication.
> It is sometimes necessary to take precautionary backups during
> business hours for various reasons. However, I have noted
> that backups made while replication is running can not be made
> without causing an error during Restore. Is there a
> workaround for this, or does Replication need to be diabled
> prior to
> backup?
> Thanks,
> Tom
Backup and restore
Hi,
When I tried to restore a database backup that was created by SQL Server
2000 / SP3a installation into a SQL Server 2000 / SP4, I got the following
error message -
Microsoft SQL-DMO (ODBC SQLState: 42000)
The file on device 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\<DATABASE_NAME>.dbk' is not a valid Microsoft Tape Format
backup set.
RESTORE DATABASE is terminating abnormally.
OK
Hence my following queries -
1) Can I restore a backup
MBS Axapta - MVP
http://www.harishm.com/
Somehow I must have pressed the 'enter' key before writing my earlier post!!
Here are my actual queries -
1) Can I backup/restore from SQL servers with different SPs?
2) If I can, could someone please tell me what the error is all about? Apart
from the SP difference, both systems are identical in all aspects including
network protocols (TCP/IP).
Many thanks in advance,
Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/
"Harish Mohanbabu" wrote:
> Hi,
> When I tried to restore a database backup that was created by SQL Server
> 2000 / SP3a installation into a SQL Server 2000 / SP4, I got the following
> error message -
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> The file on device 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\<DATABASE_NAME>.dbk' is not a valid Microsoft Tape Format
> backup set.
> RESTORE DATABASE is terminating abnormally.
> --
> OK
> --
> Hence my following queries -
> 1) Can I restore a backup
|||could you try running restore filelistonly and restore headeronly...
and could you post the output...
>From my initial toughts, it looks like your bkup is corrupt.. cant be
sure yet..
|||Hi,
There shall not be problem to restore user databases between different SPs.
It is more like backup corruption. You may want to try to install a test
machine with SP3a to make sure it is not the cause of the problem. Or, you
may try to restore to other sql server to test.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Shadow" <dinu_babu@.yahoo.com>
>Newsgroups: microsoft.public.sqlserver.server
>Subject: Re: Backup and restore
>Date: 1 Feb 2006 04:58:37 -0800
>Organization: http://groups.google.com
>Lines: 6
>Message-ID: <1138798716.749411.122180@.g43g2000cwa.googlegroups .com>
>References: <A9767E10-89BD-44BE-805F-06DBDFEE7FA2@.microsoft.com>
> <376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
>NNTP-Posting-Host: 207.46.50.70
>Mime-Version: 1.0
>Content-Type: text/plain; charset="iso-8859-1"
>X-Trace: posting.google.com 1138798722 18121 127.0.0.1 (1 Feb 2006
12:58:42 GMT)
>X-Complaints-To: groups-abuse@.google.com
>NNTP-Posting-Date: Wed, 1 Feb 2006 12:58:42 +0000 (UTC)
>In-Reply-To: <376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
>User-Agent: G2/0.2
>X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET
CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe)
>X-HTTP-Via: 1.0 APS-PRXY-01
>Complaints-To: groups-abuse@.google.com
>Injection-Info: g43g2000cwa.googlegroups.com; posting-host=207.46.50.70;
> posting-account=kL0mBg0AAABi9x5I5akt-eVvS_W0s-_U
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g43g2000c wa.googlegroups.com!not-fo
r-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419694
>X-Tomcat-NG: microsoft.public.sqlserver.server
>could you try running restore filelistonly and restore headeronly...
>and could you post the output...
>sure yet..
>
|||Thanks for your posts :-)
You are right Peter - the backup was corrupted. Copied it once again and it
works like a charm!
Cheers,
Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/
"Peter Yang [MSFT]" wrote:
> Hi,
> There shall not be problem to restore user databases between different SPs.
> It is more like backup corruption. You may want to try to install a test
> machine with SP3a to make sure it is not the cause of the problem. Or, you
> may try to restore to other sql server to test.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
|||Hello Harish,
Welcome! :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Backup and restore
>thread-index: AcYoIJrluIUoFfrTQRWeqgnU3F5b4w==
>X-WBNR-Posting-Host: 217.15.170.246
>From: "=?Utf-8?B?SGFyaXNoIE1vaGFuYmFidQ==?=" <Axapta@.online.nospam>
>References: <A9767E10-89BD-44BE-805F-06DBDFEE7FA2@.microsoft.com>
<376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
<1138798716.749411.122180@.g43g2000cwa.googlegroups .com>
<TbHbgt5JGHA.3152@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Backup and restore
>Date: Thu, 2 Feb 2006 09:46:32 -0800
>Lines: 30
>Message-ID: <BCC389C0-3828-4BED-B7EA-4DED3B7B6CFC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419873
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for your posts :-)
>You are right Peter - the backup was corrupted. Copied it once again and
it[vbcol=seagreen]
>works like a charm!
>Cheers,
>Harish Mohanbabu
>--
>MBS Axapta - MVP
>http://www.harishm.com/
>"Peter Yang [MSFT]" wrote:
SPs.[vbcol=seagreen]
you
>
When I tried to restore a database backup that was created by SQL Server
2000 / SP3a installation into a SQL Server 2000 / SP4, I got the following
error message -
Microsoft SQL-DMO (ODBC SQLState: 42000)
The file on device 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\<DATABASE_NAME>.dbk' is not a valid Microsoft Tape Format
backup set.
RESTORE DATABASE is terminating abnormally.
OK
Hence my following queries -
1) Can I restore a backup
MBS Axapta - MVP
http://www.harishm.com/
Somehow I must have pressed the 'enter' key before writing my earlier post!!
Here are my actual queries -
1) Can I backup/restore from SQL servers with different SPs?
2) If I can, could someone please tell me what the error is all about? Apart
from the SP difference, both systems are identical in all aspects including
network protocols (TCP/IP).
Many thanks in advance,
Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/
"Harish Mohanbabu" wrote:
> Hi,
> When I tried to restore a database backup that was created by SQL Server
> 2000 / SP3a installation into a SQL Server 2000 / SP4, I got the following
> error message -
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> The file on device 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\<DATABASE_NAME>.dbk' is not a valid Microsoft Tape Format
> backup set.
> RESTORE DATABASE is terminating abnormally.
> --
> OK
> --
> Hence my following queries -
> 1) Can I restore a backup
|||could you try running restore filelistonly and restore headeronly...
and could you post the output...
>From my initial toughts, it looks like your bkup is corrupt.. cant be
sure yet..
|||Hi,
There shall not be problem to restore user databases between different SPs.
It is more like backup corruption. You may want to try to install a test
machine with SP3a to make sure it is not the cause of the problem. Or, you
may try to restore to other sql server to test.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Shadow" <dinu_babu@.yahoo.com>
>Newsgroups: microsoft.public.sqlserver.server
>Subject: Re: Backup and restore
>Date: 1 Feb 2006 04:58:37 -0800
>Organization: http://groups.google.com
>Lines: 6
>Message-ID: <1138798716.749411.122180@.g43g2000cwa.googlegroups .com>
>References: <A9767E10-89BD-44BE-805F-06DBDFEE7FA2@.microsoft.com>
> <376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
>NNTP-Posting-Host: 207.46.50.70
>Mime-Version: 1.0
>Content-Type: text/plain; charset="iso-8859-1"
>X-Trace: posting.google.com 1138798722 18121 127.0.0.1 (1 Feb 2006
12:58:42 GMT)
>X-Complaints-To: groups-abuse@.google.com
>NNTP-Posting-Date: Wed, 1 Feb 2006 12:58:42 +0000 (UTC)
>In-Reply-To: <376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
>User-Agent: G2/0.2
>X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET
CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe)
>X-HTTP-Via: 1.0 APS-PRXY-01
>Complaints-To: groups-abuse@.google.com
>Injection-Info: g43g2000cwa.googlegroups.com; posting-host=207.46.50.70;
> posting-account=kL0mBg0AAABi9x5I5akt-eVvS_W0s-_U
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfee d00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!g43g2000c wa.googlegroups.com!not-fo
r-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419694
>X-Tomcat-NG: microsoft.public.sqlserver.server
>could you try running restore filelistonly and restore headeronly...
>and could you post the output...
>sure yet..
>
|||Thanks for your posts :-)
You are right Peter - the backup was corrupted. Copied it once again and it
works like a charm!
Cheers,
Harish Mohanbabu
MBS Axapta - MVP
http://www.harishm.com/
"Peter Yang [MSFT]" wrote:
> Hi,
> There shall not be problem to restore user databases between different SPs.
> It is more like backup corruption. You may want to try to install a test
> machine with SP3a to make sure it is not the cause of the problem. Or, you
> may try to restore to other sql server to test.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
|||Hello Harish,
Welcome! :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Backup and restore
>thread-index: AcYoIJrluIUoFfrTQRWeqgnU3F5b4w==
>X-WBNR-Posting-Host: 217.15.170.246
>From: "=?Utf-8?B?SGFyaXNoIE1vaGFuYmFidQ==?=" <Axapta@.online.nospam>
>References: <A9767E10-89BD-44BE-805F-06DBDFEE7FA2@.microsoft.com>
<376FB79B-EC2D-4520-A7B8-F20D4A3CEBC9@.microsoft.com>
<1138798716.749411.122180@.g43g2000cwa.googlegroups .com>
<TbHbgt5JGHA.3152@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Backup and restore
>Date: Thu, 2 Feb 2006 09:46:32 -0800
>Lines: 30
>Message-ID: <BCC389C0-3828-4BED-B7EA-4DED3B7B6CFC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:419873
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for your posts :-)
>You are right Peter - the backup was corrupted. Copied it once again and
it[vbcol=seagreen]
>works like a charm!
>Cheers,
>Harish Mohanbabu
>--
>MBS Axapta - MVP
>http://www.harishm.com/
>"Peter Yang [MSFT]" wrote:
SPs.[vbcol=seagreen]
you
>
Backup and Restore
I need to backup 10 GB of data on the west coast and send over a dedicated T1
to restore on the east coast nightly. What is the best method?
Thanks.
LaEsmeralda
I would think (assuming your bandwidth is about 1.5Mbps):
1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
password protection (so the data doesn't get stolen in transmission)
2) compress backup file
3) send compressed file to East Coast (FTP? NTFS file copy? what kind
of tunnel do you have?)
4) uncompress file (on local disk on East Coast)
5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
backup password
Also, do you need all 10G to go every night? How much of the data is
_changed_ on a daily basis? What about a full backup (10GB presumably)
weekly & logs nightly? Assuming the modification activity against the
DB is not excessive, the logs should be considerably smaller than the
full DB backup. You might even consider a full DB less regularly (once
a month maybe?) with differentials weekly & logs nightly. Just use a
little imagination.
*mike hodgson*
http://sqlnerd.blogspot.com
PS. You're not a Victor Hugo fan are you?
LaEsmeralda wrote:
>I need to backup 10 GB of data on the west coast and send over a dedicated T1
>to restore on the east coast nightly. What is the best method?
>Thanks.
>LaEsmeralda
>
|||Your are absolutley right Mike,
Do full backup once as Mike suggested till step 5.
After that do a log dump on hourly basis or so and ship that to east coast
load it there. The log size wont be that huge. The DB i monitor have 60GB of
data. We do the above method and its running smoothly for the past 3 years.
Thanks,
Sree
"Mike Hodgson" wrote:
> I would think (assuming your bandwidth is about 1.5Mbps):
> 1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
> password protection (so the data doesn't get stolen in transmission)
> 2) compress backup file
> 3) send compressed file to East Coast (FTP? NTFS file copy? what kind
> of tunnel do you have?)
> 4) uncompress file (on local disk on East Coast)
> 5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
> backup password
> Also, do you need all 10G to go every night? How much of the data is
> _changed_ on a daily basis? What about a full backup (10GB presumably)
> weekly & logs nightly? Assuming the modification activity against the
> DB is not excessive, the logs should be considerably smaller than the
> full DB backup. You might even consider a full DB less regularly (once
> a month maybe?) with differentials weekly & logs nightly. Just use a
> little imagination.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
> PS. You're not a Victor Hugo fan are you?
>
> LaEsmeralda wrote:
>
to restore on the east coast nightly. What is the best method?
Thanks.
LaEsmeralda
I would think (assuming your bandwidth is about 1.5Mbps):
1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
password protection (so the data doesn't get stolen in transmission)
2) compress backup file
3) send compressed file to East Coast (FTP? NTFS file copy? what kind
of tunnel do you have?)
4) uncompress file (on local disk on East Coast)
5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
backup password
Also, do you need all 10G to go every night? How much of the data is
_changed_ on a daily basis? What about a full backup (10GB presumably)
weekly & logs nightly? Assuming the modification activity against the
DB is not excessive, the logs should be considerably smaller than the
full DB backup. You might even consider a full DB less regularly (once
a month maybe?) with differentials weekly & logs nightly. Just use a
little imagination.
*mike hodgson*
http://sqlnerd.blogspot.com
PS. You're not a Victor Hugo fan are you?
LaEsmeralda wrote:
>I need to backup 10 GB of data on the west coast and send over a dedicated T1
>to restore on the east coast nightly. What is the best method?
>Thanks.
>LaEsmeralda
>
|||Your are absolutley right Mike,
Do full backup once as Mike suggested till step 5.
After that do a log dump on hourly basis or so and ship that to east coast
load it there. The log size wont be that huge. The DB i monitor have 60GB of
data. We do the above method and its running smoothly for the past 3 years.
Thanks,
Sree
"Mike Hodgson" wrote:
> I would think (assuming your bandwidth is about 1.5Mbps):
> 1) BACKUP DATABASE MyDB ... (to a local disk (West Coast)), with
> password protection (so the data doesn't get stolen in transmission)
> 2) compress backup file
> 3) send compressed file to East Coast (FTP? NTFS file copy? what kind
> of tunnel do you have?)
> 4) uncompress file (on local disk on East Coast)
> 5) RESTORE DATABASE MyDB ... (on the East Coast server)), using the
> backup password
> Also, do you need all 10G to go every night? How much of the data is
> _changed_ on a daily basis? What about a full backup (10GB presumably)
> weekly & logs nightly? Assuming the modification activity against the
> DB is not excessive, the logs should be considerably smaller than the
> full DB backup. You might even consider a full DB less regularly (once
> a month maybe?) with differentials weekly & logs nightly. Just use a
> little imagination.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
> PS. You're not a Victor Hugo fan are you?
>
> LaEsmeralda wrote:
>
backup and restore
Could anyone please give me a link that will show me the step-by-step backup
and restore SQL server 2005 database. Thanks
hvn
http://msdn2.microsoft.com/en-us/library/ms191239.aspx
http://elsasoft.org
"Huy Nguyen" <hnguyen@.gmail.com> wrote in message
news:OME9Kq1NIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Could anyone please give me a link that will show me the step-by-step
> backup and restore SQL server 2005 database. Thanks
> hvn
and restore SQL server 2005 database. Thanks
hvn
http://msdn2.microsoft.com/en-us/library/ms191239.aspx
http://elsasoft.org
"Huy Nguyen" <hnguyen@.gmail.com> wrote in message
news:OME9Kq1NIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Could anyone please give me a link that will show me the step-by-step
> backup and restore SQL server 2005 database. Thanks
> hvn
Subscribe to:
Posts (Atom)