Restore dbase but leave 1 table the same

I have 2 servers and am migrating data occasionally from one server to the
other. There are several tables but one of the tables I do not want to be
updated by the restore. Just wondering if there is an easy way to do this?
Also the destination server has an additional stored procedure so would like
this procedure to be retained with the dbase restore.
thanks.
--
Paul G
Software engineer.There are many potential approaches, the choice of which depends on factors
such as the size of the database, the size of the table in question, etc.
If the table is smallish, you can preserve a copy in a different database
before the restore. After the database restore, repopulate the table back
from the saved copy. Same can be done to the stored procedure.
Linchi
"Paul" wrote:
> I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do this?
> Also the destination server has an additional stored procedure so would like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I forgot to mention that the schema is a bit
different between the two tables, destination table has 2 additional columns.
I have used the create script file for stored procedures and tables out of
the server explorer in .net. Guess there is no way to script to file a table
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
> There are many potential approaches, the choice of which depends on factors
> such as the size of the database, the size of the table in question, etc.
> If the table is smallish, you can preserve a copy in a different database
> before the restore. After the database restore, repopulate the table back
> from the saved copy. Same can be done to the stored procedure.
> Linchi
> "Paul" wrote:
> > I have 2 servers and am migrating data occasionally from one server to the
> > other. There are several tables but one of the tables I do not want to be
> > updated by the restore. Just wondering if there is an easy way to do this?
> > Also the destination server has an additional stored procedure so would like
> > this procedure to be retained with the dbase restore.
> > thanks.
> > --
> > Paul G
> > Software engineer.|||> Guess there is no way to script to file a table
> and the data that is in it.
You could always use DMO/SMO to script the table and bcp to export the data.
Linchi
"Paul" wrote:
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional columns.
> I have used the create script file for stored procedures and tables out of
> the server explorer in .net. Guess there is no way to script to file a table
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > There are many potential approaches, the choice of which depends on factors
> > such as the size of the database, the size of the table in question, etc.
> >
> > If the table is smallish, you can preserve a copy in a different database
> > before the restore. After the database restore, repopulate the table back
> > from the saved copy. Same can be done to the stored procedure.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > I have 2 servers and am migrating data occasionally from one server to the
> > > other. There are several tables but one of the tables I do not want to be
> > > updated by the restore. Just wondering if there is an easy way to do this?
> > > Also the destination server has an additional stored procedure so would like
> > > this procedure to be retained with the dbase restore.
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.|||use DTS...
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A97BB5B-3F3E-419E-917F-19B16A050400@.microsoft.com...
>I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do
> this?
> Also the destination server has an additional stored procedure so would
> like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.

Restore DB**

Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:

quote:

---

> This link should help you
> http://support.microsoft.com/defaul...kb;en-us;246133
> HTH
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:

quote:

---

> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/sql

Restore DB**

Hi
I've backed up my DB called DB1 in SQLServer 2000,
which include some users that owns some objects in
the database such as tables,views,...and these users
related to some logins.
now I want to restore DB1 in another Server,
but my problem is how can I define my logins and make a
relation between them and DB1'users?
(note: if I want to restore my DB1 in first server
there's no problem cause the defination of related logins exists)
or is there anyway to restore these information
without any problem?
any help would be greatly apprreciated.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Hi,
After restoration you can use the system stored procedure to fix the login
issue,
sp_change_users_login
Please refere BOL for more infomation. The only issue is you may need to
create the logins manually based on the first server. After that you can run
the above procedure with required parameters.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||This link should help you
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr1gd7geghqligo@.msnews.microsoft.com...
> Hi
> I've backed up my DB called DB1 in SQLServer 2000,
> which include some users that owns some objects in
> the database such as tables,views,...and these users
> related to some logins.
> now I want to restore DB1 in another Server,
> but my problem is how can I define my logins and make a
> relation between them and DB1'users?
> (note: if I want to restore my DB1 in first server
> there's no problem cause the defination of related logins exists)
> or is there anyway to restore these information
> without any problem?
> any help would be greatly apprreciated.
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:
> This link should help you
> http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
> HTH
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>> Hi
>> I've backed up my DB called DB1 in SQLServer 2000,
>> which include some users that owns some objects in
>> the database such as tables,views,...and these users
>> related to some logins.
>> now I want to restore DB1 in another Server,
>> but my problem is how can I define my logins and make a
>> relation between them and DB1'users?
>> (note: if I want to restore my DB1 in first server
>> there's no problem cause the defination of related logins exists)
>> or is there anyway to restore these information
>> without any problem?
>> any help would be greatly apprreciated.
>> --
>> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

Restore DB without Transaction Log

Hi,

How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.

Thanks

Shafiq

If you only restore the transaction log then the database will be transactionally inconsistent - the transaction log is an integral part of the backup.

What's the scenario context here?

BTW, what size DB and transaction log are we talking about here?

Thanks

|||

1. You must change your recovery model to simple and the backups wont record the transaction logs, this is the easiest way, but You shouldnt restore a backup without transaction logs, its supposed for you to have the info upto date until the failover.

|||

You should be wary about just going to simple recovery model. This is not suitable for production systems where loss of recent changes (in the event of a problem) is not acceptable.

This is because after a data backup in the simple recovery model, the transaction log is truncated. This means that its only possible to restore back to the time the last backup was completed - its not possible to do a point-in-time restore up to just before the problem occured so all work between those two points is lost.

See the SQL Server 2005 BOL entry for 'Overview of Simple Recovery' for more details.

Thanks

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisR
Hi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>
|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>
|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the transaction
>> log? If so, how?
>> TIA, ChrisR
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> If you have the FULL Database backup you could restore the full database.
>> See RESTORE DATABASE command in books online.
>> Log restore will help you to recover the database till POINT IN TIME.
>> Thanks
>> Hari
>> SQL Server MVP
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the
>> transaction log? If so, how?
>> TIA, ChrisR
>>
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>sql