Automating Backups

Is there a way to automate backups through MSDE? I have been trying to find
a way I can automate a backup of a SQL database through a network. Please
help!
Thanks in advance
You can automate backups using some OSQL scripts and the scheduler on the
machine where MSDE is installed.
Jim
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:74FB91B8-1111-49F5-A25C-F14E8EFC84FC@.microsoft.com...
> Is there a way to automate backups through MSDE? I have been trying to
> find
> a way I can automate a backup of a SQL database through a network. Please
> help!
> Thanks in advance
|||I kind of figured that but what would be the syntax to connect to the remote
PC?
"Jim Young" wrote:

> You can automate backups using some OSQL scripts and the scheduler on the
> machine where MSDE is installed.
> Jim
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:74FB91B8-1111-49F5-A25C-F14E8EFC84FC@.microsoft.com...
>
>
|||hi Paul,
Paul wrote:[vbcol=seagreen]
> I kind of figured that but what would be the syntax to connect to the
> remote PC?
> "Jim Young" wrote:
you can even use the SQL Server Agent to provide this kind of feature..
you have to create a Job and relative schedule, and add a job step including
a T-SQL command like
BACKUP DATABASE ...
if you have to access network shares, the account running SQL Server agent
will require enought privileges on those shares..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Ok ... I must be sleep deprived or just plain not too smart. I have access
to the PC I am trying to connect to. DO I have to add a mapped drive? I
have been able to backup to any other driver letter but my mapped drive.
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> you can even use the SQL Server Agent to provide this kind of feature..
> you have to create a Job and relative schedule, and add a job step including
> a T-SQL command like
> BACKUP DATABASE ...
> if you have to access network shares, the account running SQL Server agent
> will require enought privileges on those shares..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Ok ... I must be sleep deprived or just plain not too smart. I have
> access to the PC I am trying to connect to. DO I have to add a
> mapped drive? I have been able to backup to any other driver letter
> but my mapped drive.
>
I use UNC notation...
just
BACKUP DATABASE [a]
TO DISK = N'\\Roberto\documenti\a.bak' WITH INIT
, NAME = N'a BackUp'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thankyou that was a big help. I did have kind of a related question. I
found a knowledge base documant for writing a backup script. It is 241397.
I modified the text to fit my database and moved in the stored procedures etc
but now I get an error "could not find stored procedure
'dinerware.dbo.xp_sqlagent_is_starting'. When I try to add that stored
procedure it tell me that "sp_addextendedproc can only be executed by the
master database. Would you know what I am doing wrong. Sorry if thses
questions are dumb. It has been about a 24 hour strestch for me.
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> I use UNC notation...
> just
> BACKUP DATABASE [a]
> TO DISK = N'\\Roberto\documenti\a.bak' WITH INIT
> , NAME = N'a BackUp'
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Thankyou that was a big help. I did have kind of a related question.
> I found a knowledge base documant for writing a backup script. It is
> 241397. I modified the text to fit my database and moved in the
> stored procedures etc but now I get an error "could not find stored
> procedure 'dinerware.dbo.xp_sqlagent_is_starting'. When I try to add
> that stored procedure it tell me that "sp_addextendedproc can only be
> executed by the master database. Would you know what I am doing
> wrong. Sorry if thses questions are dumb. It has been about a 24
> hour strestch for me.
>
can you please post the script?
BTW you ca not move sp_add_jobXXX procedures (if that is what you mean) as
they reside in msdb database..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Here is the script. There is a scheduling section after. Maybe I am trying
to do this all wrong. I am not sure. The sites use MSDE but I have SQL
Server 2K developer. I need to set up a backup and restore accross a network
to another instance of MSDE for redundancy purposes.
-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @.job_name = 'myTestBackupJob',
@.enabled = 1,
@.description = 'myTestBackupJob',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE msdb TO DISK = ''c:\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG msdb TO DISK = ''c:\msdb.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'myTestBackupJob', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @.job_name = 'myTestBackupJob'
I would need this to backup a database called dinerware. Thanks again for
your help
"Andrea Montanari" wrote:

> hi Paul,
> Paul wrote:
> can you please post the script?
> BTW you ca not move sp_add_jobXXX procedures (if that is what you mean) as
> they reside in msdb database..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Paul,
Paul wrote:
> Here is the script. There is a scheduling section after. Maybe I am
> trying to do this all wrong. I am not sure. The sites use MSDE but
> I have SQL Server 2K developer. I need to set up a backup and
> restore accross a network to another instance of MSDE for redundancy
> purposes.
the script is fine but the part that perform the BACKUP LOG as msdb is in
simple recovery model so that no backup log is allowed...
the only things you have to change are
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Data', -- change step name to match your db
name
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE [database_name] TO DISK =
''c:\msdb.dat_bak''', -- change this to point
-- to your actual database
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'myTestBackupJob',
@.step_name = 'Backup msdb Log', -- change step name to match your db
name
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG [database_name] TO DISK =
''c:\msdb.log_bak''', -- change this to point
-- to your actual database log
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
but they always will reside in msdb database..
as regard the destination, you can change
TO DISK = ''c:\msdb.log_bak'''
with
TO DISK = ''\\ComputerName\Share\msdb.log_bak'''
as long as the SQL Server Agent has enought privileges (read/write) to that
share..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

automatically restarting merge replication after time out

Hello,
every few days my merge replication fails due to a network problem and
subsequent time out. How can I trigger a replication restart automatically,
or at a certain time of day?
Bill,
you can change the workflow properties on your merge agent's job so that the
third step on completion goes to the first step.
Alternatively, you could avoid continuous replication and have the merge
agent run on a schedule.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

automatic printing from trigger

Hi,
I am new to Sql Server 2000. I am looking for help on how to automatically generate a report to a network printer after a record is added/updated/changed in a table. How should I do this? With a Trigger? With DTS?
Please help.With a Trigger ...|||Thanks for the info. I think I was unclear in my initial question and would like to know what are the exact steps needed to perform this function? I don't know how to create a trigger to do this and would be looking for an example of some sort or instructions for the code.

Any help greatly appreciated.|||What about sp_OA... procedures, write component in any language
and use these SP for communication.

Export with DTS or SQLDMO objects.

Multitask temp tables with :
1.X=OBJECT_ID('temp..#temptbl')
2.Send X to object
3.Get temp name
use tempdb
GO
Y=OBJECT_NAME(X)

Too much work to be done ...
MSSQLSERVER2K has native support for e-mail and pager.

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
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
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
Irfan
In SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk =
'\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
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
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

AutoMatic Configuration Differential Backup

hello every one
i am using Microsoft SQL Server 2000
Enterprise Edition and running four data bases on
server.All on Local Areal Network the size of every data
bases is 1 GB and changing occour every Second and minute
so i want to implement Differential Backup after every One
Hours using SQL Server agent and schedule task can any
body guide me properly and any tutorial like web site that
help me step by step ......Documentation etc...
Second i also want replicated to another machine on LAN
thank
IrfanIn SQL Enterprise Manager, select SQL Agent->jobs, New job
Give the job a name and go to steps -> new Step... Choose t-sql step...
The command should be
backup database yourdbname to disk ='\\servername\sharename\backupfilename.bkp' with init,differential
OK this and add similar steps for each of the other databases.
Then go to schedule and choose a schedule for this to run...
Save it and see if it works...
More details can be found in SQL Server Books On Line...
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
"Irfan" <mirfanaslam@.hotmail.com> wrote in message
news:0ca401c48b8c$9c5f9a90$a301280a@.phx.gbl...
> hello every one
> i am using Microsoft SQL Server 2000
> Enterprise Edition and running four data bases on
> server.All on Local Areal Network the size of every data
> bases is 1 GB and changing occour every Second and minute
> so i want to implement Differential Backup after every One
> Hours using SQL Server agent and schedule task can any
> body guide me properly and any tutorial like web site that
> help me step by step ......Documentation etc...
> Second i also want replicated to another machine on LAN
> thank
> Irfan
>

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatab
ase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> And how does one places the output into the table?
> Ivan
>

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan
|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
>
>
>
>
>
>
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file
|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegr oups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan
|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegr oups.com...
> And how does one places the output into the table?
> Ivan
>

Automate a restore through a backup

Hi all,
Regards SQL Server 2000.
I have a situation whereby the client takes a nightly backup of his
databases. The backups are placed on a network share. He has a laptop where
he has sql server installed and wants to update the laptop's databases from
the lastest backup of the day before.
Is there a way to automate (within enterprice manager or t-sql) the
restoration of a database through a backup file?
Thanks,
Ivan> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
Sure, you can execute a T-SQL restore script. You may need to specify the
MOVE option if you need to restore to different drives/folders than the
source database:
RESTORE DATABASE MyDatabase
FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
WITH
MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
The restore runs in the OS security context of the SQL Server service
account when performed by a sysadmin role member so the service account will
need read permissions to the network backup share. Also, you can include
the restore scripts in a SQL Agent job to facilitate execution, either
scheduled or manual.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Regards SQL Server 2000.
> I have a situation whereby the client takes a nightly backup of his
> databases. The backups are placed on a network share. He has a laptop
> where he has sql server installed and wants to update the laptop's
> databases from the lastest backup of the day before.
> Is there a way to automate (within enterprice manager or t-sql) the
> restoration of a database through a backup file?
> Thanks,
> Ivan
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
He may also want to look into straight log-shipping.
There's scripts for 'rolling your own'.
Rather than restore the full backup every night, he can restore just the
logs and put the DB into read-only mode if that'll work for him.
Even if he doesn't go that far, he can get "smarter" possibly and look in
the MSDB for the last backup set and automatically load that one.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> Regards SQL Server 2000.
>> I have a situation whereby the client takes a nightly backup of his
>> databases. The backups are placed on a network share. He has a laptop
>> where he has sql server installed and wants to update the laptop's
>> databases from the lastest backup of the day before.
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
>> Thanks,
>> Ivan
>>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schrieb im Newsbeitrag
news:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
> Sure, you can execute a T-SQL restore script. You may need to specify the
> MOVE option if you need to restore to different drives/folders than the
> source database:
> RESTORE DATABASE MyDatabase
> FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> WITH
> MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> The restore runs in the OS security context of the SQL Server service
> account when performed by a sysadmin role member so the service account
> will need read permissions to the network backup share. Also, you can
> include the restore scripts in a SQL Agent job to facilitate execution,
> either scheduled or manual.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> Regards SQL Server 2000.
>> I have a situation whereby the client takes a nightly backup of his
>> databases. The backups are placed on a network share. He has a laptop
>> where he has sql server installed and wants to update the laptop's
>> databases from the lastest backup of the day before.
>> Is there a way to automate (within enterprice manager or t-sql) the
>> restoration of a database through a backup file?
>> Thanks,
>> Ivan
>>
>
That's a great idea. One problem is that the backup job creates the backup
files with a timestamp in the name. Is there a way to exclude this or at
least get the latest backup copy automatically?
Thanks,
Ivan|||On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>
>
> >> Is there a way to automate (within enterprice manager or t-sql) the
> >> restoration of a database through a backup file?
> > Sure, you can execute a T-SQL restore script. You may need to specify the
> > MOVE option if you need to restore to different drives/folders than the
> > source database:
> > RESTORE DATABASE MyDatabase
> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
> > WITH
> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
> > The restore runs in the OS security context of the SQL Server service
> > account when performed by a sysadmin role member so the service account
> > will need read permissions to the network backup share. Also, you can
> > include the restore scripts in a SQL Agent job to facilitate execution,
> > either scheduled or manual.
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
> >> Hi all,
> >> Regards SQL Server 2000.
> >> I have a situation whereby the client takes a nightly backup of his
> >> databases. The backups are placed on a network share. He has a laptop
> >> where he has sql server installed and wants to update the laptop's
> >> databases from the lastest backup of the day before.
> >> Is there a way to automate (within enterprice manager or t-sql) the
> >> restoration of a database through a backup file?
> >> Thanks,
> >> Ivan
> That's a great idea. One problem is that the backup job creates the backup
> files with a timestamp in the name. Is there a way to exclude this or at
> least get the latest backup copy automatically?
> Thanks,
> Ivan- Hide quoted text -
> - Show quoted text -
You can rename your latest backup file to a constant file name and
restore it from this.
If you don't want to change the name of the backup file use
xp_cmdshell 'dir ' and put into a table and parse to get the name of
the latest backup file|||"M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
>> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im
>> Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>>
>>
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> > Sure, you can execute a T-SQL restore script. You may need to specify
>> > the
>> > MOVE option if you need to restore to different drives/folders than the
>> > source database:
>> > RESTORE DATABASE MyDatabase
>> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
>> > WITH
>> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
>> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
>> > The restore runs in the OS security context of the SQL Server service
>> > account when performed by a sysadmin role member so the service account
>> > will need read permissions to the network backup share. Also, you can
>> > include the restore scripts in a SQL Agent job to facilitate execution,
>> > either scheduled or manual.
>> > --
>> > Hope this helps.
>> > Dan Guzman
>> > SQL Server MVP
>> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
>> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> >> Hi all,
>> >> Regards SQL Server 2000.
>> >> I have a situation whereby the client takes a nightly backup of his
>> >> databases. The backups are placed on a network share. He has a laptop
>> >> where he has sql server installed and wants to update the laptop's
>> >> databases from the lastest backup of the day before.
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> >> Thanks,
>> >> Ivan
>> That's a great idea. One problem is that the backup job creates the
>> backup
>> files with a timestamp in the name. Is there a way to exclude this or at
>> least get the latest backup copy automatically?
>> Thanks,
>> Ivan- Hide quoted text -
>> - Show quoted text -
> You can rename your latest backup file to a constant file name and
> restore it from this.
> If you don't want to change the name of the backup file use
> xp_cmdshell 'dir ' and put into a table and parse to get the name of
> the latest backup file
>
And how does one places the output into the table?
Ivan|||> And how does one places the output into the table?
You can use INSERT...EXEC. For example:
CREATE TABLE #FileList(output_line varchar(8000))
INSERT INTO #FileList
EXEC master..xp_cmdshell 'DIR C:\Backups'
However, I think parsing the output is a bit of a kludge. You might instead
consider using a VBScript that you can include as an ActiveX script job step
or a stand-alone vbs file. Below is a VBScript example you can tweak for
your needs.
'Restore from latest backup
Option Explicit
Const BackupFolder = "C:\Backups"
Const ConnectionString = "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;Integrated Security=SSPI"
Const RestoreScriptTemplate = "RESTORE DATABASE MyDatabase FROM
DISK='$(BackupFilePath)'"
Dim oFSO, BackupFilePath
Set oFSO = CreateObject("Scripting.FileSystemObject")
BackupFilePath = GetLatestBackupFile()
RestoreDatabase ConnectionString, BackupFilePath
Function GetLatestBackupFile()
Dim oFolder, oFile, LatestFilePath, LatestFileDateCreated
Set oFolder = oFSO.GetFolder(BackupFolder)
For Each oFile In oFolder.Files
If oFile.DateCreated > LatestFileDateCreated Then
LatestFileDateCreated = oFile.DateCreated
LatestFilePath = oFile.Path
End If
Next
GetLatestBackupFile = LatestFilePath
End Function
Sub RestoreDatabase(ConnectionString, BackupFilePath)
Dim RestoreScript, connection
RestoreScript = Replace(RestoreScriptTemplate, "$(BackupFilePath)",
BackupFilePath)
Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString
connection.Execute RestoreScript
connection.Close
End Sub
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%239dlo%23qdHHA.4872@.TK2MSFTNGP03.phx.gbl...
> "M A Srinivas" <masri999@.gmail.com> schrieb im Newsbeitrag
> news:1175684363.282267.133330@.e65g2000hsc.googlegroups.com...
>> On Apr 4, 12:14 pm, "Ivan Debono" <ivanm...@.hotmail.com> wrote:
>> "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net> schrieb im
>> Newsbeitragnews:CBF1540E-BD97-436B-87DF-9FE24BD4EA6F@.microsoft.com...
>>
>>
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> > Sure, you can execute a T-SQL restore script. You may need to specify
>> > the
>> > MOVE option if you need to restore to different drives/folders than
>> > the
>> > source database:
>> > RESTORE DATABASE MyDatabase
>> > FROM DISK='\\BackupServer\BackupShare\MyDatabase.bak'
>> > WITH
>> > MOVE 'MyDatabase' TO 'C:\DataFiles\MyDatabase.mdf',
>> > MOVE 'MyDatabase_Log' TO 'C:\LogFiles\MyDatabase_Log.ldf'
>> > The restore runs in the OS security context of the SQL Server service
>> > account when performed by a sysadmin role member so the service
>> > account
>> > will need read permissions to the network backup share. Also, you can
>> > include the restore scripts in a SQL Agent job to facilitate
>> > execution,
>> > either scheduled or manual.
>> > --
>> > Hope this helps.
>> > Dan Guzman
>> > SQL Server MVP
>> > "Ivan Debono" <ivanm...@.hotmail.com> wrote in message
>> >news:eNh3EBedHHA.4188@.TK2MSFTNGP02.phx.gbl...
>> >> Hi all,
>> >> Regards SQL Server 2000.
>> >> I have a situation whereby the client takes a nightly backup of his
>> >> databases. The backups are placed on a network share. He has a laptop
>> >> where he has sql server installed and wants to update the laptop's
>> >> databases from the lastest backup of the day before.
>> >> Is there a way to automate (within enterprice manager or t-sql) the
>> >> restoration of a database through a backup file?
>> >> Thanks,
>> >> Ivan
>> That's a great idea. One problem is that the backup job creates the
>> backup
>> files with a timestamp in the name. Is there a way to exclude this or at
>> least get the latest backup copy automatically?
>> Thanks,
>> Ivan- Hide quoted text -
>> - Show quoted text -
>> You can rename your latest backup file to a constant file name and
>> restore it from this.
>> If you don't want to change the name of the backup file use
>> xp_cmdshell 'dir ' and put into a table and parse to get the name of
>> the latest backup file
> And how does one places the output into the table?
> Ivan
>

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated
.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:

> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.
go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...
|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:

> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>
|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.

Auto starting the sqlserver service

Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.