Restore user db'd on servers with different service pack levels

Is there any documentation on comaptibility related to restoring
databases backed up from server A to server B as it pertains to
editions and service pack levels?
For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
database, and vice versa?take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm
in your SQL 2005 books online
or
search sp_dbcmptlevel for SQL 2000
SQL Server is forward Compatable as a rule of thumb. Meaning you can
usually go from an older version of 2000 to a 2005. It does not support
backwards compatablity moving from 2005 to 2000.
So to answer your question. Yes you can move forward and restore onto the
newest service pack as long as these are USER databases and not system.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Jeff McAhren" <mcahren@.gmail.com> wrote in message
news:1158004530.422905.146510@.i3g2000cwc.googlegroups.com...
> Is there any documentation on comaptibility related to restoring
> databases backed up from server A to server B as it pertains to
> editions and service pack levels?
> For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
> database, and vice versa?
>

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the t
wo? When should I use the Transaction log files? Thanks.
This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.
|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known go
od state, should I restore the transaction log files which is backed up ever
y hour or the .bak file which is backed up every night. Basically, what is t
he difference between the t
wo? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.sql

Restore Transaction log or the .bak

If there is inconsistency on the DB and I want to restore to a last known good state, should I restore the transaction log files which is backed up every hour or the .bak file which is backed up every night. Basically, what is the difference between the two? When should I use the Transaction log files? Thanks.This depends on when you what you want to do and when you database was last
consistant.
If the last consistant state was just after you last nightly full or
differential backups then you will not need any transaction log backups.
The transaction log allows you to restore up to a particular point in time
between your nightly full and/or differentials backups. If your requirement
is to restore your database to 8:36 AM today, then you will need to use the
last full backup, plus the closest differential (last nights, if there was
one) and then the 1 am, 2 am, 3, am, 4 am, 5 am, 6 am, 7 am, 8 am and 9 am
transaction logs to restore using the STOPAT. The STOPAT command allows you
to stop the restore process at 8:36 am.
What backups do you have and when where they taken? Also when was the last
time you knew for sure the database was consistant.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.|||You need to read the BOL (Books On-Line) section on Backing up and Restoring
Databases located under Administering SQL Server. It is a bit long and
complex, but then the topic is somewhat complex and deserves a long
explanation.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"mmc" <anonymous@.discussions.microsoft.com> wrote in message
news:AD0940C9-56AF-4851-88B0-CBC2AB05058E@.microsoft.com...
> If there is inconsistency on the DB and I want to restore to a last known
good state, should I restore the transaction log files which is backed up
every hour or the .bak file which is backed up every night. Basically, what
is the difference between the two? When should I use the Transaction log
files? Thanks.

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.

> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.

> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR
1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>
|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>
|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process
will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command
and the error message(s)
returned by the database engine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl.
.
>I have backed up our production database to a file and would like to restor
e it to a test database
>I created. This seems very simple but I seem to be missing something. I am
using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
Paul G
Software engineer.
"TRD" wrote:

> I have backed up our production database to a file and would like to resto
re
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...[vbcol=seagreen]
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>

Restore to a Different Database

I have backed up our production database to a file and would like to restore
it to a test database I created. This seems very simple but I seem to be
missing something. I am using query analyzer. Could someone please assist
me?
TIA
TR1. You don't have to create the database before RESTORE. The restore process will do it for you.
Pay special care of the MOVE and REPLACE options of the RESTORE command.
In case you don't succeed with the restore, please post the RESTORE command and the error message(s)
returned by the database engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TRD" <tdejohnx2@.hotmail.com> wrote in message news:%23GZjg512GHA.1548@.TK2MSFTNGP02.phx.gbl...
>I have backed up our production database to a file and would like to restore it to a test database
>I created. This seems very simple but I seem to be missing something. I am using query analyzer.
>Could someone please assist me?
>
> TIA
> TR
>|||just restore the database from the backup file as suggested. First delete
the test database and then make sure you name the new database and select
from Device as it will then allow you to browse to your backup file from the
source database. Just right click from enterprise manager and select all
tasks/restore database.
--
Paul G
Software engineer.
"TRD" wrote:
> I have backed up our production database to a file and would like to restore
> it to a test database I created. This seems very simple but I seem to be
> missing something. I am using query analyzer. Could someone please assist
> me?
>
> TIA
> TR
>
>|||Thanks for the help.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:3379D7D1-01CA-4617-A54B-EEBFF56631AB@.microsoft.com...
> just restore the database from the backup file as suggested. First delete
> the test database and then make sure you name the new database and select
> from Device as it will then allow you to browse to your backup file from
> the
> source database. Just right click from enterprise manager and select all
> tasks/restore database.
> --
> Paul G
> Software engineer.
>
> "TRD" wrote:
>> I have backed up our production database to a file and would like to
>> restore
>> it to a test database I created. This seems very simple but I seem to be
>> missing something. I am using query analyzer. Could someone please assist
>> me?
>>
>> TIA
>> TR
>>

Restore system dbs from SP3 to SP3a?

Can system databases (master, model, msdb) backed up from SQL2k SP3 be restored to an instance of SP3a? Can they be restored from SP3a to SP3? TIA!Not going to happen buddy. try installing a fresh copy of sql server somewhere else upgrade to sp3, restore your system db's there, upgrade to sp3a then move the system db's to the live server. hope this helps.|||Thanks - that's what I thought. However, how can it be determined which SP (3 or 3a) a given instance is running? @.@.version returns 8.00.760 for both of them, & Properties in EM show SP3 for both. I remember there being a way to find the build number, but I don't know how to get it. TIA.|||Check with MS website at the SP3/SP3a download pages. They usually have a section that tells you how to determine which SP you're currently running under.|||Even better: http://www.sqlteam.com/item.asp?ItemID=8318

Restore of sql server database is producing error.

Hi,
I had to move a database 'accounting' from one server to another. I had backed up the database in the first server and then trying to restore in the second server. I have quite a few databases in the second server that needs to be operational. However, wh
en I am trying to restore the 'accounting' database from EM, I am getting the following error:
Device activation error. The physical file name 'e:\accounts\Data\accounting.mdf' may be incorrect. File 'accounting_Data' cannot be retored to 'e:\accounts\Data\accounting.mdf'. Use WITH MOVE to identify a valid loction for the file.
Device activation error. The physical file name 'e:\accounts\Data\accounting.ldf' may be incorrect. File 'accounting_Data' cannot be retored to 'e:\accounts\Data\accounting.ldf'. Use WITH MOVE to identify a valid loction for the
Any help as to how to resolve this problem is highly appreciated in advance.
Thanks
Hi,
This is because you do not have the directory 'e:\accounts\Data\' in the new
server. There are 2 options , either craete the directory in E drive
and do the restore again.
seconds option - If you do not have the Edrive then you have to follow the
below steps:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\backup\dbname.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\dbname.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
path'
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:31F8EFC7-1D4A-486E-87D4-4695018B224B@.microsoft.com...
> Hi,
> I had to move a database 'accounting' from one server to another. I had
backed up the database in the first server and then trying to restore in the
second server. I have quite a few databases in the second server that needs
to be operational. However, when I am trying to restore the 'accounting'
database from EM, I am getting the following error:
> Device activation error. The physical file name
'e:\accounts\Data\accounting.mdf' may be incorrect. File 'accounting_Data'
cannot be retored to 'e:\accounts\Data\accounting.mdf'. Use WITH MOVE to
identify a valid loction for the file.
> Device activation error. The physical file name
'e:\accounts\Data\accounting.ldf' may be incorrect. File 'accounting_Data'
cannot be retored to 'e:\accounts\Data\accounting.ldf'. Use WITH MOVE to
identify a valid loction for the
> Any help as to how to resolve this problem is highly appreciated in
advance.
> Thanks

Restore of sql server database is producing error.

Hi,
I had to move a database 'accounting' from one server to another. I had back
ed up the database in the first server and then trying to restore in the sec
ond server. I have quite a few databases in the second server that needs to
be operational. However, wh
en I am trying to restore the 'accounting' database from EM, I am getting th
e following error:
Device activation error. The physical file name 'e:\accounts\Data\accounting
.mdf' may be incorrect. File 'accounting_Data' cannot be retored to 'e:\acco
unts\Data\accounting.mdf'. Use WITH MOVE to identify a valid loction for the
file.
Device activation error. The physical file name 'e:\accounts\Data\accounting
.ldf' may be incorrect. File 'accounting_Data' cannot be retored to 'e:\acco
unts\Data\accounting.ldf'. Use WITH MOVE to identify a valid loction for the
Any help as to how to resolve this problem is highly appreciated in advance.
ThanksHi,
This is because you do not have the directory 'e:\accounts\Data' in the new
server. There are 2 options , either craete the directory in E drive
and do the restore again.
seconds option - If you do not have the Edrive then you have to follow the
below steps:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\backup\dbname.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\dbname.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
path'
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:31F8EFC7-1D4A-486E-87D4-4695018B224B@.microsoft.com...
> Hi,
> I had to move a database 'accounting' from one server to another. I had
backed up the database in the first server and then trying to restore in the
second server. I have quite a few databases in the second server that needs
to be operational. However, when I am trying to restore the 'accounting'
database from EM, I am getting the following error:
> Device activation error. The physical file name
'e:\accounts\Data\accounting.mdf' may be incorrect. File 'accounting_Data'
cannot be retored to 'e:\accounts\Data\accounting.mdf'. Use WITH MOVE to
identify a valid loction for the file.
> Device activation error. The physical file name
'e:\accounts\Data\accounting.ldf' may be incorrect. File 'accounting_Data'
cannot be retored to 'e:\accounts\Data\accounting.ldf'. Use WITH MOVE to
identify a valid loction for the
> Any help as to how to resolve this problem is highly appreciated in
advance.
> Thanks

Restore nText from Transaction Log

A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?
Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com

Restore nText from Transaction Log

A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com

Restore nText from Transaction Log

A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com

restore mssql 7.0. database

When I try to restore mssql 7.0 database,I got strange error :

The database you are attempting to restore was backed up under a different sort order ID (52) than the one currently runing on the server (106), and at least one of them is a non-binary sort order.
Backup or restore operation terminating abnormally.

Is there a way how i can fix it ?
Thanks a lot !You need to change sort order by using REBUILDM utility to rebuild the master database, refer to books online for more information.

Or rebuild the target server with source server's collation settings.