Running Backup through Maintenance Plan

I am receiving an Event 208 when running our autmated backup in Enterprise
Manager. The job use to run fine however possibly a change in password
caused this to stop working? The error states:
Event Type:Warning
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:208
Date:11/21/2006
Time:8:45:00 AM
User:N/A
Computer:CROMWELL-DBPROD
Description:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan1'' (0x8E8A44BEA81D964CAAF7E3F3338674D3) - Status: Failed -
Invoked on: 2006-11-21 08:45:00 - Message: The job failed. The owner () of
job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' does not
have server access.
How can I become owner of this job?
Check the properties for the job named
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'
and change the owner to your login if you want to be the
owner.
-Sue
On Tue, 21 Nov 2006 06:57:02 -0800, Andrew Harlan
<AndrewHarlan@.discussions.microsoft.com> wrote:

>I am receiving an Event 208 when running our autmated backup in Enterprise
>Manager. The job use to run fine however possibly a change in password
>caused this to stop working? The error states:
>Event Type:Warning
>Event Source:SQLSERVERAGENT
>Event Category:Job Engine
>Event ID:208
>Date:11/21/2006
>Time:8:45:00 AM
>User:N/A
>Computer:CROMWELL-DBPROD
>Description:
>SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
>Maintenance Plan1'' (0x8E8A44BEA81D964CAAF7E3F3338674D3) - Status: Failed -
>Invoked on: 2006-11-21 08:45:00 - Message: The job failed. The owner () of
>job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' does not
>have server access.
>
>How can I become owner of this job?

Running Backup Jobs

Hi everyone
I have a SQL Server 2005. On this server is a database that only a handfull
of users may access - This excludes the normal IT people such as Domain
Admins etc. Only problem is that if I remove BuiltIn\Administrators from the
sysadmin server role I am no longer able to do backups of the database. If I
leave the BuiltIn\Administrator in the SysAdmin role they can access the
database.
Any help would be appriciated.
Thanks.
Regards
JonasJonas
By default all members of Administrators group have login SQL Server .
What is an account SQL Server Agent running under? Is it Local Account?
Create a user domain account/group on the box SQL Server running on. Change
Sql Server Agent's/MSSqlService account to this new one but make sure that
this account has an appropriate permissions to access the network
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:C06D17CC-95A2-48A3-BFD7-6288CA2918BE@.microsoft.com...
> Hi everyone
> I have a SQL Server 2005. On this server is a database that only a
> handfull
> of users may access - This excludes the normal IT people such as Domain
> Admins etc. Only problem is that if I remove BuiltIn\Administrators from
> the
> sysadmin server role I am no longer able to do backups of the database. If
> I
> leave the BuiltIn\Administrator in the SysAdmin role they can access the
> database.
> Any help would be appriciated.
> Thanks.
> Regards
> Jonas|||Thanks for that. The Agent was running Local System, changed that as
suggested and then removed builtin admin from the sysadmin role. Everything
works.
"Uri Dimant" wrote:

> Jonas
> By default all members of Administrators group have login SQL Server .
> What is an account SQL Server Agent running under? Is it Local Account?
> Create a user domain account/group on the box SQL Server running on. Chan
ge
> Sql Server Agent's/MSSqlService account to this new one but make sure that
> this account has an appropriate permissions to access the network
>
>
> "Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
> news:C06D17CC-95A2-48A3-BFD7-6288CA2918BE@.microsoft.com...
>
>sql

running backup and restore...

I had my IT dept. install sql server 2005 enterprise edition on a new windows server 2003. All other machines are running sql 2000 server on windows server 2000. I am a sa and local admin on all servers. I tried performing copy database, backup and restore and detach and attach to upgrade the sql server 2000 databases to 2005 and all fail. They also installed sql server 2005 sp2. I followed all steps. It's like the servers dont talk to each other. Their all on the company's domain. I am wondering if something happen with the install but the IT dept. insist that everything went fine. It's strange that I can't perform a simple backup and restore it on the new server buy when I click on restore it doesn't let me browse to get the backup file on the 2000 server. I never had a problem in Sql 2000 with backup. Can it be the installation was corrupted somehow. It seems fine. I haven't created any new databases because I wanted to move the databases from sql 2000. Can anyone help me get a clue of what the problem is please?

ThanksSounds like the service account for the SQL Server 2005 service has no network rights. Try copying the backup file locally, then doing the restore.

Running an Instance of SQL Server 2000 in SQL Server 2005

Can I run an instance of SQL Server 2000 in SQL Server 2005?

I was planning on restoring a backup of my SQL Server 2000 db to SS 2005, but keep the SS 2000 configuration. The main reason for doing so is to maintain the DTS packages I have in SS 2000 because my understanding is that these are not easily duplicated in SS 2005 Integration Services.

Thanks-Dave

You cannot run "an Instance of SQL Server 2000 in SQL Server 2005".

However, you can attach a SQL 2000 database to SQL 2005, and leave the 'compatibility level' set for SQL 2000.

But perhaps you mean to ask if you can ALSO have a SQL 2000 server INSTANCE on the same computer as a SQL 2005 server Instance -and if so, that would be 'Yes'. (You would actually 'install' the SQL 2000 software...)

|||

Arnie,

I do plan on attaching a SQL 2000 database to SQL 2005 and leaving the 'compatibility level' set for SQL 2000...

With that said, will I be able to access my DTS packages from SQL 2000? Where do I reference these in SQL 2005? If i'm running in SQL 2000 compatibility level will the SQL 2000 DTS packages function the same?

Thanks-David

|||

When you Attach /Resotre a SQL Server 2000 database in SQL Server 2005 instance , the compatibility level will be 80 (that is of sql server 2000) for this database. But its nothing to do with DTS. For DTS in sql server 2005 , you have an option for backward compatibility.

Open your SQL Server 2005 -- You can find "Management " node expand it -- you will find "Legacy" -- under it DTS

you can import DTS package of sql server 2000 here and you can run. But i would always recomment to re-design the DTS packages in SSIS , coz you have to anyway migrate this if not today , tomarrrow

Madhu

|||

Madhu,

Thanks for the response. I will import the SS 2000 DTS packages into SS 2205 and test. As for re-designing these in SSIS I will have to wait and see before moving forward.

David

sql

run times of SSIS package not making sense....HELP!

We have an SSIS package that was created to migrate data in from a few production databases. The steps for the package are as follows...

backup databases on server 1 (prod database server) restore database to SSIS server (server 2) . truncate worker tables in SSIS server's (server 2) Main DB database. copy data from restored db tables to working db tables ( database to database) Start Multiple threads (15 ) and run steps from here in parrallel Combination of Data flow tasks and SQL scripts and Stored procedures used to flatten data out and combine data for reporting purposes.

The average run time is 8 hours.

the issue we are seeing is this, the package will fluctuate in run times from 4 hours to over 11 with no change in the data or the underlying SSIS package. We have looked for any changes or things that would effect this but have not found anything that changed...

Also, certain steps are running shorter while others double in time. there doesnt seem to be any rhyme or reason to this behaviour. The server is x64 12GB of RAM 2 dual core 3.2Ghz.

Please let me know if you need any more information or specifics...

the only thing I have seen so far that looks out of place is Tempdb has one of its files that is 20+GB.

Thanks,

Chris

Are you using "fast load" in your OLE DB Destination connections?|||

yes we are using Fast load...

we have narrowed the actual longest running spot right now... it has been running for over 10 hours....

I had to change some of the names of Tables etc... but here is the rought outline of what the SQL task does:

truncate table [Reporting].[dbo].[ReportTable]
INSERT INTO [Reporting].[dbo].[ReportTable] (pid)
SELECT DISTINCT [MainAPPDB].[dbo].[ReportTableHistory].[Client_ID]
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblBrokerContact] ON [SecondAPPdb].[dbo].[tblBrokerContact].[BrokerContact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]

UPDATE [Reporting].[dbo].[ReportTable] SET rdata = X.rAsXML
FROM
(
SELECT
C.pid as 'primid',
(SELECT [MainAPPDB].[dbo].[ReportTableStatus].[Description] --varchar(25)
,CONVERT(CHAR(30),[MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedOn],100) AS [LastUpdatedOn] --varchar(30)
,[SecondAPPdb].[dbo].[tblContact].[Contact_FName] + ' ' + [SecondAPPdb].[dbo].[tblContact].[Contact_LName] AS [LastUpdatedBy] --varchar(50)
,[MainAPPDB].[dbo].[ReportTableHistory].[Reason] --varchar(300)
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblContact] ON [SecondAPPdb].[dbo].[tblContact].[Contact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]
WHERE [C_ID] = C.pid FOR XML RAW ('ReportTableHistory'), ROOT('ReportTableStatusHistories'), ELEMENTS XSINIL) as rAsXML
FROM [Reporting].[dbo].[ReportTable] C) X
WHERE X.primid = pid

DECLARE @.inxml XML
DECLARE @.res XML
DECLARE @.pid INT

DECLARE cur CURSOR fast_forward FOR
SELECT pid,rdata FROM [Reporting].[dbo].[ReportTable]
OPEN cur
FETCH next FROM cur INTO @.pid, @.inxml
WHILE @.@.fetch_status = 0
BEGIN
EXEC ExternFunctions_FormatReportTableHistory @.inxml, @.res OUT
UPDATE [Reporting].[dbo].[Client] SET ReportTableHistory = @.res
WHERE [Reporting].[dbo].[Client].[Client_ID] = @.pid
FETCH next FROM cur INTO @.pid,@.inxml
END
CLOSE cur
DEALLOCATE cur

|||Try asking this question in the Transact-SQL forum as it seems that's where your issue is. It doesn't look like this is an SSIS issue anymore.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||

ok I posted the question there as well..

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David
>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run sp when job fails

I have a few backup jobs of SQL 2000 databases that are scheduled and run
via SQL Agent. Is it possible to have SQL Server run a stored procedure if
a job fails? I only saw options for emailing but that required mail
profiles, etc. Thanks.
David>I have a few backup jobs of SQL 2000 databases that are scheduled and run
>via SQL Agent. Is it possible to have SQL Server run a stored procedure if
>a job fails?
Sure, create the job with two steps. Step 1 does the bulk of your work. It
is set to quit the job with success, or move to the next step on failure.
The next step (which only gets reached if step 1 fails) can call your stored
procedure, send an e-mail, etc.
A

Run Maintanance Plan As Agent Job Step

Hi Everyone,
I have created a maintanance plan to generate a full database backup with no
schedule.
I have then created a new Agent job with multiple steps, most of the steps
execute TSQL and these seem to be working. One of the job steps is to execute
the Full database backup SSIS package generated by the Maintanance plan. This
step executes in less than 1 second without an error and does not generate
the backup.
If I execute the maintanance plan directly via the right click EXECUTE
command it works as expected.
I have checked to ensure that the Agent job owner is a member of the
sysadmin so I don't think access rights are an issue.
Does anyone have any ideas?
I have considered copying the TSQL the maintanance plan generated and
executing this in the step instead of the SSIS package but I would like to
know why my existing configuration will not work.
Please help
ACM
On Jun 11, 12:58 pm, ACM <A...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I have created a maintanance plan to generate a full database backup with no
> schedule.
> I have then created a new Agent job with multiple steps, most of the steps
> execute TSQL and these seem to be working. One of the job steps is to execute
> the Full database backup SSIS package generated by the Maintanance plan. This
> step executes in less than 1 second without an error and does not generate
> the backup.
> If I execute the maintanance plan directly via the right click EXECUTE
> command it works as expected.
> I have checked to ensure that the Agent job owner is a member of the
> sysadmin so I don't think access rights are an issue.
> Does anyone have any ideas?
> I have considered copying the TSQL the maintanance plan generated and
> executing this in the step instead of the SSIS package but I would like to
> know why my existing configuration will not work.
> Please help
> ACM
Hi
Try running dtexec with verbose reporting see
http://msdn2.microsoft.com/en-us/library/ms162810(SQL.90).aspx
John

Run Maintanance Plan As Agent Job Step

Hi Everyone,
I have created a maintanance plan to generate a full database backup with no
schedule.
I have then created a new Agent job with multiple steps, most of the steps
execute TSQL and these seem to be working. One of the job steps is to execute
the Full database backup SSIS package generated by the Maintanance plan. This
step executes in less than 1 second without an error and does not generate
the backup.
If I execute the maintanance plan directly via the right click EXECUTE
command it works as expected.
I have checked to ensure that the Agent job owner is a member of the
sysadmin so I don't think access rights are an issue.
Does anyone have any ideas?
I have considered copying the TSQL the maintanance plan generated and
executing this in the step instead of the SSIS package but I would like to
know why my existing configuration will not work.
Please help
ACMOn Jun 11, 12:58 pm, ACM <A...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I have created a maintanance plan to generate a full database backup with no
> schedule.
> I have then created a new Agent job with multiple steps, most of the steps
> execute TSQL and these seem to be working. One of the job steps is to execute
> the Full database backup SSIS package generated by the Maintanance plan. This
> step executes in less than 1 second without an error and does not generate
> the backup.
> If I execute the maintanance plan directly via the right click EXECUTE
> command it works as expected.
> I have checked to ensure that the Agent job owner is a member of the
> sysadmin so I don't think access rights are an issue.
> Does anyone have any ideas?
> I have considered copying the TSQL the maintanance plan generated and
> executing this in the step instead of the SSIS package but I would like to
> know why my existing configuration will not work.
> Please help
> ACM
Hi
Try running dtexec with verbose reporting see
http://msdn2.microsoft.com/en-us/library/ms162810(SQL.90).aspx
John

Run Barch in SQL Server problem - share drive??

Hello Guys,
I have a problem with a job moving and zipping file to share drive
What does my Batch?
1) Copy backup device (initially in D:/) into
//SERVER/SHARE/Archived_Backups/
2) Create new folder in //SERVER/SHARE/Archived_Backups/
3) Move the DEVICE into this new folder
4) Zip the DEVICE and named it today.zip
if I run it manually the batch file works perfectly, But if I launch it
with sql server (what I would like to perform) this step does not run
perfectly.
What does this job?
1) run the batch
Step 1
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'start D:\Backups\MyBacth.bat'
EXEC master..xp_cmdshell @.var
2) Overwrite the DEVICE
3) Verify DEVICE integrity
I have already set privileges for sqlservice in to this share
What do i need to do? Any Suggestions?
Thanks in advance
InaSorry,
Thank you for your answer... yes it is running and has network
privileges.
Regards,
Ina
John Bell wrote:
> Hi
> Apart from the typo in your script! You don't say what the SQL Agent account
> is running as and if it has network privileges.
> John
> "ina" wrote:
> > Hello Guys,
> >
> > I have a problem with a job moving and zipping file to share drive
> >
> >
> > What does my Batch?
> >
> >
> >
> > 1) Copy backup device (initially in D:/) into
> > //SERVER/SHARE/Archived_Backups/
> >
> > 2) Create new folder in //SERVER/SHARE/Archived_Backups/
> >
> > 3) Move the DEVICE into this new folder
> >
> > 4) Zip the DEVICE and named it today.zip
> >
> >
> > if I run it manually the batch file works perfectly, But if I launch it
> > with sql server (what I would like to perform) this step does not run
> > perfectly.
> >
> > What does this job?
> >
> > 1) run the batch
> > Step 1
> > DECLARE @.cmd sysname, @.var sysname
> > SET @.var = 'start D:\Backups\MyBacth.bat'
> > EXEC master..xp_cmdshell @.var
> >
> > 2) Overwrite the DEVICE
> >
> > 3) Verify DEVICE integrity
> >
> >
> > I have already set privileges for sqlservice in to this share
> >
> >
> > What do i need to do? Any Suggestions?
> >
> >
> > Thanks in advance
> >
> >
> >
> > Ina
> >
> >|||Hi Ina
Does this mean that you have solved the problem? If not can you log onto the
server as the service account and run the job?
John
"ina" wrote:
> Sorry,
> Thank you for your answer... yes it is running and has network
> privileges.
> Regards,
> Ina
>
> John Bell wrote:
> > Hi
> >
> > Apart from the typo in your script! You don't say what the SQL Agent account
> > is running as and if it has network privileges.
> >
> > John
> >
> > "ina" wrote:
> >
> > > Hello Guys,
> > >
> > > I have a problem with a job moving and zipping file to share drive
> > >
> > >
> > > What does my Batch?
> > >
> > >
> > >
> > > 1) Copy backup device (initially in D:/) into
> > > //SERVER/SHARE/Archived_Backups/
> > >
> > > 2) Create new folder in //SERVER/SHARE/Archived_Backups/
> > >
> > > 3) Move the DEVICE into this new folder
> > >
> > > 4) Zip the DEVICE and named it today.zip
> > >
> > >
> > > if I run it manually the batch file works perfectly, But if I launch it
> > > with sql server (what I would like to perform) this step does not run
> > > perfectly.
> > >
> > > What does this job?
> > >
> > > 1) run the batch
> > > Step 1
> > > DECLARE @.cmd sysname, @.var sysname
> > > SET @.var = 'start D:\Backups\MyBacth.bat'
> > > EXEC master..xp_cmdshell @.var
> > >
> > > 2) Overwrite the DEVICE
> > >
> > > 3) Verify DEVICE integrity
> > >
> > >
> > > I have already set privileges for sqlservice in to this share
> > >
> > >
> > > What do i need to do? Any Suggestions?
> > >
> > >
> > > Thanks in advance
> > >
> > >
> > >
> > > Ina
> > >
> > >
>|||Hello Jonh I was in holidays,
Happy new 2007!!!
I did not solved the problem. my sqlservice has network privileges but
I cannot run this job. So, I did a scheduled task for copy and zip the
backup and run a job to overwrite the existing backup.
I have three disk configurate with my slq server (C --> local, D -->
Data and F --> backup) how can I set up a new one without restall sql
Server again.
Best regards,
Ina
John Bell wrote:
> Hi Ina
> Does this mean that you have solved the problem? If not can you log onto the
> server as the service account and run the job?
> John
> "ina" wrote:
> > Sorry,
> >
> > Thank you for your answer... yes it is running and has network
> > privileges.
> >
> > Regards,
> >
> > Ina
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Apart from the typo in your script! You don't say what the SQL Agent account
> > > is running as and if it has network privileges.
> > >
> > > John
> > >
> > > "ina" wrote:
> > >
> > > > Hello Guys,
> > > >
> > > > I have a problem with a job moving and zipping file to share drive
> > > >
> > > >
> > > > What does my Batch?
> > > >
> > > >
> > > >
> > > > 1) Copy backup device (initially in D:/) into
> > > > //SERVER/SHARE/Archived_Backups/
> > > >
> > > > 2) Create new folder in //SERVER/SHARE/Archived_Backups/
> > > >
> > > > 3) Move the DEVICE into this new folder
> > > >
> > > > 4) Zip the DEVICE and named it today.zip
> > > >
> > > >
> > > > if I run it manually the batch file works perfectly, But if I launch it
> > > > with sql server (what I would like to perform) this step does not run
> > > > perfectly.
> > > >
> > > > What does this job?
> > > >
> > > > 1) run the batch
> > > > Step 1
> > > > DECLARE @.cmd sysname, @.var sysname
> > > > SET @.var = 'start D:\Backups\MyBacth.bat'
> > > > EXEC master..xp_cmdshell @.var
> > > >
> > > > 2) Overwrite the DEVICE
> > > >
> > > > 3) Verify DEVICE integrity
> > > >
> > > >
> > > > I have already set privileges for sqlservice in to this share
> > > >
> > > >
> > > > What do i need to do? Any Suggestions?
> > > >
> > > >
> > > > Thanks in advance
> > > >
> > > >
> > > >
> > > > Ina
> > > >
> > > >
> >
> >|||John Thank you for this tips.
I will try to do that.
Ina
John Bell wrote:
> Hi Ina
> Happy New Year!
> Looking back at your script, can you change the batch file it so that does
> not map the network drive, zips up the backup file before it copies it to the
> UNC? On the extended properties for the step can you then add a log file so
> that any error messages are captured. Then schedule the job to run and post
> the messages from the log file.
> Thanks
> John
>
> "ina" wrote:
> > Hello Jonh I was in holidays,
> >
> > Happy new 2007!!!
> >
> > I did not solved the problem. my sqlservice has network privileges but
> > I cannot run this job. So, I did a scheduled task for copy and zip the
> > backup and run a job to overwrite the existing backup.
> >
> > I have three disk configurate with my slq server (C --> local, D -->
> > Data and F --> backup) how can I set up a new one without restall sql
> > Server again.
> >
> > Best regards,
> >
> > Ina
> >
> > John Bell wrote:
> > > Hi Ina
> > >
> > > Does this mean that you have solved the problem? If not can you log onto the
> > > server as the service account and run the job?
> > >
> > > John
> > >
> > > "ina" wrote:
> > >
> > > > Sorry,
> > > >
> > > > Thank you for your answer... yes it is running and has network
> > > > privileges.
> > > >
> > > > Regards,
> > > >
> > > > Ina
> > > >
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > Apart from the typo in your script! You don't say what the SQL Agent account
> > > > > is running as and if it has network privileges.
> > > > >
> > > > > John
> > > > >
> > > > > "ina" wrote:
> > > > >
> > > > > > Hello Guys,
> > > > > >
> > > > > > I have a problem with a job moving and zipping file to share drive
> > > > > >
> > > > > >
> > > > > > What does my Batch?
> > > > > >
> > > > > >
> > > > > >
> > > > > > 1) Copy backup device (initially in D:/) into
> > > > > > //SERVER/SHARE/Archived_Backups/
> > > > > >
> > > > > > 2) Create new folder in //SERVER/SHARE/Archived_Backups/
> > > > > >
> > > > > > 3) Move the DEVICE into this new folder
> > > > > >
> > > > > > 4) Zip the DEVICE and named it today.zip
> > > > > >
> > > > > >
> > > > > > if I run it manually the batch file works perfectly, But if I launch it
> > > > > > with sql server (what I would like to perform) this step does not run
> > > > > > perfectly.
> > > > > >
> > > > > > What does this job?
> > > > > >
> > > > > > 1) run the batch
> > > > > > Step 1
> > > > > > DECLARE @.cmd sysname, @.var sysname
> > > > > > SET @.var = 'start D:\Backups\MyBacth.bat'
> > > > > > EXEC master..xp_cmdshell @.var
> > > > > >
> > > > > > 2) Overwrite the DEVICE
> > > > > >
> > > > > > 3) Verify DEVICE integrity
> > > > > >
> > > > > >
> > > > > > I have already set privileges for sqlservice in to this share
> > > > > >
> > > > > >
> > > > > > What do i need to do? Any Suggestions?
> > > > > >
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > >
> > > > > >
> > > > > > Ina
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >

run backup (maintenance plan) from Batch (.bat) file

What is the method to execute backups from batch (.bat) files on the server running SQL Server. I have tried the sqlmaint command - doesn't seem to execute, looked into the xp_sqlmaint with no luck. I'm sure the problem lies in my lack of DOS batch programming skills. If anyone has an example of a batch file that executes a backup would you mind sharing. thanksHave you tried using OSQL from within a batch file?|||just stumbled onto that option this morning. I'm not much of a SQL guru, tring to set up some backups of DB contained in SQL from an COTS package. Do you know how this would all fit together? It appears I would create a file with (possiblely) the SQLMAINT (statement and parms) contained within. Then execute it from within the OSQL statement. Does this sound like I'm on the right track? Do you use the OSQL statement in any .BAT files? I would love to see an example of a (.BAT & sqlmaint) file that I could mimic the context of.|||personally I do not use maintenance plans. I script out all of my maintenance using BACKUP and DBCC t-sql statements. And yu can probably do this with osql in a batch file.

Why may I ask are you taking this approach? Do you need to schedule these to run at a specific time? If so, why not use a sql server agent job?|||I want to have it executed by the server backup software right before the backup system: tivoli, TSM, (not sure of the specific tool) runs. I also want to include some file ZIPs and versioning (file rolls - moves - to specific folders) to mimic the backup/DR process used for other products on the server (a comprehebnsive backup/recovery porocess used for our COTS tools)|||you can use jobs in the sql server agent to do all of that. I would go to SQL Server books Online-->Contents-->Administering SQL Server--> Automating Administrative Tasks-->Implimenting Jobs and do some reading.

you may also want to read xp_cmdshell for the file copies|||thanks I will...I just can't believe that there isn't a fairly easy way to do it extrnally so that I can leverage my existing BAT files and scheduling software (autosys)

Run As Command

I need to copy a backup file from server to server in seperate domains.
Unfortunately in my company they do not have trusted connections. So I need
to use a seperate logon for the destination server. I have tried a mapped
drive that is mapped as a valid logon in thatdomain with no luck, I get
"cannot find the drive specified." Is there a way thorugh sql server to run
a
job or a copy as something different than the SQL Agent account. FYI, the
logon that I use to logon to the other domain cannot be registered as a logo
n
to SQL server bacause SQL Server cannot see that domain. Any suggestions?Not sure of all the details on the mapped drive - you had no
luck where? Can you create a mapped drive and access it from
your domain with a particular login and password?
If you have that, you could execute net use to map a drive
and specify a domain\user and password. Then copy the file
to that mapped drive. Then delete the mapped drive when
done.
-Sue
On Mon, 21 May 2007 09:11:00 -0700, Rick
<Rick@.discussions.microsoft.com> wrote:

>I need to copy a backup file from server to server in seperate domains.
>Unfortunately in my company they do not have trusted connections. So I need
>to use a seperate logon for the destination server. I have tried a mapped
>drive that is mapped as a valid logon in thatdomain with no luck, I get
>"cannot find the drive specified." Is there a way thorugh sql server to run
a
>job or a copy as something different than the SQL Agent account. FYI, the
>logon that I use to logon to the other domain cannot be registered as a log
on
>to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||Rick
here is how you can do it
SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
/user:XXXX\srdev_db_backup'
EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
where v: is the share name
dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
XXXX\srdev_db_backup : is the user login on the destination server
if you are using BACKUP DATABASE command to backup the database then
issue the above command first and then backup up the database on to v:
hope this helps you
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>I need to copy a backup file from server to server in seperate domains.
> Unfortunately in my company they do not have trusted connections. So I
> need
> to use a seperate logon for the destination server. I have tried a mapped
> drive that is mapped as a valid logon in thatdomain with no luck, I get
> "cannot find the drive specified." Is there a way thorugh sql server to
> run a
> job or a copy as something different than the SQL Agent account. FYI, the
> logon that I use to logon to the other domain cannot be registered as a
> logon
> to SQL server bacause SQL Server cannot see that domain. Any suggestions?|||This is what I came up with after a lot of trial and error
Declare @.VCHCOMMAND Char (150),
@.CMD Char (50),
@.COPY Char (50)
Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples'
Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
EXEC MASTER..XP_CMDSHELL @.CMD
, but I get the following error:
L: has a remembered connection to \\172.17.9.67\backup. Do you
want to overwrite the remembered connection? (Y/N) [Y]:
No valid response was provided.
NULL
Any Suggestions?
"vt" wrote:

> Rick
> here is how you can do it
> SELECT @.VCHCOMMAND = 'net use v: \\10.1.97.150\sqlbackups$\SRDEV dbbackup
> /user:XXXX\srdev_db_backup'
> EXEC @.IRESULT = MASTER..XP_CMDSHELL @.VCHCOMMAND, NO_OUTPUT
> where v: is the share name
> dbbackup : password to connect \\10.1.97.150\sqlbackups$\SRDEV
> XXXX\srdev_db_backup : is the user login on the destination server
> if you are using BACKUP DATABASE command to backup the database then
> issue the above command first and then backup up the database on to v:
> hope this helps you
>
>
> Regards
> VT
> Knowledge is power, share it...
> http://oneplace4sql.blogspot.com/
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:042AEE4F-6267-4EA8-8BB6-4609B32409DB@.microsoft.com...
>
>|||> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
First, remove the persistent connection:
EXEC master..xp_cmdshell 'net use L: /delete'
Then try:
--map drive
EXEC master..xp_cmdshell 'net use L: \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
--unmap drive
EXEC master..xp_cmdshell 'net use L: /delete'
I might be wrong but I think you could also do without the drive letter:
--establish security credentials
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup peoples123
/user:sigueqa\rpeoples /persistent:no'
--copy
EXEC master..xp_cmdshell 'copy \\server2\Backup\DB\SIRETGUE.Bak
\\172.9.17.99\Backup\DB'
--unmap
EXEC master..xp_cmdshell 'net use \\172.9.17.99\Backup /delete'
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:6D7669E9-390B-46D4-8EAB-F03196899C3B@.microsoft.com...[vbcol=seagreen]
> This is what I came up with after a lot of trial and error
> Declare @.VCHCOMMAND Char (150),
> @.CMD Char (50),
> @.COPY Char (50)
> Set @.VCHCOMMAND = 'net use L: *\\172.9.17.99\Backup peoples123
> /user:sigueqa\rpeoples'
> Set @.COPY = 'copy \\server2\Backup\DB\SIRETGUE.Bak L:\DB'
> Set @.CMD = @.VCHCOMMAND + ' Y ' + @.COPY
> EXEC MASTER..XP_CMDSHELL @.CMD
>
> , but I get the following error:
> L: has a remembered connection to \\172.17.9.67\backup. Do you
> want to overwrite the remembered connection? (Y/N) [Y]:
> No valid response was provided.
> NULL
> Any Suggestions?
> "vt" wrote:
>