In one of my task I have to apply the latest SQL Server Service Pack /
Patch / Hot fix on all the SQL Server Boxes in our client environment. There
are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and 2000
running on Window NT, 2000 and 2003 Platforms. We have been given deadline of
one month to apply the SQL Server patches access all the box.
It is also being advised to rollout this task Quarterly on every year
as the Service pack / Patch are being released.
Hereby I am looking for some advise on
1. The best process to follow on for this task:
2. Automating this task:
Is anyone used any third party tools like "Opsware" to automate this
process?
3. Issues and Risks:
Issues and Risks which to be faced as part of this task .
4. Misc:
Any sort of information that would help with this task.
Thanks
S SIVAPRASAD
Have you considered Microsoft Systems Managment Server. It's really designed
just for change and configuration management. It's a great tool for applying
service packs & hotfixes accross the enterprise.
See http://www.microsoft.com/smserver/ev.../default.mspx.
Mike
This posting is provided "AS IS" with no warranties, and confers no rights."
When you include scripts, it should also include "Use of included script
samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in
message news:1C8805CA-3E70-452E-944A-30419598693D@.microsoft.com...
> In one of my task I have to apply the latest SQL Server Service Pack /
> Patch / Hot fix on all the SQL Server Boxes in our client environment.
> There
> are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and
> 2000
> running on Window NT, 2000 and 2003 Platforms. We have been given deadline
> of
> one month to apply the SQL Server patches access all the box.
> It is also being advised to rollout this task Quarterly on every year
> as the Service pack / Patch are being released.
> Hereby I am looking for some advise on
> 1. The best process to follow on for this task:
> 2. Automating this task:
> Is anyone used any third party tools like "Opsware" to automate this
> process?
>
> 3. Issues and Risks:
> Issues and Risks which to be faced as part of this task .
>
> 4. Misc:
> Any sort of information that would help with this task.
> --
> Thanks
> S SIVAPRASAD
sql
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
Automatic installation
I have a small desktop application that is distributed in CD.
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?
An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?
hi,
AFAIK, this kind of install requires you to launch the sqlexpr.exe boostrap installer of SQLExpress... you can eventually provide the required parameters as command line params or using an ini file... personally I do prefer the command line options as no sensible information is required to be stored in the provided ini file...
or, if your app is designed accordingly, you could even use ClickOnce deployment..
regards
|||Also check out the FAQ at the top of this forum, one of the questions answered is about configuring SQL Express during installation and covers the most common of the required parameters that Andrea mentions in what I hope is a more friendly and understandable way than BOL does.
Mike
|||Thank you, I will check your advice.An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||
hi,
personally I do not think there're DBMS out there not requiring any kind of periodical administration.. even Access requires, from time to time, to compact read/write databases.. and so does SQL Server/SQLExpress...
you can "automate" usual admin tasks as some sort of "maintenance plans" (not supported feature on SQLExpress becouse of the absence of the SQL Agent) via other schedulers, like the builtin OS scheduler to execute a Transact-SQL script(s) to reindex user's database(s), check db integrity and the like and, obviously, define a backup strategy to protect against disaster scenarios...
you can perhaps have a look at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29 to get some start ideas..
personally I provide a set of Trasact-SQL scripts to be scheduled via the native OS scheduler to automate these tasks with an added feature.. I do provide a CLR assembly (you can get for free from my web site, amDBObj) to be "imported" as a CLR stored procedure to provide some sort of Database Mail missing feature, so that those scripts can notify me, the admins/it stuff that the task has failed...
regards
Automatic installation
I have a small desktop application that is distributed in CD.
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?
An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?
hi,
AFAIK, this kind of install requires you to launch the sqlexpr.exe boostrap installer of SQLExpress... you can eventually provide the required parameters as command line params or using an ini file... personally I do prefer the command line options as no sensible information is required to be stored in the provided ini file...
or, if your app is designed accordingly, you could even use ClickOnce deployment..
regards
|||Also check out the FAQ at the top of this forum, one of the questions answered is about configuring SQL Express during installation and covers the most common of the required parameters that Andrea mentions in what I hope is a more friendly and understandable way than BOL does.
Mike
|||Thank you, I will check your advice.An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||
hi,
personally I do not think there're DBMS out there not requiring any kind of periodical administration.. even Access requires, from time to time, to compact read/write databases.. and so does SQL Server/SQLExpress...
you can "automate" usual admin tasks as some sort of "maintenance plans" (not supported feature on SQLExpress becouse of the absence of the SQL Agent) via other schedulers, like the builtin OS scheduler to execute a Transact-SQL script(s) to reindex user's database(s), check db integrity and the like and, obviously, define a backup strategy to protect against disaster scenarios...
you can perhaps have a look at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29 to get some start ideas..
personally I provide a set of Trasact-SQL scripts to be scheduled via the native OS scheduler to automate these tasks with an added feature.. I do provide a CLR assembly (you can get for free from my web site, amDBObj) to be "imported" as a CLR stored procedure to provide some sort of Database Mail missing feature, so that those scripts can notify me, the admins/it stuff that the task has failed...
regards
Sunday, March 11, 2012
Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005
Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.repla ce("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
([url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover +.net+1.1&rnum=10&hl=en#cc09960826a31a94)[/url].
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
Michael
Hi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.repla ce("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
([url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover +.net+1.1&rnum=10&hl=en#cc09960826a31a94)[/url].
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
Michael
Hi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005
Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.replace("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
(http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover+.net+1.1&rnum=10&hl=en#cc09960826a31a94).
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
MichaelHi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.replace("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
(http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover+.net+1.1&rnum=10&hl=en#cc09960826a31a94).
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
MichaelHi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
Automatic client redirect in SQL Server 2005
Will this work with replication too. I see it with a mirror database, but could it work if I was keeping two copies of the database and wanted to transparently reroute the connection to a surviving server?We will support such redirection on the publication database, when DB mirroring becomes a supported feature later.|||* If the publication DB is mirrored, client can do automatic redirection between the database on principal server and database on mirror server. BOL has a topic called "Replication and Database Mirroring" on this. As Li said, it will be supported once DB mirroring becomes supported feature.
* If you question is to do automatic redirection between publication DB and subscription DB, the answer is NO for SQL 2005.
Thursday, March 8, 2012
Automated client report rendering
My company would like to write an app that does on-demand rendering of
certain reports. Is there a .NET client report rendering library that
is not dependent on IIS (as the SOAP/URL apis are), nor WinForms (as
the ReportViewer is)?
Thanks,
EvaAfter some research I've found one third-party product called the RDL
Project that renders RDL into HTML, PDF, and some other formats. It's
found at http://www.fyiReporting.com. I played with it, and its version
1.0.1 can't yet parse a report that I built in VS2005, although I think
it could if I removed a few unsupported things from the report and if I
studied its code a bit more (documentation seems lacking).
Hope this post helps someone else, and of course if you know of any
other options please let me know!
certain reports. Is there a .NET client report rendering library that
is not dependent on IIS (as the SOAP/URL apis are), nor WinForms (as
the ReportViewer is)?
Thanks,
EvaAfter some research I've found one third-party product called the RDL
Project that renders RDL into HTML, PDF, and some other formats. It's
found at http://www.fyiReporting.com. I played with it, and its version
1.0.1 can't yet parse a report that I built in VS2005, although I think
it could if I removed a few unsupported things from the report and if I
studied its code a bit more (documentation seems lacking).
Hope this post helps someone else, and of course if you know of any
other options please let me know!
Saturday, February 25, 2012
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
>
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
>
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
>
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
>
Subscribe to:
Comments (Atom)