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 li
ke
> 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 tabl
e
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
[vbcol=seagreen]
> There are many potential approaches, the choice of which depends on factor
s
> 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:
>|||> 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:
[vbcol=seagreen]
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional colum
ns.
> I have used the create script file for stored procedures and tables out o
f
> the server explorer in .net. Guess there is no way to script to file a ta
ble
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
>|||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.
Showing posts with label migrating. Show all posts
Showing posts with label migrating. Show all posts
Friday, March 30, 2012
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.
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 to another server
I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.
I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.
|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.
I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.
|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
Restore db to another server
I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
>> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
>> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
>> it was cancelled after 10% write; error 34113 on BackupExec server, and
>> repeated error 57859 on destination SQL server.
>> When rerun job, no data is written and job cancels with same error in
>> BackupExec server, and new error in destination SQL server: 57859 --
>> "Exclusive access couldn not be obtained because the database is in use." (It
>> seems to me it's not in use, nobody uses this server yet.)
>> Strange thing is, I can restore another (test) database from same tape
>> without any problems.
>> Happy to get any hints or guidelines, I'm kinda stuck.
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (It
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use." (It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
>> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
>> I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
>> it was cancelled after 10% write; error 34113 on BackupExec server, and
>> repeated error 57859 on destination SQL server.
>> When rerun job, no data is written and job cancels with same error in
>> BackupExec server, and new error in destination SQL server: 57859 --
>> "Exclusive access couldn not be obtained because the database is in use." (It
>> seems to me it's not in use, nobody uses this server yet.)
>> Strange thing is, I can restore another (test) database from same tape
>> without any problems.
>> Happy to get any hints or guidelines, I'm kinda stuck.
Restore db to another server
I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (I
t
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a resto
re
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use."
(It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>sql
(SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
I'm restoring db from tape w. BackupExec 9.0. The first time I did a restore
it was cancelled after 10% write; error 34113 on BackupExec server, and
repeated error 57859 on destination SQL server.
When rerun job, no data is written and job cancels with same error in
BackupExec server, and new error in destination SQL server: 57859 --
"Exclusive access couldn not be obtained because the database is in use." (I
t
seems to me it's not in use, nobody uses this server yet.)
Strange thing is, I can restore another (test) database from same tape
without any problems.
Happy to get any hints or guidelines, I'm kinda stuck.I deleted the restored database on new SQL server an reran the restore job,
and everything worked OK -- and so I will never know what went wrong the
first time, but will nevertheless be able to spend the weekend with family
instead of SQL.
Thanks for taking time, if you took any.
"JSL" wrote:
> I'm in the process of migrating db:s from SQL 2000 (SP2) on Windows 2000
> (SP4) to SQL 2000 (SP4) on Windows Server 2003 (SP1).
> I'm restoring db from tape w. BackupExec 9.0. The first time I did a resto
re
> it was cancelled after 10% write; error 34113 on BackupExec server, and
> repeated error 57859 on destination SQL server.
> When rerun job, no data is written and job cancels with same error in
> BackupExec server, and new error in destination SQL server: 57859 --
> "Exclusive access couldn not be obtained because the database is in use."
(It
> seems to me it's not in use, nobody uses this server yet.)
> Strange thing is, I can restore another (test) database from same tape
> without any problems.
> Happy to get any hints or guidelines, I'm kinda stuck.|||Next time, use sp_who to see who is using the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:90762CE8-604D-4BD5-A1DE-9645952022C9@.microsoft.com...[vbcol=seagreen]
>I deleted the restored database on new SQL server an reran the restore job,
> and everything worked OK -- and so I will never know what went wrong the
> first time, but will nevertheless be able to spend the weekend with family
> instead of SQL.
> Thanks for taking time, if you took any.
> "JSL" wrote:
>sql
Subscribe to:
Posts (Atom)