Hi just wondering if anyone has any ideas what would cause an automatic
backup to fail when the manual backup works fine? There seems to be plenty
of disk space. Also there does not to be a log created showing any error
messages when the backup fails. The autobackup was setup in Enterprise
manager under Management/jobs. Also the SQL Server Agent is running. Thanks.
Paul G
Software engineer.
Is the backup being sent to a remote share or machine? If so make sure the
account SQL Server is running under has the proper rights to that share.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> Hi just wondering if anyone has any ideas what would cause an automatic
> backup to fail when the manual backup works fine? There seems to be
> plenty
> of disk space. Also there does not to be a log created showing any error
> messages when the backup fails. The autobackup was setup in Enterprise
> manager under Management/jobs. Also the SQL Server Agent is running.
> Thanks.
> --
> Paul G
> Software engineer.
|||the backup should be just going to the same machine as the database in the
directory c:\program files\microsoft sql server\BACKUP, thanks for the
information will check out the rights/permissions.
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Is the backup being sent to a remote share or machine? If so make sure the
> account SQL Server is running under has the proper rights to that share.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
>
>
|||Backing up to the same drive the database is on is a bad idea. If you loose
the drive you loose both the db and the backups. Have a look in the SQL
Error logs and in the job history and see if there is more info to go on.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...[vbcol=seagreen]
> the backup should be just going to the same machine as the database in the
> directory c:\program files\microsoft sql server\BACKUP, thanks for the
> information will check out the rights/permissions.
> --
> Paul G
> Software engineer.
>
> "Andrew J. Kelly" wrote:
|||ok thanks for the additional information. We have a seperate backup of the
drive that occures daily but will probably back up to an external device in
the near future.
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Backing up to the same drive the database is on is a bad idea. If you loose
> the drive you loose both the db and the backups. Have a look in the SQL
> Error logs and in the job history and see if there is more info to go on.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
>
>
Showing posts with label ideas. Show all posts
Showing posts with label ideas. Show all posts
Sunday, March 11, 2012
automatic dbase backup fails
Hi just wondering if anyone has any ideas what would cause an automatic
backup to fail when the manual backup works fine? There seems to be plenty
of disk space. Also there does not to be a log created showing any error
messages when the backup fails. The autobackup was setup in Enterprise
manager under Management/jobs. Also the SQL Server Agent is running. Thanks.
--
Paul G
Software engineer.Is the backup being sent to a remote share or machine? If so make sure the
account SQL Server is running under has the proper rights to that share.
--
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> Hi just wondering if anyone has any ideas what would cause an automatic
> backup to fail when the manual backup works fine? There seems to be
> plenty
> of disk space. Also there does not to be a log created showing any error
> messages when the backup fails. The autobackup was setup in Enterprise
> manager under Management/jobs. Also the SQL Server Agent is running.
> Thanks.
> --
> Paul G
> Software engineer.|||the backup should be just going to the same machine as the database in the
directory c:\program files\microsoft sql server\BACKUP, thanks for the
information will check out the rights/permissions.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Is the backup being sent to a remote share or machine? If so make sure the
> account SQL Server is running under has the proper rights to that share.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> > Hi just wondering if anyone has any ideas what would cause an automatic
> > backup to fail when the manual backup works fine? There seems to be
> > plenty
> > of disk space. Also there does not to be a log created showing any error
> > messages when the backup fails. The autobackup was setup in Enterprise
> > manager under Management/jobs. Also the SQL Server Agent is running.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Backing up to the same drive the database is on is a bad idea. If you loose
the drive you loose both the db and the backups. Have a look in the SQL
Error logs and in the job history and see if there is more info to go on.
--
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
> the backup should be just going to the same machine as the database in the
> directory c:\program files\microsoft sql server\BACKUP, thanks for the
> information will check out the rights/permissions.
> --
> Paul G
> Software engineer.
>
> "Andrew J. Kelly" wrote:
>> Is the backup being sent to a remote share or machine? If so make sure
>> the
>> account SQL Server is running under has the proper rights to that share.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
>> > Hi just wondering if anyone has any ideas what would cause an automatic
>> > backup to fail when the manual backup works fine? There seems to be
>> > plenty
>> > of disk space. Also there does not to be a log created showing any
>> > error
>> > messages when the backup fails. The autobackup was setup in Enterprise
>> > manager under Management/jobs. Also the SQL Server Agent is running.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. We have a seperate backup of the
drive that occures daily but will probably back up to an external device in
the near future.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Backing up to the same drive the database is on is a bad idea. If you loose
> the drive you loose both the db and the backups. Have a look in the SQL
> Error logs and in the job history and see if there is more info to go on.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
> > the backup should be just going to the same machine as the database in the
> > directory c:\program files\microsoft sql server\BACKUP, thanks for the
> > information will check out the rights/permissions.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is the backup being sent to a remote share or machine? If so make sure
> >> the
> >> account SQL Server is running under has the proper rights to that share.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> >> > Hi just wondering if anyone has any ideas what would cause an automatic
> >> > backup to fail when the manual backup works fine? There seems to be
> >> > plenty
> >> > of disk space. Also there does not to be a log created showing any
> >> > error
> >> > messages when the backup fails. The autobackup was setup in Enterprise
> >> > manager under Management/jobs. Also the SQL Server Agent is running.
> >> > Thanks.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>
backup to fail when the manual backup works fine? There seems to be plenty
of disk space. Also there does not to be a log created showing any error
messages when the backup fails. The autobackup was setup in Enterprise
manager under Management/jobs. Also the SQL Server Agent is running. Thanks.
--
Paul G
Software engineer.Is the backup being sent to a remote share or machine? If so make sure the
account SQL Server is running under has the proper rights to that share.
--
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> Hi just wondering if anyone has any ideas what would cause an automatic
> backup to fail when the manual backup works fine? There seems to be
> plenty
> of disk space. Also there does not to be a log created showing any error
> messages when the backup fails. The autobackup was setup in Enterprise
> manager under Management/jobs. Also the SQL Server Agent is running.
> Thanks.
> --
> Paul G
> Software engineer.|||the backup should be just going to the same machine as the database in the
directory c:\program files\microsoft sql server\BACKUP, thanks for the
information will check out the rights/permissions.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Is the backup being sent to a remote share or machine? If so make sure the
> account SQL Server is running under has the proper rights to that share.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> > Hi just wondering if anyone has any ideas what would cause an automatic
> > backup to fail when the manual backup works fine? There seems to be
> > plenty
> > of disk space. Also there does not to be a log created showing any error
> > messages when the backup fails. The autobackup was setup in Enterprise
> > manager under Management/jobs. Also the SQL Server Agent is running.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Backing up to the same drive the database is on is a bad idea. If you loose
the drive you loose both the db and the backups. Have a look in the SQL
Error logs and in the job history and see if there is more info to go on.
--
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
> the backup should be just going to the same machine as the database in the
> directory c:\program files\microsoft sql server\BACKUP, thanks for the
> information will check out the rights/permissions.
> --
> Paul G
> Software engineer.
>
> "Andrew J. Kelly" wrote:
>> Is the backup being sent to a remote share or machine? If so make sure
>> the
>> account SQL Server is running under has the proper rights to that share.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
>> > Hi just wondering if anyone has any ideas what would cause an automatic
>> > backup to fail when the manual backup works fine? There seems to be
>> > plenty
>> > of disk space. Also there does not to be a log created showing any
>> > error
>> > messages when the backup fails. The autobackup was setup in Enterprise
>> > manager under Management/jobs. Also the SQL Server Agent is running.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>|||ok thanks for the additional information. We have a seperate backup of the
drive that occures daily but will probably back up to an external device in
the near future.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Backing up to the same drive the database is on is a bad idea. If you loose
> the drive you loose both the db and the backups. Have a look in the SQL
> Error logs and in the job history and see if there is more info to go on.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
> > the backup should be just going to the same machine as the database in the
> > directory c:\program files\microsoft sql server\BACKUP, thanks for the
> > information will check out the rights/permissions.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Is the backup being sent to a remote share or machine? If so make sure
> >> the
> >> account SQL Server is running under has the proper rights to that share.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> >> > Hi just wondering if anyone has any ideas what would cause an automatic
> >> > backup to fail when the manual backup works fine? There seems to be
> >> > plenty
> >> > of disk space. Also there does not to be a log created showing any
> >> > error
> >> > messages when the backup fails. The autobackup was setup in Enterprise
> >> > manager under Management/jobs. Also the SQL Server Agent is running.
> >> > Thanks.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>
automatic dbase backup fails
Hi just wondering if anyone has any ideas what would cause an automatic
backup to fail when the manual backup works fine? There seems to be plenty
of disk space. Also there does not to be a log created showing any error
messages when the backup fails. The autobackup was setup in Enterprise
manager under Management/jobs. Also the SQL Server Agent is running. Thank
s.
--
Paul G
Software engineer.Is the backup being sent to a remote share or machine? If so make sure the
account SQL Server is running under has the proper rights to that share.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> Hi just wondering if anyone has any ideas what would cause an automatic
> backup to fail when the manual backup works fine? There seems to be
> plenty
> of disk space. Also there does not to be a log created showing any error
> messages when the backup fails. The autobackup was setup in Enterprise
> manager under Management/jobs. Also the SQL Server Agent is running.
> Thanks.
> --
> Paul G
> Software engineer.|||the backup should be just going to the same machine as the database in the
directory c:\program files\microsoft sql server\BACKUP, thanks for the
information will check out the rights/permissions.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Is the backup being sent to a remote share or machine? If so make sure th
e
> account SQL Server is running under has the proper rights to that share.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
>
>|||Backing up to the same drive the database is on is a bad idea. If you loose
the drive you loose both the db and the backups. Have a look in the SQL
Error logs and in the job history and see if there is more info to go on.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...[vbcol=seagreen]
> the backup should be just going to the same machine as the database in the
> directory c:\program files\microsoft sql server\BACKUP, thanks for the
> information will check out the rights/permissions.
> --
> Paul G
> Software engineer.
>
> "Andrew J. Kelly" wrote:
>|||ok thanks for the additional information. We have a seperate backup of the
drive that occures daily but will probably back up to an external device in
the near future.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Backing up to the same drive the database is on is a bad idea. If you loo
se
> the drive you loose both the db and the backups. Have a look in the SQL
> Error logs and in the job history and see if there is more info to go on.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
>
>
backup to fail when the manual backup works fine? There seems to be plenty
of disk space. Also there does not to be a log created showing any error
messages when the backup fails. The autobackup was setup in Enterprise
manager under Management/jobs. Also the SQL Server Agent is running. Thank
s.
--
Paul G
Software engineer.Is the backup being sent to a remote share or machine? If so make sure the
account SQL Server is running under has the proper rights to that share.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
> Hi just wondering if anyone has any ideas what would cause an automatic
> backup to fail when the manual backup works fine? There seems to be
> plenty
> of disk space. Also there does not to be a log created showing any error
> messages when the backup fails. The autobackup was setup in Enterprise
> manager under Management/jobs. Also the SQL Server Agent is running.
> Thanks.
> --
> Paul G
> Software engineer.|||the backup should be just going to the same machine as the database in the
directory c:\program files\microsoft sql server\BACKUP, thanks for the
information will check out the rights/permissions.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Is the backup being sent to a remote share or machine? If so make sure th
e
> account SQL Server is running under has the proper rights to that share.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E523A95-0F6E-403B-904B-B8F134A86055@.microsoft.com...
>
>|||Backing up to the same drive the database is on is a bad idea. If you loose
the drive you loose both the db and the backups. Have a look in the SQL
Error logs and in the job history and see if there is more info to go on.
Andrew J. Kelly SQL MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...[vbcol=seagreen]
> the backup should be just going to the same machine as the database in the
> directory c:\program files\microsoft sql server\BACKUP, thanks for the
> information will check out the rights/permissions.
> --
> Paul G
> Software engineer.
>
> "Andrew J. Kelly" wrote:
>|||ok thanks for the additional information. We have a seperate backup of the
drive that occures daily but will probably back up to an external device in
the near future.
--
Paul G
Software engineer.
"Andrew J. Kelly" wrote:
> Backing up to the same drive the database is on is a bad idea. If you loo
se
> the drive you loose both the db and the backups. Have a look in the SQL
> Error logs and in the job history and see if there is more info to go on.
> --
> Andrew J. Kelly SQL MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E621A920-9E90-4401-B759-1806B6F03F8B@.microsoft.com...
>
>
Wednesday, March 7, 2012
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the mo
st recent database
backup from the database. Save it in a variable, and use that to construct y
our RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the mo
st recent database
backup from the database. Save it in a variable, and use that to construct y
our RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thx
Are you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.
|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thx
Are you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.
|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
automate creation of staging database
any ideas on how to automate the production of a staging db with day old
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>> RESTORE DATABASE TargetDB
>> FROM DISK = 'C:\Backups\SourceDB.bak'
>> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
>> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
>> See the Books Online for BACKUP/RESTORE details.
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
production data? right now i do it by hand, taking a production db backup
and restore this on top of the staging version. im not a dba so go slow
please ;)
thxAre you simply looking for a way to automate this backup/restore process
instead of using the GUI? In that case, you can create and schedule a daily
SQL Agent job Transact-SQL job step. Sample script below. If you already
schedule a nightly production backup for recovery purposes, you can skip
this backup and use that backup instead. That has the side benefit of
testing your production backup.
BACKUP DATABASE SourceDB
TO DISK = 'C:\Backups\SourceDB.bak'
RESTORE DATABASE TargetDB
FROM DISK = 'C:\Backups\SourceDB.bak'
WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
See the Books Online for BACKUP/RESTORE details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742CBDD3E732usenetjb@.207.115.17.102...
> any ideas on how to automate the production of a staging db with day old
> production data? right now i do it by hand, taking a production db backup
> and restore this on top of the staging version. im not a dba so go slow
> please ;)
>
> thx|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
> RESTORE DATABASE TargetDB
> FROM DISK = 'C:\Backups\SourceDB.bak'
> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
> See the Books Online for BACKUP/RESTORE details.
>
yeah, well the thing is that the scheduled backup's file name changes with
each backup, so a simple script may not do the trick.|||How about doing some SELECT from the backup history tables to pick up the most recent database
backup from the database. Save it in a variable, and use that to construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"usenetjb" <usenet.20.jimbo-black@.antichef.net> wrote in message
news:Xns9742DB7C5225Dusenetjb@.207.115.17.102...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
> news:#6kembmEGHA.208@.tk2msftngp13.phx.gbl:
>> RESTORE DATABASE TargetDB
>> FROM DISK = 'C:\Backups\SourceDB.bak'
>> WITH MOVE 'SourceDB' TO 'D:\DataFiles\TargetDB.mdf',
>> MOVE 'TargetDB_Log' TO 'E:\LogFiles\TargetDB_Log.ldf'
>> See the Books Online for BACKUP/RESTORE details.
> yeah, well the thing is that the scheduled backup's file name changes with
> each backup, so a simple script may not do the trick.
Subscribe to:
Posts (Atom)