Sql Backing up Tables: setup

Showing posts with label setup. Show all posts

Thursday, March 29, 2012

Backup Failure - Delete old files

I have setup my backup job to delete file older than 12 hours. I want to
keep atleast 2 backup files saved. The backup runs every night.
My backup job fails on alternate days.
The day it fails, it completes just 2 steps 'backup database' and 'verify
database'.
The day is completes sucessfully, it completes 4 steps 'backup database' and
'verify database' and 'delete old backup files' and 'check data and index
linkage'.
It started failing for last few months. The same setting used to work fine
before.
Any kind of help is greatly appreciated.What is the error message you get at 3rd step?
"helpplease" wrote:
> I have setup my backup job to delete file older than 12 hours. I want to
> keep atleast 2 backup files saved. The backup runs every night.
> My backup job fails on alternate days.
> The day it fails, it completes just 2 steps 'backup database' and 'verify
> database'.
> The day is completes sucessfully, it completes 4 steps 'backup database' and
> 'verify database' and 'delete old backup files' and 'check data and index
> linkage'.
> It started failing for last few months. The same setting used to work fine
> before.
> Any kind of help is greatly appreciated.|||Does not show the description of error inside the job or the maintenance
plan. The following error message is shown on the Application event log.
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
"bluefish" wrote:
> What is the error message you get at 3rd step?
>
> "helpplease" wrote:
> > I have setup my backup job to delete file older than 12 hours. I want to
> > keep atleast 2 backup files saved. The backup runs every night.
> > My backup job fails on alternate days.
> >
> > The day it fails, it completes just 2 steps 'backup database' and 'verify
> > database'.
> >
> > The day is completes sucessfully, it completes 4 steps 'backup database' and
> > 'verify database' and 'delete old backup files' and 'check data and index
> > linkage'.
> >
> > It started failing for last few months. The same setting used to work fine
> > before.
> >
> > Any kind of help is greatly appreciated.|||Specify a report file for the maint plan and check the report file for the error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:E2EA51C2-6ED1-4EF6-BA39-FEF7D275245E@.microsoft.com...
> Does not show the description of error inside the job or the maintenance
> plan. The following error message is shown on the Application event log.
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Insite
> Maint'' (0xE5E76C0F4E0B7546BB973E5A02710F7F) - Status: Failed - Invoked on:
> 2006-02-20 21:00:00 - Message: The job failed. The Job was invoked by
> Schedule 17 (Schedule 1). The last step to run was step 1 (Step 1).
>
> "bluefish" wrote:
>> What is the error message you get at 3rd step?
>>
>> "helpplease" wrote:
>> > I have setup my backup job to delete file older than 12 hours. I want to
>> > keep atleast 2 backup files saved. The backup runs every night.
>> > My backup job fails on alternate days.
>> >
>> > The day it fails, it completes just 2 steps 'backup database' and 'verify
>> > database'.
>> >
>> > The day is completes sucessfully, it completes 4 steps 'backup database' and
>> > 'verify database' and 'delete old backup files' and 'check data and index
>> > linkage'.
>> >
>> > It started failing for last few months. The same setting used to work fine
>> > before.
>> >
>> > Any kind of help is greatly appreciated.

Backup Failure - Delete old files

Sunday, March 25, 2012

Backup error - not part of a multiple family media set

I am new to SQL 2005. I have setup and new maintanaince plan of making backup on to different paths but i encountered an error saying

Executing the query "BACKUP DATABASE [promis_05] TO DISK = N'E:\\ERP Database\\ERP Backup\\Promis_05', DISK = N'\\\\backupsrv\\ERP Backup\\Promis_05' WITH NOFORMAT, INIT, NAME = N'promis_05_backup_20061111181236', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The volume on device 'E:\\ERP Database\\ERP Backup\\Promis_05' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

We did not know what to make of this.

1) Is it bcoz i am backing up the database on 2 locations same time.

2) What is BACKUP WITH FORMAT?

3) Why won't it let me add a new file that is not part of the 'family' ?

4) How does it get to be part of the family?

Thought and ideas are highly appreciated!

When you back up to two files, you are creating a stripe set. The restriction is that all backups sent to a media set must have the same number of stripes. That's the meaning of your error.

You need to create a media set with the number of stripes you want to use. You can't add members later.

The method for creating a new media set is to use the WITH FORMAT option on your backup command. This is the equivalent of reformatting a tape for backups. It wipes out any previous data in the file(s), and sets up the headers correctly.

So, if you issue the same command in your script, adding WITH FORMAT for ONE TIME ONLY, the first time you use that media family, you should be good to go. From then on, you can just use it as your script is now.

Tuesday, March 20, 2012

Backup Device setup

I am a novice and have setup a backup device for the
database and one for logs. When I attempt a backup I
recevie a device error. Status = 112. The error log
gives me no definitive reason for the error. CAn anyone
help?Run below from a DOS prompt to get explanation for OS error 112:
NET HELPMSG 112
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<cneeley@.co.pierce.wi.us> wrote in message
news:09e301c3bf57$e2c38430$3101280a@.phx.gbl...
> I am a novice and have setup a backup device for the
> database and one for logs. When I attempt a backup I
> recevie a device error. Status = 112. The error log
> gives me no definitive reason for the error. CAn anyone
> help?

Backup Dependent file remove

In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.
Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:

> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.

Backup Dependent file remove

In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
would not delete files unless the backup completes successfully. If the
backup fails for some reason, you still have the previous backup.
How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
only way to do it ?
Thanks.Hi
If you look a Maintenance Plans then the maintenance cleanup task can be set
up to remove files older than a given age from a given directory. This can be
dependent on a the successes of a backup database task, although this would
leave files until the backup problem is fixed and you could end up with weeks
of files from failed backups. If you are going to use maintenance tasks then
there was some issues with upgrading in SP2 so starting on SP2 will remove
any issues with upgrading.
John
"DXC" wrote:
> In SQL Server 2000, when you setup "Delete files older than 1 day" option, it
> would not delete files unless the backup completes successfully. If the
> backup fails for some reason, you still have the previous backup.
> How can this be accomplished in SQL Server 2005 ? Is SSIS Package is the
> only way to do it ?
> Thanks.

Monday, March 19, 2012

Backup databases

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Thursday, March 8, 2012

Backup Automatisation in SQL Server 2000 - Need your help.

I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup ?
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.
Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:

> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup ?
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>
|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:

>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and backup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with overwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This database used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?
|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:

>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.

Backup Automatisation in SQL Server 2000 - Need your help.

I am trying to setup my SQL server and I want to plan the backups
My ideas were to create a sys_dat_bak device for the full backup of
all the system databases (master, model, msdb) and a sys_logs_bak for
the transaction log backup of the model db. The backup files created
by MS SQL server would then be backupped again by our normal central
backup system in filesystem mode. Ditto for the user databases, a
app_data_bak and app_logs_bak. For the user database I would schedule
extra tlog-backups during the day.
I have successfully created the backup disk-devices with T-SQL.
When I try to automate the actual backup commands in Enterprise
Manager, I can get the backups to succeed, but when I veryfy with
FILELIST then I seem to be missing files and not getting what I want
on this device.
Here's what I do :
I have a job called Full backup systemdatabases (data and tlog).
Step 1 (= daily full backup of master and clear file before backup)
I use master
BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD ,
NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 2 (= daily full backup of model and append to backupdevice)
I use master
BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
On success goto next step
Step 3 (= Tlog backup of model and clear file before backup)
I use master
BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAME
=
N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
Transaction log', NOFORMAT , NO_TRUNCATE
On success goto next step
Step 4 (= daily full backup of msdb and append to backupdevice)
I use master
BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD ,
NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
MSDB database', NOFORMAT
All these steps finish with success
When I check with restore headeronly, then I can see that 3 session
were started and finished to device sys_data_bak and 1 session to
sys_logs_bak. That's exactly what I hoped for.
When I check with restore filelistonly, then I see on :
- sys_data_bak : two files written : master.mdf and mastlog.ldf
- sys_logs_bak : two files written : model.mdf and modellog.ldf
And now I am puzzled.
Why would this backup write only master.mdf and mastlog.ldf on
sys_data_bak ?
Why would this backup write only model.mdf and model.ldf on
sys_logs_bak ? Model.mdf shouldn't even be on that device.
And where's msdb backup '
Can someone explain please? In need to understand what is happening
here. Also if you have suggestions how to improve the scripts please
do.Hi
1. Use separate device for each database.
2. Use separate device for backup tasks on database.
Eg:
SAMPLE_DATABASE
Create devices:
-SAMPLE_DATABASE_FULL
-SAMPLE_DATABASE_DIFF
-SAMPLE_DATABASE_LOG_1
-SAMPLE_DATABASE_LOG_2
-SAMPLE_DATABASE_LOG_3
-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and bac
kup the file to tape.
-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ove
rwrite) and backup the file to tape.
-And by day use log backup devices , also with overwrite
You can this method easy scripting. Also retore methods.
3. Configure Your database defaults on model, then backup once. This databas
e used only when you create a new database on server, not store data.
Andras Jakus MCDBA
"citizen" wrote:

> I am trying to setup my SQL server and I want to plan the backups
> My ideas were to create a sys_dat_bak device for the full backup of
> all the system databases (master, model, msdb) and a sys_logs_bak for
> the transaction log backup of the model db. The backup files created
> by MS SQL server would then be backupped again by our normal central
> backup system in filesystem mode. Ditto for the user databases, a
> app_data_bak and app_logs_bak. For the user database I would schedule
> extra tlog-backups during the day.
> I have successfully created the backup disk-devices with T-SQL.
> When I try to automate the actual backup commands in Enterprise
> Manager, I can get the backups to succeed, but when I veryfy with
> FILELIST then I seem to be missing files and not getting what I want
> on this device.
> Here's what I do :
> I have a job called Full backup systemdatabases (data and tlog).
> Step 1 (= daily full backup of master and clear file before backup)
> I use master
> BACKUP DATABASE [master] TO [sys_data_bak] WITH INIT , NOUNLOAD
,
> NAME = N'master backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 2 (= daily full backup of model and append to backupdevice)
> I use master
> BACKUP DATABASE [model] TO [sys_data_bak] WITH NOINIT , NOUNLOA
D ,
> NAME = N'model backup', NOSKIP , STATS = 10, NOFORMAT
> On success goto next step
> Step 3 (= Tlog backup of model and clear file before backup)
> I use master
> BACKUP LOG [model] TO [sys_logs_bak] WITH INIT , NOUNLOAD , NAM
E =
> N'model backup tlog', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> Transaction log', NOFORMAT , NO_TRUNCATE
> On success goto next step
> Step 4 (= daily full backup of msdb and append to backupdevice)
> I use master
> BACKUP DATABASE [msdb] TO [sys_data_bak] WITH NOINIT , NOUNLOAD
,
> NAME = N'msdb backup', NOSKIP , STATS = 10, DESCRIPTION = N'Backup
> MSDB database', NOFORMAT
> All these steps finish with success
> When I check with restore headeronly, then I can see that 3 session
> were started and finished to device sys_data_bak and 1 session to
> sys_logs_bak. That's exactly what I hoped for.
> When I check with restore filelistonly, then I see on :
> - sys_data_bak : two files written : master.mdf and mastlog.ldf
> - sys_logs_bak : two files written : model.mdf and modellog.ldf
> And now I am puzzled.
> Why would this backup write only master.mdf and mastlog.ldf on
> sys_data_bak ?
> Why would this backup write only model.mdf and model.ldf on
> sys_logs_bak ? Model.mdf shouldn't even be on that device.
> And where's msdb backup '
> Can someone explain please? In need to understand what is happening
> here. Also if you have suggestions how to improve the scripts please
> do.
>|||You are seeing this because you probably did not specify the WITH FILE =
parameter for the RESTORE FILELISTONLY command. If you do not specify this
parameter, it will show you the files that were backed up for the first
backup in the device. Refer to Books Online for more information on how to
use this parameter.
However as suggested by Andras, you may want to create separate devices to
reduce the confusion.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||On Fri, 30 Jul 2004 08:45:03 -0700, Andras Jakus
<AndrasJakus@.discussions.microsoft.com> wrote:

>Hi
>1. Use separate device for each database.
>2. Use separate device for backup tasks on database.
>Eg:
>SAMPLE_DATABASE
>Create devices:
>-SAMPLE_DATABASE_FULL
>-SAMPLE_DATABASE_DIFF
>-SAMPLE_DATABASE_LOG_1
>-SAMPLE_DATABASE_LOG_2
>-SAMPLE_DATABASE_LOG_3
>-Sunday use SAMPLE_DATABASE_FULL for full db backup (with overwrite) and ba
ckup the file to tape.
>-All other day use SAMPLE_DATABASE_DIFF for differential db backup (with ov
erwrite) and backup the file to tape.
>-And by day use log backup devices , also with overwrite
>You can this method easy scripting. Also retore methods.
>3. Configure Your database defaults on model, then backup once. This databa
se used only when you create a new database on server, not store data.
>Andras Jakus MCDBA
>
Since the database will be very small, we will not be making
differential backups, only full backup's. Those backupfiles will get
backuped again by our central backup system.
I don't think I will have much trouble with the app. database backup,
but I don't know why the backup of the systemdatabases is behaving
like it is now. Do you know ?|||On Fri, 30 Jul 2004 16:49:39 GMT, pankaja@.online.microsoft.com (Pankaj
Agarwal [MSFT]) wrote:

>You are seeing this because you probably did not specify the WITH FILE =
>parameter for the RESTORE FILELISTONLY command. If you do not specify this
>parameter, it will show you the files that were backed up for the first
>backup in the device. Refer to Books Online for more information on how to
>use this parameter.
>However as suggested by Andras, you may want to create separate devices to
>reduce the confusion.
>Thank you for using Microsoft newsgroups.
>Sincerely
>Pankaj Agarwal
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
OK, found it. I must indeed specify with file =
So all the files that must be there, I have found now.
But is there no way to let sql server show ALL the files available ?
Now one must know which fileset and guess how many there are ...
if they don't know all of the backupprocedure.

Backup Automatisation in SQL Server 2000 - Need your help.

Backup and Restore using VBScript (ActiveX)

We are trying to setup a central job server that will perform the backups, restores, and other automated tasks for all of our SQL servers. The goal is that we have one place to check the status of all of our jobs. I have a couple of problems, though.

The first is that, to backup a database on a remote server, we are using VBScript as an ActiveX Script type of step. The script uses SQLDMO. The jobs mostly run fine, but there seems to be a problem with concurrent jobs running against the same server. For instance, I have 4 backup jobs that I kick off at 7:00PM. One of them (not always the same one) will fail with:

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

The databses are all quite small (master, msdb, and a couple of utility dbs we have). Are there threading issues in using SQL-DMO such that I can't run jobs concurrently? The remote server is SQL2000, so I don't believe I can use SMO. My script is at the end of this post.

My second problem is trying to remotely kick off a restore. Currently, when using the local scheduler, we use the RESTORE DATABASE command right after an ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE command. This command is necessary to kill any processes currently using the DB so that a restore will succeed. In DMO I can't find anyway to duplicate this functionality. Any ideas?

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Backup and Restore using VBScript (ActiveX)

Error Code: 0 Error Source= Microsoft SQL-DMO Error Description: [SQL-DMO]The application is busy. Error on Line 11. The step failed.

Thanks

Steve

-- Backup Script --

Dim sServer, sDatabase, sPath, sDumpFile, oSQLServer, oBackup

sServer = "PRDSQL01"
sDatabase = "master"
sPath = "E:\MSSQL\DUMP\"
sDumpFile = "master.dmp"

Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.BackUp")

oSQLServer.loginsecure = true
oSQLServer.Connect sServer

oBackup.Database = sDatabase
oBackup.Files = sPath & sDumpFile
oBackUp.BackupSetDescription = "Backup - " & sDatabase
oBackup.Initialize = "TRUE"
oBackup.SQLBackup oSQLServer

oSQLServer.Disconnect

Set sServer = nothing
Set sDatabase = nothing
Set sPath = nothing
Set sDumpFile = nothing
Set oSQLServer = nothing
Set oBackup = nothing

Hi Stev

Vimal , here ,

sir its helpful to take backup ,please tell me script for restore the taken dump file.

Vimal

Saturday, February 25, 2012

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> T-SQL scheduled using SQL Server Agent Service
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.
> >
> > Plus please post some links to backup command scripts pages.
> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> >
> > Thanks
> > BVR
> >
> >
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > What is the preferred way for Backup and Restore? Maintenance plan
wizard
> > or command line scripts?
> >
> > Also, to setup email notifications on a sql box, what do we need
(outlook
> > client..) on the server box? Is there a step by step document online to
> > setup this?
> >
> > Plus please post some links to backup command scripts pages.
> >
> > Thanks
> > BVR
> >
> >
>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/...us/default.mspx[vbco
l=seagreen]
> Plus please post some links to backup command scripts pages.[/vbcol]
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
> T-SQL scheduled using SQL Server Agent Service
(outlook[vbcol=seagreen]
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/...lt
.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.

> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> Cheers
> Gill
>
>|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
(outlook[vbcol=seagreen]
>

Backup and restore - What is the preffered way?

What is the preferred way for Backup and Restore? Maintenance plan wizard
or command line scripts?
Also, to setup email notifications on a sql box, what do we need (outlook
client..) on the server box? Is there a step by step document online to
setup this?
Plus please post some links to backup command scripts pages.
Thanks
BVR
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
T-SQL scheduled using SQL Server Agent Service
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
You need outlook 2000 onwards installed on the box SQL is installed. SQL
Server is going to be a member of a domain and a MAPI mail server has be to
used (e.g Exchange)
http://www.microsoft.com/resources/d...s/default.mspx
> Plus please post some links to backup command scripts pages.
The BOOKS Online (get the updated version from www.microsoft.com/sql) is
more then enough as a resource for writing T-SQL for Backup/Restore
> Thanks
> BVR
>
Cheers
Gill
|||http://weblogs.sqlteam.com/tarad
Tara has some good backup/restore scripts. She has a standard version and a
SQL LiteSpeed version.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
> What is the preferred way for Backup and Restore? Maintenance plan wizard
> or command line scripts?
> Also, to setup email notifications on a sql box, what do we need (outlook
> client..) on the server box? Is there a step by step document online to
> setup this?
> Plus please post some links to backup command scripts pages.
> Thanks
> BVR
>
|||"Sarbjit Gill" <ssgill.no.spam@.no.spam.hotmail.com> wrote in message
news:%23hl3NHa9EHA.1408@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
> T-SQL scheduled using SQL Server Agent Service
(outlook
> You need outlook 2000 onwards installed on the box SQL is installed. SQL
> Server is going to be a member of a domain and a MAPI mail server has be
to
> used (e.g Exchange)
>
http://www.microsoft.com/resources/d...s/default.mspx
This is not true. Yes, you need a MAPI client, but you can seti it up to
use an SMTP server. In fact that's what I find to be the most stable, set
up an SMTP server on the same box as the SQL Server and use that to forward
outgoing email. This eliminates the problem of the mail client locking up
due to time outs.

> The BOOKS Online (get the updated version from www.microsoft.com/sql) is
> more then enough as a resource for writing T-SQL for Backup/Restore
> Cheers
> Gill
>
>
|||Thanks
BVR
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:OCYipwc9EHA.2788@.TK2MSFTNGP15.phx.gbl...
> http://weblogs.sqlteam.com/tarad
> Tara has some good backup/restore scripts. She has a standard version and
a[vbcol=seagreen]
> SQL LiteSpeed version.
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:ev0apRU9EHA.2804@.TK2MSFTNGP15.phx.gbl...
wizard[vbcol=seagreen]
(outlook
>

Friday, February 24, 2012

Backup a mirrored database

I have a client who is worried about his backup strategy. He has two server
s
and has succesfully setup mirroring. He wants to be able to automatically
backup databaseA before and after the mirror server becomes a principal.
What is the proper procedure to ensure that a backup job doesn't fail when
the roles of principal and mirror are reversed?
For example. On the principal, my client is doing a full backup on sun,
diff monday to sat and tlog backup every hour. On the mirror he has not
scheduled a backup. If the database fails on tuesday, the backup that would
normally run on the principal server will fail. The mirror server (which is
now the principal) currently doesn't have a backup scheduled. What is the
best practice?
Thanks for any help.
DanCreate the same job on both servers, both jobs are always enabled, and in th
e job you use the
sys.database_mirroring catalog view it see if the database is principal or m
irror.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Wood" <DanWood@.discussions.microsoft.com> wrote in message
news:F9A439EF-A131-450E-A032-12382AE9C782@.microsoft.com...
>I have a client who is worried about his backup strategy. He has two serve
rs
> and has succesfully setup mirroring. He wants to be able to automatically
> backup databaseA before and after the mirror server becomes a principal.
> What is the proper procedure to ensure that a backup job doesn't fail when
> the roles of principal and mirror are reversed?
> For example. On the principal, my client is doing a full backup on sun,
> diff monday to sat and tlog backup every hour. On the mirror he has not
> scheduled a backup. If the database fails on tuesday, the backup that wou
ld
> normally run on the principal server will fail. The mirror server (which
is
> now the principal) currently doesn't have a backup scheduled. What is the
> best practice?
> Thanks for any help.
> Dan|||Thanks for you help.
"Tibor Karaszi" wrote:

> Create the same job on both servers, both jobs are always enabled, and in
the job you use the
> sys.database_mirroring catalog view it see if the database is principal or
mirror.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Wood" <DanWood@.discussions.microsoft.com> wrote in message
> news:F9A439EF-A131-450E-A032-12382AE9C782@.microsoft.com...
>

Backup a mirrored database

I have a client who is worried about his backup strategy. He has two servers
and has succesfully setup mirroring. He wants to be able to automatically
backup databaseA before and after the mirror server becomes a principal.
What is the proper procedure to ensure that a backup job doesn't fail when
the roles of principal and mirror are reversed?
For example. On the principal, my client is doing a full backup on sun,
diff monday to sat and tlog backup every hour. On the mirror he has not
scheduled a backup. If the database fails on tuesday, the backup that would
normally run on the principal server will fail. The mirror server (which is
now the principal) currently doesn't have a backup scheduled. What is the
best practice?
Thanks for any help.
DanCreate the same job on both servers, both jobs are always enabled, and in the job you use the
sys.database_mirroring catalog view it see if the database is principal or mirror.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Wood" <DanWood@.discussions.microsoft.com> wrote in message
news:F9A439EF-A131-450E-A032-12382AE9C782@.microsoft.com...
>I have a client who is worried about his backup strategy. He has two servers
> and has succesfully setup mirroring. He wants to be able to automatically
> backup databaseA before and after the mirror server becomes a principal.
> What is the proper procedure to ensure that a backup job doesn't fail when
> the roles of principal and mirror are reversed?
> For example. On the principal, my client is doing a full backup on sun,
> diff monday to sat and tlog backup every hour. On the mirror he has not
> scheduled a backup. If the database fails on tuesday, the backup that would
> normally run on the principal server will fail. The mirror server (which is
> now the principal) currently doesn't have a backup scheduled. What is the
> best practice?
> Thanks for any help.
> Dan|||Thanks for you help.
"Tibor Karaszi" wrote:
> Create the same job on both servers, both jobs are always enabled, and in the job you use the
> sys.database_mirroring catalog view it see if the database is principal or mirror.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan Wood" <DanWood@.discussions.microsoft.com> wrote in message
> news:F9A439EF-A131-450E-A032-12382AE9C782@.microsoft.com...
> >I have a client who is worried about his backup strategy. He has two servers
> > and has succesfully setup mirroring. He wants to be able to automatically
> > backup databaseA before and after the mirror server becomes a principal.
> > What is the proper procedure to ensure that a backup job doesn't fail when
> > the roles of principal and mirror are reversed?
> >
> > For example. On the principal, my client is doing a full backup on sun,
> > diff monday to sat and tlog backup every hour. On the mirror he has not
> > scheduled a backup. If the database fails on tuesday, the backup that would
> > normally run on the principal server will fail. The mirror server (which is
> > now the principal) currently doesn't have a backup scheduled. What is the
> > best practice?
> >
> > Thanks for any help.
> >
> > Dan
>

Thursday, March 29, 2012

Backup Failure - Delete old files

Backup Failure - Delete old files

Backup Failure - Delete old files

Sunday, March 25, 2012

Backup error - not part of a multiple family media set

Tuesday, March 20, 2012

Backup Device setup

Backup Dependent file remove

Backup Dependent file remove

Monday, March 19, 2012

Backup databases

Thursday, March 8, 2012

Backup Automatisation in SQL Server 2000 - Need your help.

Backup Automatisation in SQL Server 2000 - Need your help.

Backup Automatisation in SQL Server 2000 - Need your help.

Backup and Restore using VBScript (ActiveX)

Backup and Restore using VBScript (ActiveX)

Saturday, February 25, 2012

Backup and restore - What is the preffered way?

Backup and restore - What is the preffered way?

Backup and restore - What is the preffered way?

Friday, February 24, 2012

Backup a mirrored database

Backup a mirrored database

Backup a mirrored database

Sql Backing up Tables

Blog Archive

About Me