Backup Strategy

Does anybody have any commnets on a backup strategy with Merge replicated
databases.
What happens when you restore a subscription database will changes previosly
propagated to it be reapplied.
Is there any point doing transaction log backups, what will happen if the
publisher is restored to a point in time will the subscribers drop data
inserted after the restore date or will the data come back from the
subscriptions to the publication after the restore date.
Any comments regarding backups and replication would be appreciated as we
begin to plan our backup strategy.
Cheers.
Yes, merge replication will detect what is missing in the restored backup
and resend what's missing.
You can do transaction log backup on the publisher and subscriber to bring
both back to a point in time and then let the merge agent merge both copies
to consistent states.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MB" <dontev@.ntry.it> wrote in message
news:emuCqZrPFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Does anybody have any commnets on a backup strategy with Merge replicated
> databases.
> What happens when you restore a subscription database will changes
previosly
> propagated to it be reapplied.
> Is there any point doing transaction log backups, what will happen if the
> publisher is restored to a point in time will the subscribers drop data
> inserted after the restore date or will the data come back from the
> subscriptions to the publication after the restore date.
> Any comments regarding backups and replication would be appreciated as we
> begin to plan our backup strategy.
> Cheers.
>
>
|||So am I right in thinking that you would have to bring all backups to the
same point in time?
Also if a publisher is restored will it all work again OK or will the
subscriptions then be invalid?
Cheers
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OWUriorPFHA.3788@.tk2msftngp13.phx.gbl...
> Yes, merge replication will detect what is missing in the restored backup
> and resend what's missing.
> You can do transaction log backup on the publisher and subscriber to bring
> both back to a point in time and then let the merge agent merge both
> copies
> to consistent states.
> --
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "MB" <dontev@.ntry.it> wrote in message
> news:emuCqZrPFHA.2252@.TK2MSFTNGP15.phx.gbl...
> previosly
>
|||You would want to bring at least the publisher back to the latest point in
time. If the subscriber is brought back to the same point - great. If not
transactions originating on the subscriber are lost, and the transactions
originating on the publisher will be back filled to the subscriber.
"MB" <dontev@.ntry.it> wrote in message
news:Oby2i8sPFHA.1088@.TK2MSFTNGP14.phx.gbl...
> So am I right in thinking that you would have to bring all backups to the
> same point in time?
> Also if a publisher is restored will it all work again OK or will the
> subscriptions then be invalid?
> Cheers
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OWUriorPFHA.3788@.tk2msftngp13.phx.gbl...
>
sql

Backup SQL 2005 to SQL 2000

Hi,
Is there anyway to restore a database that was created on SQL 2005 into SQL
2000. We have a database that I have been doing some testing on, on SQL 2005
and need to restore it back to SQL 2000 but keep getting various messages.
I have read that there is no way to do this but thought I would drop a
message in here first to confirm this or to find a way to do it?
Thanks
Mike
Your question is already answered in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:6C40D5E5-325E-429B-AF38-ED1DCA162AFA@.microsoft.com...
> Hi,
> Is there anyway to restore a database that was created on SQL 2005 into SQL
> 2000. We have a database that I have been doing some testing on, on SQL 2005
> and need to restore it back to SQL 2000 but keep getting various messages.
> I have read that there is no way to do this but thought I would drop a
> message in here first to confirm this or to find a way to do it?
> Thanks
> Mike

Backup SQL 2005 to SQL 2000

Hi,
Is there anyway to restore a database that was created on SQL 2005 into SQL
2000. We have a database that I have been doing some testing on, on SQL 2005
and need to restore it back to SQL 2000 but keep getting various messages.
I have read that there is no way to do this but thought I would drop a
message in here first to confirm this or to find a way to do it?
Thanks
MikeYour question is already answered in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:6C40D5E5-325E-429B-AF38-ED1DCA162AFA@.microsoft.com...
> Hi,
> Is there anyway to restore a database that was created on SQL 2005 into SQ
L
> 2000. We have a database that I have been doing some testing on, on SQL 20
05
> and need to restore it back to SQL 2000 but keep getting various messages.
> I have read that there is no way to do this but thought I would drop a
> message in here first to confirm this or to find a way to do it?
> Thanks
> Mike

Backup SQL 2005 to SQL 2000

Hi,
Is there anyway to restore a database that was created on SQL 2005 into SQL
2000. We have a database that I have been doing some testing on, on SQL 2005
and need to restore it back to SQL 2000 but keep getting various messages.
I have read that there is no way to do this but thought I would drop a
message in here first to confirm this or to find a way to do it?
Thanks
MikeYour question is already answered in your prior post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:6C40D5E5-325E-429B-AF38-ED1DCA162AFA@.microsoft.com...
> Hi,
> Is there anyway to restore a database that was created on SQL 2005 into SQL
> 2000. We have a database that I have been doing some testing on, on SQL 2005
> and need to restore it back to SQL 2000 but keep getting various messages.
> I have read that there is no way to do this but thought I would drop a
> message in here first to confirm this or to find a way to do it?
> Thanks
> Mike

Backup SQL 2005 Plan

Hello everybody,
I'm using SQL 2005 for few weeks, and I'd like to ask you something about
the Backup and Restore Plan. I need to create a complete DR solution for my
client.
So, what I did was: (using Management Studio-Maintenance Plan)
- All the user database are on Full Recovery Mode.
- Run a Full User Database backup every day at 1:00am and save it on day's
folder, it means the backup for Monday will go to Monday's backup folder and
so on.
- At 6:00am (normally the time they start to work), I start to do a Log
Backup every 15 minutes until 11:00pm aprox.
My question is:
- Every time that I ran the log backup, I'm truncating the LOG file? or I
have to run other task to truncate the log?
- Using that Plan, I'll be able to recover the data for the last 15 minutes
in case the database crashed?
- How often I have to shrink the database file and logs?
The Primary data file is : 570Gb
The Secondary data file is : 130Gb
The Log file is : 850Gb
I'm not worry about the space because the server and the backup unit have a
lot of space.
Any ideas or opinions'
Thank you!
JoseHola Jose,
Some comments here:
> The Log file is : 850Gb
Is this a typo? This is too big for a transaction log file. You should
manually shrink it, but probably only once. Do not schedule a job to shrink
it periodically.
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
After the transaction log is shrunk manually doing a backup every 15 minutes
should keep it in a acceptable size.
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
Yes, if you still have access to the backup folder. Are these folders on the
same computer? Also, if you are lucky and have access to the tail of the log
you can recover your database to the point of failure, losing no data at all.
> - How often I have to shrink the database file and logs?
Do not shrink any file on a job or maintenance plan. Do it only manually
when you really need it.
Hope this helps,
Ben Nevarez
"Jose" wrote:
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Jose
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
You can run BACKUP LOG with or without WITH INIT option (for more details
please see BOL)
If you choose using WITH INIT option that means SQL Server creates one file
per media set and you need to give the file separate name
Like log_20080101_17:00.log
log_20080101_17:30.log
On the other hand if you use WITH NOINIT you can create one file and SQL
Server adds one file to the media set and you will have to refer thopse
file when you restore database
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1,
norecovery --full database
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
You will be able to restore even at point of time
> - How often I have to shrink the database file and logs?
Do not shrink them at all
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:A3D3CD10-2D80-422A-AD68-09F9E510CDCB@.microsoft.com...
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for
> my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder
> and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have
> a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Hi Ben and Uri.
Thanks for your comments!.
And I made a mistake, is not "GB" is "MB"..sorry.
I've asked if every time that I run the backup for the LOG files I'm
truncating the log, because my client is using NAVISION from Microsoft, and
the company who did the installation, gave me some instructions for the
backup, and to be honest I dont know too much about SQL commands, that is way
I did using the Management Studio. And they told me to create :
- 1 full backup every day
- 1 log backup every 15 minutes
- at the end of the day truncate the log to keep the log small...
But if backing up the log and I'm truncating it at the same time, I'll need
to create other job to truncate the log at the end of the day? I guess not.
Right now, when I run the log backup, SQL creates a directory for each
database and it creates every log file for each database every 15 minutes and
the size of the log is 500Kb.
All the files backup are on the server and I have a task who move files and
logs to other (external) unit backup, so, at least I have 2 places to find if
something happens to the datasbase and 1 place if something happens to the
server and to be more protected, those database are been replicated online to
1 external server outside of the company.
When I will really need shrink a database and/or Log File?
Thank you so much for all your comments!
Have a nice day!
Jose
"Ben Nevarez" wrote:
> Hola Jose,
> Some comments here:
> > The Log file is : 850Gb
> Is this a typo? This is too big for a transaction log file. You should
> manually shrink it, but probably only once. Do not schedule a job to shrink
> it periodically.
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> After the transaction log is shrunk manually doing a backup every 15 minutes
> should keep it in a acceptable size.
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> Yes, if you still have access to the backup folder. Are these folders on the
> same computer? Also, if you are lucky and have access to the tail of the log
> you can recover your database to the point of failure, losing no data at all.
> > - How often I have to shrink the database file and logs?
> Do not shrink any file on a job or maintenance plan. Do it only manually
> when you really need it.
> Hope this helps,
> Ben Nevarez
>
>
> "Jose" wrote:
> > Hello everybody,
> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> > the Backup and Restore Plan. I need to create a complete DR solution for my
> > client.
> > So, what I did was: (using Management Studio-Maintenance Plan)
> > - All the user database are on Full Recovery Mode.
> > - Run a Full User Database backup every day at 1:00am and save it on day's
> > folder, it means the backup for Monday will go to Monday's backup folder and
> > so on.
> > - At 6:00am (normally the time they start to work), I start to do a Log
> > Backup every 15 minutes until 11:00pm aprox.
> >
> > My question is:
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> > - How often I have to shrink the database file and logs?
> >
> > The Primary data file is : 570Gb
> > The Secondary data file is : 130Gb
> > The Log file is : 850Gb
> >
> > I'm not worry about the space because the server and the backup unit have a
> > lot of space.
> >
> > Any ideas or opinions'
> > Thank you!
> >
> > Jose|||> When I will really need shrink a database and/or Log File?
If, and only if, it becomes exceptionally large. Larger that it would have to be for your normal
operation. And only if you actually gain something by doing the shrink (i.e., you really need the
disk space). See below:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:7D430938-4FC7-4D15-A4EF-6ED05EF6E386@.microsoft.com...
> Hi Ben and Uri.
> Thanks for your comments!.
> And I made a mistake, is not "GB" is "MB"..sorry.
> I've asked if every time that I run the backup for the LOG files I'm
> truncating the log, because my client is using NAVISION from Microsoft, and
> the company who did the installation, gave me some instructions for the
> backup, and to be honest I dont know too much about SQL commands, that is way
> I did using the Management Studio. And they told me to create :
> - 1 full backup every day
> - 1 log backup every 15 minutes
> - at the end of the day truncate the log to keep the log small...
> But if backing up the log and I'm truncating it at the same time, I'll need
> to create other job to truncate the log at the end of the day? I guess not.
> Right now, when I run the log backup, SQL creates a directory for each
> database and it creates every log file for each database every 15 minutes and
> the size of the log is 500Kb.
> All the files backup are on the server and I have a task who move files and
> logs to other (external) unit backup, so, at least I have 2 places to find if
> something happens to the datasbase and 1 place if something happens to the
> server and to be more protected, those database are been replicated online to
> 1 external server outside of the company.
> When I will really need shrink a database and/or Log File?
> Thank you so much for all your comments!
> Have a nice day!
> Jose
> "Ben Nevarez" wrote:
>> Hola Jose,
>> Some comments here:
>> > The Log file is : 850Gb
>> Is this a typo? This is too big for a transaction log file. You should
>> manually shrink it, but probably only once. Do not schedule a job to shrink
>> it periodically.
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> After the transaction log is shrunk manually doing a backup every 15 minutes
>> should keep it in a acceptable size.
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> Yes, if you still have access to the backup folder. Are these folders on the
>> same computer? Also, if you are lucky and have access to the tail of the log
>> you can recover your database to the point of failure, losing no data at all.
>> > - How often I have to shrink the database file and logs?
>> Do not shrink any file on a job or maintenance plan. Do it only manually
>> when you really need it.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Jose" wrote:
>> > Hello everybody,
>> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
>> > the Backup and Restore Plan. I need to create a complete DR solution for my
>> > client.
>> > So, what I did was: (using Management Studio-Maintenance Plan)
>> > - All the user database are on Full Recovery Mode.
>> > - Run a Full User Database backup every day at 1:00am and save it on day's
>> > folder, it means the backup for Monday will go to Monday's backup folder and
>> > so on.
>> > - At 6:00am (normally the time they start to work), I start to do a Log
>> > Backup every 15 minutes until 11:00pm aprox.
>> >
>> > My question is:
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> > - How often I have to shrink the database file and logs?
>> >
>> > The Primary data file is : 570Gb
>> > The Secondary data file is : 130Gb
>> > The Log file is : 850Gb
>> >
>> > I'm not worry about the space because the server and the backup unit have a
>> > lot of space.
>> >
>> > Any ideas or opinions'
>> > Thank you!
>> >
>> > Jose

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
Mike
One way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
> properties) and need to restore it back to another machine running SQL 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
MikeOne way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from t
he
> properties) and need to restore it back to another machine running SQL 200
0
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup SQL 2005 - Restore In SQL 2000

Hi,
I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
properties) and need to restore it back to another machine running SQL 2000
SP4. I am getting errors and cannot find a way to do it? Can any one help
please?
Thanks
MikeOne way to do that is running DTS Packages to transfer the data
"sonicm" <sonicm@.discussions.microsoft.com> wrote in message
news:987F2970-3600-4207-ACCB-E397C7F2AE6D@.microsoft.com...
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from
> the
> properties) and need to restore it back to another machine running SQL
> 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike|||sonicm wrote:
> Hi,
> I have been testing a database in SQL 2005 (keeping it in 2000 Mode from the
> properties) and need to restore it back to another machine running SQL 2000
> SP4. I am getting errors and cannot find a way to do it? Can any one help
> please?
> Thanks
> Mike
You cannot restore backwards across versions like this... You'll have
to migrate the data and schema using import/export, DTS, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup SQL 2000 SP4 and Restore on SQL 2005

Does anyone know if this works to where the data is viewable/usable?

I have an application that takes datasets out of the SQL database. We decommisioned our old SQL 2000 SP4 machine and brought in a SQL 2005 Server. What we did was backed up the old database and then restored it on the new Server.

Now the application does not see the old datasets but will collect new ones that are viewable. We can import older datasets but we have nearly 27 GB of data, which our DBA can see the old tables in Enterprise Manager but our applciation can't see it.

I'm thinking it might be a schema issue or the way that 2005 translate the odler 2000 SP4 database. Does anyone know, or have they run a back up on a older 2000 Server and then restored it to a 2005?

Thank you.

David

Can you see the data inside the new management studio.

|||

Yes. But not within the other application

My main question is has anyone tried to run a Backup of a SQL 2000 SP4 and restore in a SQL 2005, and was the data accessible?

|||Hi,

could be a owner / schema issue, depending which owner / schema the objects had and now have. The best thing would be to analyze what the application tries to query using the profiler. There you would see which user is trying to gain access to the database through the application and which objects are tried to access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

BACKUP RESTORE with no data

Hi champs,

Is there a way in SQL2k5 to backup and restore a database, without the data that is stored in the tables?

I know I can script the whole database, but is there do this with a backup restore?

/Many thanks

No, Backup backups up the database, data included. Otherwise it wouldn't be a backup, would it?

The easiest way is to script the database and run the scripts. I'm curious about you desire to avoid scripting...

|||

Is there any way of scripting the database out automatically?

I mean, is it possible to make a scripting backup of the database as a "Maintenance plan"?

/Many thanks

|||not as maintainance plan..... but u can create a job for it and schedule it....|||

Hi Rogvi

Is this something that you would need to do regularly and, if so, for what purpose?

Thanks
Chris

|||

The data in the database is not needed, as we are in the development mode now. I need to make regular database definition backup; the data in the database is only filling up the diskspace and is of no value.

/many thanks

BACKUP RESTORE with no data

Hi champs,

Is there a way in SQL2k5 to backup and restore a database, without the data that is stored in the tables?

I know I can script the whole database, but is there do this with a backup restore?

/Many thanks

No, Backup backups up the database, data included. Otherwise it wouldn't be a backup, would it?

The easiest way is to script the database and run the scripts. I'm curious about you desire to avoid scripting...

|||

Is there any way of scripting the database out automatically?

I mean, is it possible to make a scripting backup of the database as a "Maintenance plan"?

/Many thanks

|||not as maintainance plan..... but u can create a job for it and schedule it....|||

Hi Rogvi

Is this something that you would need to do regularly and, if so, for what purpose?

Thanks
Chris

|||

The data in the database is not needed, as we are in the development mode now. I need to make regular database definition backup; the data in the database is only filling up the diskspace and is of no value.

/many thanks

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
Keith
You can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:

>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is there
>a way to restore at the table level?
>
>Thanks,
>Keith
|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:

> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is there
> a way to restore at the table level?
>
> Thanks,
> Keith
>

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
KeithYou can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:

>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is ther
e
>a way to restore at the table level?
>
>Thanks,
>Keith|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:

> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is the
re
> a way to restore at the table level?
>
> Thanks,
> Keith
>sql

Backup Restore Tables inside a database

Can back up and restore a complete database, but it appears that I do not
have the options to restore individual tables inside the database. Is there
a way to restore at the table level?
Thanks,
KeithYou can't restore an individual table with SQL Server 7 or
SQL Server 2000. You would need to restore the backup to
another server or database and then copy the table from the
restored database.
-Sue
On Tue, 1 Mar 2005 17:01:02 -0800, "Keith"
<Keith@.discussions.microsoft.com> wrote:
>Can back up and restore a complete database, but it appears that I do not
>have the options to restore individual tables inside the database. Is there
>a way to restore at the table level?
>
>Thanks,
>Keith|||This is useful when you have a lot of data in your table. To do this your
database should be organized in primary and secondary filegroups. The large
tables should be on their own filegroup. In such cases you can backup and
restore a single file group (+ primary) and this way you may get just the
table you need.
Look for "Partial Database Restore Operations" in books online
Arun
"Keith" wrote:
> Can back up and restore a complete database, but it appears that I do not
> have the options to restore individual tables inside the database. Is there
> a way to restore at the table level?
>
> Thanks,
> Keith
>

backup restore question

Hi,
I wanted to clarify something about the backup restore process. If have the
following backups -
Day1 - 3 AM - Full backup
Day1 - 9 AM - Log backup
Day1 - 3 PM - Log backup
Day1 - 9 PM - Log backup
Day2 - 3 AM - Full backup
Day2 - 9 AM - Log backup
Day2 - 3 PM - Log backup
Day2 - 9 PM - Log backup
I want to restore the database to a point in time of 9 PM on Day2. The Full
backup of Day2 is corrupt. Can I restore the database by applying the
following backups in sequence -
Day1 - 3 AM - Full backup - not recovered
Day1 - 9 AM - Log backup - not recovered
Day1 - 3 PM - Log backup - not recovered
Day1 - 9 PM - Log backup - not recovered
Day2 - 9 AM - Log backup - not recovered
Day2 - 3 PM - Log backup - not recovered
Day2 - 9 PM - Log backup - recovered
Thanks in advance.
sharman,
Yes, if none of your log files are corrupt, you can indeed apply them over a
missing full backup, just as you describe. (Thus explaining, for anyone who
wondered, why the full backup does not free up the transaction log.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDA7766C-91AB-49E5-85F2-696B1807D59B@.microsoft.com...
> Hi,
> I wanted to clarify something about the backup restore process. If have
> the
> following backups -
> Day1 - 3 AM - Full backup
> Day1 - 9 AM - Log backup
> Day1 - 3 PM - Log backup
> Day1 - 9 PM - Log backup
> Day2 - 3 AM - Full backup
> Day2 - 9 AM - Log backup
> Day2 - 3 PM - Log backup
> Day2 - 9 PM - Log backup
> I want to restore the database to a point in time of 9 PM on Day2. The
> Full
> backup of Day2 is corrupt. Can I restore the database by applying the
> following backups in sequence -
> Day1 - 3 AM - Full backup - not recovered
> Day1 - 9 AM - Log backup - not recovered
> Day1 - 3 PM - Log backup - not recovered
> Day1 - 9 PM - Log backup - not recovered
> Day2 - 9 AM - Log backup - not recovered
> Day2 - 3 PM - Log backup - not recovered
> Day2 - 9 PM - Log backup - recovered
> Thanks in advance.
>
>

Backup Restore question

Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup taking
place at 10 PM?
Thanks in advance.You cannot do a restore to a database that is having a backup done at the
same time - and why would you?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup
taking
place at 10 PM?
Thanks in advance.|||Also, you will have to the use the previous full backup if you want to
restore the 10 p.m. transaction log backup
Ash
"sharman" wrote:
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup taking
> place at 10 PM?
> Thanks in advance.
>|||Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up these
new backups through Enterprise Manager( I have done it this way earlier and I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>|||Yes, you don't have to use the most recent full backup. You can use a
previous full backup and then all of the logs taken after that point in
time.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F39EAE37-615B-4AF7-BA67-13C56F34AB0B@.microsoft.com...
Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction
logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up
these
new backups through Enterprise Manager( I have done it this way earlier and
I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM
> how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>

backup restore question

Hi,
I wanted to clarify something about the backup restore process. If have the
following backups -
Day1 - 3 AM - Full backup
Day1 - 9 AM - Log backup
Day1 - 3 PM - Log backup
Day1 - 9 PM - Log backup
Day2 - 3 AM - Full backup
Day2 - 9 AM - Log backup
Day2 - 3 PM - Log backup
Day2 - 9 PM - Log backup
I want to restore the database to a point in time of 9 PM on Day2. The Full
backup of Day2 is corrupt. Can I restore the database by applying the
following backups in sequence -
Day1 - 3 AM - Full backup - not recovered
Day1 - 9 AM - Log backup - not recovered
Day1 - 3 PM - Log backup - not recovered
Day1 - 9 PM - Log backup - not recovered
Day2 - 9 AM - Log backup - not recovered
Day2 - 3 PM - Log backup - not recovered
Day2 - 9 PM - Log backup - recovered
Thanks in advance.sharman,
Yes, if none of your log files are corrupt, you can indeed apply them over a
missing full backup, just as you describe. (Thus explaining, for anyone who
wondered, why the full backup does not free up the transaction log.)
RLF
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDA7766C-91AB-49E5-85F2-696B1807D59B@.microsoft.com...
> Hi,
> I wanted to clarify something about the backup restore process. If have
> the
> following backups -
> Day1 - 3 AM - Full backup
> Day1 - 9 AM - Log backup
> Day1 - 3 PM - Log backup
> Day1 - 9 PM - Log backup
> Day2 - 3 AM - Full backup
> Day2 - 9 AM - Log backup
> Day2 - 3 PM - Log backup
> Day2 - 9 PM - Log backup
> I want to restore the database to a point in time of 9 PM on Day2. The
> Full
> backup of Day2 is corrupt. Can I restore the database by applying the
> following backups in sequence -
> Day1 - 3 AM - Full backup - not recovered
> Day1 - 9 AM - Log backup - not recovered
> Day1 - 3 PM - Log backup - not recovered
> Day1 - 9 PM - Log backup - not recovered
> Day2 - 9 AM - Log backup - not recovered
> Day2 - 3 PM - Log backup - not recovered
> Day2 - 9 PM - Log backup - recovered
> Thanks in advance.
>
>

Backup Restore of DB

Hi,
Newbie to Database administration. I would like to know what kind of
permission do I need to give a developer so that he can do restore/backup of
database other then the system DB?
Many thanksI would read the BOL reference for backinging up database permissions...
do a find for "permissions".
Backup
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
you are most likely looking for the db_backupoperator database role.
http://msdn2.microsoft.com/en-us/library/ms189041.aspx
Restore
http://msdn2.microsoft.com/en-us/library/ms186858.aspx
you are most likely looking for the db_dbcreator database role.
http://msdn2.microsoft.com/en-us/library/ms176014.aspx
That should be what you need...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mario" <Mario@.discussions.microsoft.com> wrote in message
news:B42FF432-3BF8-4B33-A286-5A955BC9945D@.microsoft.com...
> Hi,
> Newbie to Database administration. I would like to know what kind of
> permission do I need to give a developer so that he can do restore/backup
> of
> database other then the system DB?
> Many thankssql

Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)
Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>
|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>
|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||See answers in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.

> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.

> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.

> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......

>
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)

>
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.

>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...

>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>
|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>
|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>
|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>
|||EXCELLENT!
This is exactly what I was hoping for.
Works great.
Thanks Kevin.
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eQO43rwTIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Thanks, I'll give it a try.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nt26t2jvudl81@.corp.supernews.com...
>

Backup restore linking

Hi guys,
I need some help with backups and restores.
I want to run a backup to disk on server1 followed by a restore on server2
(Sort of replication)
I know how to do this with separate jobs or procedures on each server.
But, is there any way to run this as a single job on server2?
The goal is to abort restore if backup fails rather than relying on "timing"
I'm having problems specifying connections. Not sure if it's doable.
Thanks,
Jim
PS: DTS is too big a mystery...
(Both servers are SQL 2000)Server 1 should already have a preset backup routine that includes a FULL
backup. I would recommend you use that backup instead otherwise you run the
risk of interfering with the existing backup and or restore strategy
depending on how it is set up. And why duplicate the effort in the first
place. Then just make sure that the account on SQL Server on Server 2 has
the proper permissions to view the share where the backups are being sent
and the proper permissions to do a restore on Server 2 and you can do it all
from one job on Server 2.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> I need some help with backups and restores.
> I want to run a backup to disk on server1 followed by a restore on server2
> (Sort of replication)
> I know how to do this with separate jobs or procedures on each server.
> But, is there any way to run this as a single job on server2?
> The goal is to abort restore if backup fails rather than relying on
> "timing"
> I'm having problems specifying connections. Not sure if it's doable.
> Thanks,
> Jim
> PS: DTS is too big a mystery...
> (Both servers are SQL 2000)
>|||Hi Andrew,
Thanks for the help.
The goal is to have an "off-line spare" for disaster recovery.
They want to be able to replace the main server quickly.
For complex reasons it's not possible to use clustering or replication.
So the only solution left is to have a second server that is kept up to date
with regular backups and restores.
I realise the potential for conflicts with backups on both machines.
I can take care of that... I hope... :-)
What I am trying to eliminate is the need to rely on timing between tasks.
I want to be able to run the backup and restore within the same job so as to
handle possible backup failures.
On seperate servers, the restore wouldn't know if the backup failed and
would needlessly restore a previous set.
Not the end of the world but I'm aiming to be as efficient as possible.
The problem is that I can't figure out how to run a baclup on the donor
server from the recipient server.
Is that possible?
Thanks,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Server 1 should already have a preset backup routine that includes a FULL
> backup. I would recommend you use that backup instead otherwise you run
> the risk of interfering with the existing backup and or restore strategy
> depending on how it is set up. And why duplicate the effort in the first
> place. Then just make sure that the account on SQL Server on Server 2 has
> the proper permissions to view the share where the backups are being sent
> and the proper permissions to do a restore on Server 2 and you can do it
> all from one job on Server 2.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eH6nfX9SIHA.536@.TK2MSFTNGP06.phx.gbl...
>|||Try using stored procedures to handle the remote server actions (be it
backup or restore).
I have set up a system like this for a client with almost 6600 databases on
one server:
backups occur via a scheduled job that calls a sproc that loops the
databases and performs the correct backup type depending on various
settings. there is a table that stores backup information (maintained by
the backup sproc). at the end of the backup sproc a 'prepare restores'
sproc is run on the production server. it copies the necessary information
from the backup log table to a driver table and fires off a scheduled job on
the standby server. that job fires a sproc that gets the data from the 'to
restore' table and executes restore statements based on that information.
Works like a charm!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||See answers in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> Thanks for the help.
> The goal is to have an "off-line spare" for disaster recovery.
> They want to be able to replace the main server quickly.
> For complex reasons it's not possible to use clustering or replication.
> So the only solution left is to have a second server that is kept up to
> date with regular backups and restores.
If you are on SQL2005 then this sounds like a perfect case for database
mirroring.

> I realise the potential for conflicts with backups on both machines.
> I can take care of that... I hope... :-)
How? You better figure out all the nuances before you implement.

> What I am trying to eliminate is the need to rely on timing between tasks.
> I want to be able to run the backup and restore within the same job so as
> to handle possible backup failures.
> On seperate servers, the restore wouldn't know if the backup failed and
> would needlessly restore a previous set.
> Not the end of the world but I'm aiming to be as efficient as possible.
I don't see the problem. This technique is used every day by thousands of
sites with no problems. The implementations may vary slightly depending on
requirements but there is no reason you need to redo restores. One way is to
have the backup job on Server A copy the file to another folder or network
share. Then the job on Server B restores the file when it sees it. When done
it removes it so it doesn't try that same file again. Another method is to
name the backup files with a monotomically increasing ID or timestamp and
track which files were restored last so you know which to do next. Neither
of these techniques require duplicating the backup operation although they
may make a copy of the backup file. If the backup fails the files don't get
copied so there is no issues.

> The problem is that I can't figure out how to run a baclup on the donor
> server from the recipient server.
> Is that possible?
If you log in with the correct accounts you can issue any command you have
the rights to using oSql or SqlCmd. This can be startign a job, calling a
stored procedure or a command directly.
> Thanks,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI2tz7$SIHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||Further questions inline below:
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unQTa$jTIHA.6060@.TK2MSFTNGP05.phx.gbl...
> See answers in-line:
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If you are on SQL2005 then this sounds like a perfect case for database
> mirroring.
We're working with SQL 2000, I only wish it was 2005.......

>
> How? You better figure out all the nuances before you implement.
That's why I'm taking it slow and methodical. I plan to dump all backups in
favour of the new structure.
(Eventually.. - I am accutely aware of potential conflicts which is why I
didn't just jump right in)

>
> I don't see the problem. This technique is used every day by thousands of
> sites with no problems. The implementations may vary slightly depending on
> requirements but there is no reason you need to redo restores. One way is
> to have the backup job on Server A copy the file to another folder or
> network share. Then the job on Server B restores the file when it sees it.
Ah now, there's the rub. How does it know the file is there?
Remember I am relatively unskilled at SQL, I'm not well versed in t-sql.
I don't know how to remove the file on completed backup.
Although, I suppose I could find it, that's a good pointer / idea, thanks.
How would you do this in sql script?
if exists(backupfile) = false then exit with error
Baring in mind that the backups run to the same network folder that the
restore pulls from.
If the backup is late or still running or gets interupted then the restore
can't run successfully...
that's what I'm trying to rule out.

>When done it removes it so it doesn't try that same file again. Another
>method is to name the backup files with a monotomically increasing ID or
>timestamp and track which files were restored last so you know which to do
>next.
Drive space is an issue, they only have room for one set of backups...
Sucks, but what can you do when they won't buy new hardware...

>Neither of these techniques require duplicating the backup operation
>although they may make a copy of the backup file. If the backup fails the
>files don't get copied so there is no issues.
>
> If you log in with the correct accounts you can issue any command you have
> the rights to using oSql or SqlCmd. This can be startign a job, calling a
> stored procedure or a command directly.
>|||Thanks Kevin,
One question though,
How do you call the sproc situated on the remote machine?
The core of my questioning is how to you specify "run this on machine x" in
t-sql?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nqafj3koojca0@.corp.supernews.com...
> Try using stored procedures to handle the remote server actions (be it
> backup or restore).
> I have set up a system like this for a client with almost 6600 databases
> on one server:
> backups occur via a scheduled job that calls a sproc that loops the
> databases and performs the correct backup type depending on various
> settings. there is a table that stores backup information (maintained by
> the backup sproc). at the end of the backup sproc a 'prepare restores'
> sproc is run on the production server. it copies the necessary
> information from the backup log table to a driver table and fires off a
> scheduled job on the standby server. that job fires a sproc that gets the
> data from the 'to restore' table and executes restore statements based on
> that information. Works like a charm!
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:OPMyIziTIHA.4532@.TK2MSFTNGP02.phx.gbl...
>|||see linked server in BOL. This allows you do to this:
exec remoteservername.remotedatabasename.dbo.somesproc
Note that the Distributed Transaction Coordinator can be a PITA to get set
up correctly! :-)
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
> Thanks Kevin,
> One question though,
> How do you call the sproc situated on the remote machine?
> The core of my questioning is how to you specify "run this on machine x"
> in t-sql?
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13nqafj3koojca0@.corp.supernews.com...
>|||Thanks, I'll give it a try.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13nt26t2jvudl81@.corp.supernews.com...
> see linked server in BOL. This allows you do to this:
> exec remoteservername.remotedatabasename.dbo.somesproc
> Note that the Distributed Transaction Coordinator can be a PITA to get set
> up correctly! :-)
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:eUxji0uTIHA.5516@.TK2MSFTNGP02.phx.gbl...
>