restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.Yes you can run this using a job in SQL Agent and you can name the name using
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday,
I do this as a VBScript that is called by a SQL Agent job but you could use a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) so
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:
> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.
Yes you can run this using a job in SQL Agent and you can name the name using
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday,
I do this as a VBScript that is called by a SQL Agent job but you could use a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) so
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:

> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

restore db daily with a job but BAK file is diff

i have to restore database from production bakup to development
everyday, there is a program which does this bakup and names the file
like PROD_20070523.BAK based on the date.
is there any way to run this with SQL agent and make sure everytime
the current file is backed up. can i rename the file to a different
name everyday automatically.
Also this file is a zip file so can i unzip this automatically.Yes you can run this using a job in SQL Agent and you can name the name usin
g
a datetime stamp. I do all my backups this way. Then you can run code to
delete the older files. If you want to copy then to another server everyday
,
I do this as a VBScript that is called by a SQL Agent job but you could use
a
DOS copy command executed by xp_cmdshell as well. Many Zip programs have a
command line interface (not the one that comes with Windows XP but others) s
o
you could script this as well via xp_cmdshell.
"ashley.sql@.gmail.com" wrote:

> i have to restore database from production bakup to development
> everyday, there is a program which does this bakup and names the file
> like PROD_20070523.BAK based on the date.
> is there any way to run this with SQL agent and make sure everytime
> the current file is backed up. can i rename the file to a different
> name everyday automatically.
> Also this file is a zip file so can i unzip this automatically.
>

RESTORE DATABASE is terminating abnormally.

Hello,

I am getting the rather odd error below; hope you can help.

RESTORE DATABASE is terminating abnormally. File 'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.

I get this from an app our company uses to programatically restore
databases. Below are the queries we are using:

Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'

This had been working great until I made some updates to the table structure
of the db and created a new backup.

Any ideas?

TIA
JakeGitarJake (spamaintme@.oz.net) writes:

Quote:

Originally Posted by

I am getting the rather odd error below; hope you can help.
>
RESTORE DATABASE is terminating abnormally. File 'C:\Program
Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.

I would not say that this is particularly odd, given:

Quote:

Originally Posted by

'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'

You are trying to place three logical files on the same physical file. That
cannot work out well.

Seems like you need to talk with the person/vendor who wrote the app
that runs your RESTORE commands.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

RESTORE DATABASE is terminating abnormally

Hello,
I am getting the rather odd error below; hope you can help.
RESTORE DATABASE is terminating abnormally. File 'C:\Program Files\Microsoft
SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed by
'sysft_KB_SearchAssetsCatalog'(65540) and 'sysft_KB_SearchCatalog'(65537).
The WITH MOVE clause can be used to relocate one or more files.
I get this from an app our company uses to programatically restore
databases. Below are the queries we are using:
Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc'
This had been working great until I made some updates to the table structure
of the db and creeated a new backup.
Any ideas?
TIA
Jake
Can you try this?
Create database trial_44testingc
select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
RESTORE FILELISTONLY FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
RESTORE DATABASE trial_44testingc FROM disk =
'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY , MOVE
'43_trialmaster' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc1', MOVE
'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc2', MOVE
'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc3', MOVE
'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\trial_44testingc4'
"GitarJake" <spamaintme@.oz.net> wrote in message
news:j0zVh.17257$OT4.12355@.newsfe19.lga...
> Hello,
> I am getting the rather odd error below; hope you can help.
> RESTORE DATABASE is terminating abnormally. File 'C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\trial_44testingc' is claimed
> by 'sysft_KB_SearchAssetsCatalog'(65540) and
> 'sysft_KB_SearchCatalog'(65537). The WITH MOVE clause can be used to
> relocate one or more files.
> I get this from an app our company uses to programatically restore
> databases. Below are the queries we are using:
> Create database trial_44testingc
> select COUNT(*) as TOTAL from sysdatabases where name = 'trial_44testingc'
> RESTORE FILELISTONLY FROM disk =
> 'C:\HostedTrialManagement\AppSource\db\43_trialmas ter'
> RESTORE DATABASE trial_44testingc FROM disk =
> 'C:\HostedTrialManagement\AppSource\db\43_trialmas ter' WITH RECOVERY ,
> MOVE '43_trialmaster' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc.mdf', MOVE '43_trialmaster_log'
> TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc.ldf', MOVE
> 'sysft_KB_SearchCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchTicketsCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchTicketsCatalog2' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc', MOVE
> 'sysft_KB_SearchAssetsCatalog' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\trial_44testingc'
> This had been working great until I made some updates to the table
> structure of the db and creeated a new backup.
> Any ideas?
> TIA
> Jake
>
>

restore a transaction

Hello,
Someone came to me and asked me to restore a record, deleted by acident via
an ERP program. Problem is how do we restore only his record without
touching integrity of other peoples record. There are more then 20 table
involved when record is created or deleted.
MCWell I would restore the latest backup of the database to a dev enviornment
and locate that one record, then linked togeather all the information about
that record from each table and go from there to determine what can be
restored. It makes a difference on the type of data that was stored about
the record.. if that record is just a single entry or it it has several
detailed entries in a log table about that records activity in the database.
"CAMC1" wrote:
> Hello,
> Someone came to me and asked me to restore a record, deleted by acident via
> an ERP program. Problem is how do we restore only his record without
> touching integrity of other peoples record. There are more then 20 table
> involved when record is created or deleted.
> MC
>
>|||That is what I tought.
I told the user that it is faster to re-create the record via his
application the restoring 1 user transaction.
MC
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> Well I would restore the latest backup of the database to a dev
enviornment
> and locate that one record, then linked togeather all the information
about
> that record from each table and go from there to determine what can be
> restored. It makes a difference on the type of data that was stored about
> the record.. if that record is just a single entry or it it has several
> detailed entries in a log table about that records activity in the
database.
>
> "CAMC1" wrote:
> > Hello,
> > Someone came to me and asked me to restore a record, deleted by acident
via
> > an ERP program. Problem is how do we restore only his record without
> > touching integrity of other peoples record. There are more then 20 table
> > involved when record is created or deleted.
> >
> > MC
> >
> >
> >|||ABSOLUTELY!!!
Good way to handle it! It's rarely cost effective to restore just a small
amount of data.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"CAMC1" <webmaster@.ozoptics.com> wrote in message
news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
> That is what I tought.
> I told the user that it is faster to re-create the record via his
> application the restoring 1 user transaction.
> MC
> "JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
> news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
>> Well I would restore the latest backup of the database to a dev
> enviornment
>> and locate that one record, then linked togeather all the information
> about
>> that record from each table and go from there to determine what can be
>> restored. It makes a difference on the type of data that was stored
>> about
>> the record.. if that record is just a single entry or it it has several
>> detailed entries in a log table about that records activity in the
> database.
>>
>> "CAMC1" wrote:
>> > Hello,
>> > Someone came to me and asked me to restore a record, deleted by acident
> via
>> > an ERP program. Problem is how do we restore only his record without
>> > touching integrity of other peoples record. There are more then 20
>> > table
>> > involved when record is created or deleted.
>> >
>> > MC
>> >
>> >
>> >
>|||Thanks
MC
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ecgEBzDrGHA.4032@.TK2MSFTNGP03.phx.gbl...
> ABSOLUTELY!!!
> Good way to handle it! It's rarely cost effective to restore just a small
> amount of data.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "CAMC1" <webmaster@.ozoptics.com> wrote in message
> news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
> > That is what I tought.
> > I told the user that it is faster to re-create the record via his
> > application the restoring 1 user transaction.
> > MC
> >
> > "JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in
message
> > news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> >> Well I would restore the latest backup of the database to a dev
> > enviornment
> >> and locate that one record, then linked togeather all the information
> > about
> >> that record from each table and go from there to determine what can be
> >> restored. It makes a difference on the type of data that was stored
> >> about
> >> the record.. if that record is just a single entry or it it has several
> >> detailed entries in a log table about that records activity in the
> > database.
> >>
> >>
> >>
> >> "CAMC1" wrote:
> >>
> >> > Hello,
> >> > Someone came to me and asked me to restore a record, deleted by
acident
> > via
> >> > an ERP program. Problem is how do we restore only his record without
> >> > touching integrity of other peoples record. There are more then 20
> >> > table
> >> > involved when record is created or deleted.
> >> >
> >> > MC
> >> >
> >> >
> >> >
> >
> >
>|||"CAMC1" <webmaster@.ozoptics.com> wrote in message
news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
> That is what I tought.
> I told the user that it is faster to re-create the record via his
> application the restoring 1 user transaction.
You forgot the "AND NEVER DO THAT AGAIN!" part. :-)
> MC
> "JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
> news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> > Well I would restore the latest backup of the database to a dev
> enviornment
> > and locate that one record, then linked togeather all the information
> about
> > that record from each table and go from there to determine what can be
> > restored. It makes a difference on the type of data that was stored
about
> > the record.. if that record is just a single entry or it it has several
> > detailed entries in a log table about that records activity in the
> database.
> >
> >
> >
> > "CAMC1" wrote:
> >
> > > Hello,
> > > Someone came to me and asked me to restore a record, deleted by
acident
> via
> > > an ERP program. Problem is how do we restore only his record without
> > > touching integrity of other peoples record. There are more then 20
table
> > > involved when record is created or deleted.
> > >
> > > MC
> > >
> > >
> > >
>

restore a transaction

Hello,
Someone came to me and asked me to restore a record, deleted by acident via
an ERP program. Problem is how do we restore only his record without
touching integrity of other peoples record. There are more then 20 table
involved when record is created or deleted.
MCWell I would restore the latest backup of the database to a dev enviornment
and locate that one record, then linked togeather all the information about
that record from each table and go from there to determine what can be
restored. It makes a difference on the type of data that was stored about
the record.. if that record is just a single entry or it it has several
detailed entries in a log table about that records activity in the database.
"CAMC1" wrote:

> Hello,
> Someone came to me and asked me to restore a record, deleted by acident vi
a
> an ERP program. Problem is how do we restore only his record without
> touching integrity of other peoples record. There are more then 20 table
> involved when record is created or deleted.
> MC
>
>|||That is what I tought.
I told the user that it is faster to re-create the record via his
application the restoring 1 user transaction.
MC
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> Well I would restore the latest backup of the database to a dev
enviornment
> and locate that one record, then linked togeather all the information
about
> that record from each table and go from there to determine what can be
> restored. It makes a difference on the type of data that was stored about
> the record.. if that record is just a single entry or it it has several
> detailed entries in a log table about that records activity in the
database.[vbcol=seagreen]
>
> "CAMC1" wrote:
>
via[vbcol=seagreen]|||ABSOLUTELY!!!
Good way to handle it! It's rarely cost effective to restore just a small
amount of data.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"CAMC1" <webmaster@.ozoptics.com> wrote in message
news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
> That is what I tought.
> I told the user that it is faster to re-create the record via his
> application the restoring 1 user transaction.
> MC
> "JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
> news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> enviornment
> about
> database.
> via
>|||Thanks
MC
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:ecgEBzDrGHA.4032@.TK2MSFTNGP03.phx.gbl...
> ABSOLUTELY!!!
> Good way to handle it! It's rarely cost effective to restore just a small
> amount of data.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "CAMC1" <webmaster@.ozoptics.com> wrote in message
> news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
message[vbcol=seagreen]
acident[vbcol=seagreen]
>|||"CAMC1" <webmaster@.ozoptics.com> wrote in message
news:%23IEvOoDrGHA.3380@.TK2MSFTNGP04.phx.gbl...
> That is what I tought.
> I told the user that it is faster to re-create the record via his
> application the restoring 1 user transaction.
You forgot the "AND NEVER DO THAT AGAIN!" part. :-)

> MC
> "JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
> news:A36EF0B2-03E8-476D-A8FD-AC867228C453@.microsoft.com...
> enviornment
> about
about[vbcol=seagreen]
> database.
acident[vbcol=seagreen]
> via
table[vbcol=seagreen]
>