I am receiving the below error message while doing a Database backup as a
schedule dts package job.
Operating system error 64(The specified network name is no longer available.).
or
Operating system error 64(error not found).
18210 :
BackupDiskFile::RequestDurableMedia: failure on backup device '
\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system error
64(error not found).
18210 :
BackupMedium::ReportIoError: write failure on backup device
'\\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system
error 64(error not found).
Please note, I am backing up to a networked drive/location using the below
command as an example, which is not a mapped drive,
BACKUP DATABASE [DatabaseName] TO DISK = \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak' WITH INIT
The same scheduled dts package backup job sometimes run successfully at
the scheduled time and most times failed. The job is ran under the SQL Server
Agent account which has full access permission to the backup network drive.
The backup dts package always run successfully when ran manually using my
login account.
Thank you and kind regards
MichaelMichael,
The OS error 64 usually indicates the network problems.
Intermittent loss of connectivity to the share. To ensure that the
backups for the database are taken properly, I recommend you backup to
the local drive and then create a NT job to move the job to the share
upon completion of the SQL Backup. If the Nt copy job fails, then we
can be sure that the problem is because of network connectivity.
Hope that helps.
Venu
"Michael" <Michael@.discussions.microsoft.com> wrote in message news:<9973687E-E0C3-41C6-BAF3-D0C240895A01@.microsoft.com>...
> I am receiving the below error message while doing a Database backup as a
> schedule dts package job.
> Operating system error 64(The specified network name is no longer available.).
> or
> Operating system error 64(error not found).
> 18210 :
> BackupDiskFile::RequestDurableMedia: failure on backup device '
> \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system error
> 64(error not found).
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device
> '\\\SERVER_NAME\SQLBackup\Production\DatabaseName.bak'. Operating system
> error 64(error not found).
>
> Please note, I am backing up to a networked drive/location using the below
> command as an example, which is not a mapped drive,
> BACKUP DATABASE [DatabaseName] TO DISK => \\SERVER_NAME\SQLBackup\Production\DatabaseName.bak' WITH INIT
> The same scheduled dts package backup job sometimes run successfully at
> the scheduled time and most times failed. The job is ran under the SQL Server
> Agent account which has full access permission to the backup network drive.
> The backup dts package always run successfully when ran manually using my
> login account.
> Thank you and kind regards
> Michaelsql
Showing posts with label schedule. Show all posts
Showing posts with label schedule. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Backup database and index
Just wonder is what my understanding is correct:
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?
Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?
Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
Backup database and index
Just wonder is what my understanding is correct:
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
1) If I put database and index onto same filegroup, when I schedule a
database backup, is the index will also be backup together with the database
?
2) If I put database and index onto separate filegroups, I need to schedule
2 backup jobs: one backup for database, another job for index at the same
schedule datetime ?Alan,
Fear not! No matter how many data files or filegroups that you have, a full
backup will preserve all the data in the database including indexes, as long
as you don't specify the filegroup or filename in the backup statement.
Indeed the same is true for transaction log and differential backups too.
e.g.
BACKUP DATABASE Northwind NwindGroup1 TO <dumpdevice> will only backup the
NWindGroup1 filegroup.
however:
BACKUP DATABASE Northwind TO <dumpdevice> backs up all files across all
filegroups.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>|||Additionally , SQL Server checks for and requires that a table and index be
backed up together... Even if you put them on separate filegroups and try to
backup only one of the FGs and not both, SQL will stop you with an error
that indicates you must backup and table and all of it's indexes together.
As Mark says,, Fear not!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%233Nt6i3qEHA.3244@.tk2msftngp13.phx.gbl...
> Just wonder is what my understanding is correct:
> 1) If I put database and index onto same filegroup, when I schedule a
> database backup, is the index will also be backup together with the
database
> ?
> 2) If I put database and index onto separate filegroups, I need to
schedule
> 2 backup jobs: one backup for database, another job for index at the same
> schedule datetime ?
>
Saturday, February 25, 2012
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:
Sunday, February 19, 2012
Backup 2005 Express
Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
Samuel
Express doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
Samuel
Express doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>
Backup 2005 Express
Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 2005 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it will backup every day
> Any advice?
> Samuel
>
Backup 2005 Express
Hi
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 200
5 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it w
ill backup every day
> Any advice?
> Samuel
>
I just tried to schedule a backup for a database created in SQL Server 2005
Express and all I could do was the actual backup but I couldn't specify the
schedule so it will backup every day
Any advice?
SamuelExpress doesn't come with Agent. Check out
http://sqldbatips.com/showcode.asp?ID=26
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23K87P5T8GHA.1244@.TK2MSFTNGP03.phx.gbl...
> Hi
> I just tried to schedule a backup for a database created in SQL Server 200
5 Express and all I
> could do was the actual backup but I couldn't specify the schedule so it w
ill backup every day
> Any advice?
> Samuel
>
Thursday, February 16, 2012
Backup
Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
Tuesday, February 14, 2012
Backup
I am in the process of setting up my backup schedule. Is it good practice to
include the transaction logs in the same backup file as my full or
differential backup?
Thank you"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:8E90BB0D-F1B9-47C9-9EFD-B8EB41C5061F@.microsoft.com...
> I am in the process of setting up my backup schedule. Is it good practice
to
> include the transaction logs in the same backup file as my full or
> differential backup?
I tend not to. Mostly personal preference.
> Thank you
include the transaction logs in the same backup file as my full or
differential backup?
Thank you"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:8E90BB0D-F1B9-47C9-9EFD-B8EB41C5061F@.microsoft.com...
> I am in the process of setting up my backup schedule. Is it good practice
to
> include the transaction logs in the same backup file as my full or
> differential backup?
I tend not to. Mostly personal preference.
> Thank you
Backup
Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql serv
er
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find a
ny
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
VijayHi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql serv
er
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find a
ny
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
Backup
Hi
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
Vijay
Hi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
I am having problem with schedule tape backup in maintenance plan sql server
2005<o:p></o:p>
I have made a schedule for tape backup on Ultrium 2 LTO drive for all
databases. Backup succesfully completes but the tape could not be unloaded
even after pressing the eject button on the tape drive. I could not find any
tape unload/rewind option in the maintenace plan.<o:p></o:p>
Is there any option avilable in sql server 2005 to unload the tape in the
maintenance plan schedule backup, if so pls let me know the same at your
earliest and that will be a great help for me<o:p></o:p>
Thanks with regards
Vijay
Hi
"Vijay" wrote:
> Hi
> I am having problem with schedule tape backup in maintenance plan sql server
> 2005<o:p></o:p>
> I have made a schedule for tape backup on Ultrium 2 LTO drive for all
> databases. Backup succesfully completes but the tape could not be unloaded
> even after pressing the eject button on the tape drive. I could not find any
> tape unload/rewind option in the maintenace plan.<o:p></o:p>
> Is there any option avilable in sql server 2005 to unload the tape in the
> maintenance plan schedule backup, if so pls let me know the same at your
> earliest and that will be a great help for me<o:p></o:p>
> Thanks with regards
> Vijay
>
I don't know of any option, but you could add a job step that did a RESTORE
REWINDONLY at the end of the job. See Books inline for more.
John
Backup
I am in the process of setting up my backup schedule. Is it good practice to
include the transaction logs in the same backup file as my full or
differential backup?
Thank you"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:8E90BB0D-F1B9-47C9-9EFD-B8EB41C5061F@.microsoft.com...
> I am in the process of setting up my backup schedule. Is it good practice
to
> include the transaction logs in the same backup file as my full or
> differential backup?
I tend not to. Mostly personal preference.
> Thank you
include the transaction logs in the same backup file as my full or
differential backup?
Thank you"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:8E90BB0D-F1B9-47C9-9EFD-B8EB41C5061F@.microsoft.com...
> I am in the process of setting up my backup schedule. Is it good practice
to
> include the transaction logs in the same backup file as my full or
> differential backup?
I tend not to. Mostly personal preference.
> Thank you
Subscribe to:
Posts (Atom)