Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piece
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPM
Hi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/default...;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring master db from old install of different SQL version

All,
I had to downgrade from Enterprise to Standard on a server. So I took
backups of all DB's, uninstalled enterprise and installed standard with
SP3a. Then I restored all user DB's.
I then wanted to use the master from enterprise and install into standard so
I would keep users and passwords etc. We have apps that use users like
SalesAppUser with a password hardly anyone knows. So I just wanted a nice
plain replace new master with old master.
Thanks to Robert Davies who helped me get to the command line prompt for
opening up the server in single user mode. I then restored master from the
previous backup and server no longer functions.
I just want to confirm that is what you would expect from one install to
another. Reading back it now looks like I was trying to put the square piec
e
through the round hole !!
Any suggestions for how I might achieve my goal?
Thanks
MPMHi
If logins/passwords are the only things you need to keep then you may want
to look at
http://support.microsoft.com/kb/246133/
Good overall references are:
http://support.microsoft.com/kb/224071/EN-US/
http://support.microsoft.com/defaul...b;en-us;Q314546
John
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:A30FB667-82C7-4156-A70C-E24497E3E96D@.microsoft.com...
> All,
> I had to downgrade from Enterprise to Standard on a server. So I took
> backups of all DB's, uninstalled enterprise and installed standard with
> SP3a. Then I restored all user DB's.
> I then wanted to use the master from enterprise and install into standard
> so
> I would keep users and passwords etc. We have apps that use users like
> SalesAppUser with a password hardly anyone knows. So I just wanted a nice
> plain replace new master with old master.
> Thanks to Robert Davies who helped me get to the command line prompt for
> opening up the server in single user mode. I then restored master from
> the
> previous backup and server no longer functions.
> I just want to confirm that is what you would expect from one install to
> another. Reading back it now looks like I was trying to put the square
> piece
> through the round hole !!
> Any suggestions for how I might achieve my goal?
> Thanks
> MPM

Restoring differential backup

fniles,
OK, then it looks like the version of your database and the version of the
differential are out of synchronization with each other. Here is someone
who had a similar problem recently, and just needed to redo things in the
proper order:
http://www.sqlservercentral.com/Forums/Topic308260-146-1.aspx
You only show restoring one backup, which is apparently the differential
backup. The error message means that it is apparently not directly
connected to the restore of your full backup. This could be for a number of
reasons, such as:
1. Some other full backup was run between the one that you restored and the
differential that you are using. If that happened, then it means that the
differential is intended for the other full backup, not the one you are
using.
2. Your full backup was not restored with NORECOVERY, so it has transactions
that invalidate the restore chain for the differential.
Also, a brief comment in this blog.
http://blog.sqlauthority.com/2007/09/02/sql-server-fix-error-msg-3117-level-16-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
In your query of the backups you would get a result like this:
MyDB StartDateTime99 EndDateTime99 L
MyDB StartDateTime98 EndDateTime98 I
MyDB StartDateTime97 EndDateTime97 L
MyDB StartDateTime96 EndDateTime96 I -- if this was a D it resets the
differential start point.
MyDB StartDateTime95 EndDateTime95 L
MyDB StartDateTime94 EndDateTime94 D
What you want to make sure of for case 2 above is that there is not another
database full backup (D) between the one that you are restoring and the
differential (I) that you are also trying to restore. For example, in the
list above you can restore D from StartDateTime 94 norecovery, then restore
I from StartDateTime 98.
However, if the backup at StartDateTime 96 was not a differential but was
another full (D) then the differential from StartDateTime 98 would be base
on 96 instead of being based on 94 and could not be restored.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Thank you.
> I followed your suggestion and run it from the following script and got
> the same error:
> RESTORE DATABASE [DeskDemoTest] FROM
> DISK = N'D:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\Desk\DeskDemo\DeskDemo _backup_200802011000.dbd'
> WITH FILE = 1, MOVE N'DeskDemo'
> TO N'D:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\DeskDemoTest.mdf',
> MOVE N'DeskDemo_log' TO N'D:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\DeskDemoTest.ldf',
> NOUNLOAD, STATS = 10
> Regarding the 2nd item:
> I ran the following query:
> select database_name, backup_start_date, backup_finish_date, type from
> msdb.dbo.backupset where database_name = 'mydb' -->> this is the original
> database where the original backup is made, is that correct ?
> and backup_start_date > DATEADD(day,-3, GETDATE()) order by
> backup_start_date desc
> and it returns 477 records. Could you please tell me what I should be
> looking for in the query result ?
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uUgNZ9rYIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
Thank you very much !
You found the problem.
I did have another full backup in between the one I restored and the
differential backup.
I use the last full backup, and it now works fine.
I just want to make sure a couple of things:
1. I have 4 transaction backup after the differential backup is created.
So, I want to restore the full backup with NORECOVERY, the differential
backup with NORECOVERY, the 3 transaction backup that were made after the
differential backup with NORECOVERY, and the last transaction backup with
RECOVERY. Is that correct ?
2. Everytime I try to restore the differential or transation backup, under
"Options" in the "Restore As" it always list the original database like
c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\myOriginalDatabase.mdf
and
c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\myOriginalDatabase_log.l df
So, to restore the diff and all the transaction log I have to keep editing
the "Restore As" to point to the new database. Is this correct ?
Thanks !
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23CCXAbzZIHA.1184@.TK2MSFTNGP04.phx.gbl...
> fniles,
> OK, then it looks like the version of your database and the version of the
> differential are out of synchronization with each other. Here is someone
> who had a similar problem recently, and just needed to redo things in the
> proper order:
> http://www.sqlservercentral.com/Forums/Topic308260-146-1.aspx
> You only show restoring one backup, which is apparently the differential
> backup. The error message means that it is apparently not directly
> connected to the restore of your full backup. This could be for a number
> of reasons, such as:
> 1. Some other full backup was run between the one that you restored and
> the differential that you are using. If that happened, then it means that
> the differential is intended for the other full backup, not the one you
> are using.
> 2. Your full backup was not restored with NORECOVERY, so it has
> transactions that invalidate the restore chain for the differential.
> Also, a brief comment in this blog.
> http://blog.sqlauthority.com/2007/09/02/sql-server-fix-error-msg-3117-level-16-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
> In your query of the backups you would get a result like this:
> MyDB StartDateTime99 EndDateTime99 L
> MyDB StartDateTime98 EndDateTime98 I
> MyDB StartDateTime97 EndDateTime97 L
> MyDB StartDateTime96 EndDateTime96 I -- if this was a D it resets the
> differential start point.
> MyDB StartDateTime95 EndDateTime95 L
> MyDB StartDateTime94 EndDateTime94 D
> What you want to make sure of for case 2 above is that there is not
> another database full backup (D) between the one that you are restoring
> and the differential (I) that you are also trying to restore. For
> example, in the list above you can restore D from StartDateTime 94
> norecovery, then restore I from StartDateTime 98.
> However, if the backup at StartDateTime 96 was not a differential but was
> another full (D) then the differential from StartDateTime 98 would be base
> on 96 instead of being based on 94 and could not be restored.
> RLF
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||Also, after restoring the database to a new database, when I do
SELECT file_id, name FROM sys.database_files;
it returns the original database name like "MyOriginalDB" and
"MyOriginalDB_Log" instead of "MyNewDB" and "MyNewDB_Log".
Is that correct ?
Thank you.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23CCXAbzZIHA.1184@.TK2MSFTNGP04.phx.gbl...
> fniles,
> OK, then it looks like the version of your database and the version of the
> differential are out of synchronization with each other. Here is someone
> who had a similar problem recently, and just needed to redo things in the
> proper order:
> http://www.sqlservercentral.com/Forums/Topic308260-146-1.aspx
> You only show restoring one backup, which is apparently the differential
> backup. The error message means that it is apparently not directly
> connected to the restore of your full backup. This could be for a number
> of reasons, such as:
> 1. Some other full backup was run between the one that you restored and
> the differential that you are using. If that happened, then it means that
> the differential is intended for the other full backup, not the one you
> are using.
> 2. Your full backup was not restored with NORECOVERY, so it has
> transactions that invalidate the restore chain for the differential.
> Also, a brief comment in this blog.
> http://blog.sqlauthority.com/2007/09/02/sql-server-fix-error-msg-3117-level-16-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
> In your query of the backups you would get a result like this:
> MyDB StartDateTime99 EndDateTime99 L
> MyDB StartDateTime98 EndDateTime98 I
> MyDB StartDateTime97 EndDateTime97 L
> MyDB StartDateTime96 EndDateTime96 I -- if this was a D it resets the
> differential start point.
> MyDB StartDateTime95 EndDateTime95 L
> MyDB StartDateTime94 EndDateTime94 D
> What you want to make sure of for case 2 above is that there is not
> another database full backup (D) between the one that you are restoring
> and the differential (I) that you are also trying to restore. For
> example, in the list above you can restore D from StartDateTime 94
> norecovery, then restore I from StartDateTime 98.
> However, if the backup at StartDateTime 96 was not a differential but was
> another full (D) then the differential from StartDateTime 98 would be base
> on 96 instead of being based on 94 and could not be restored.
> RLF
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||fniles,
Great! Other replies inline.

> 1. I have 4 transaction backup after the differential backup is created.
> So, I want to restore the full backup with NORECOVERY, the differential
> backup with NORECOVERY, the 3 transaction backup that were made after the
> differential backup with NORECOVERY, and the last transaction backup with
> RECOVERY. Is that correct ?
Yes, that is correct.

> 2. Everytime I try to restore the differential or transation backup, under
> ...
> So, to restore the diff and all the transaction log I have to keep editing
> the "Restore As" to point to the new database. Is this correct ?
That is correct. Of course, now that you see the backup history (and if it
is worth the work) you could create a query that would script out your
restore command for you. Then you could paste it into the query window and
hit Execute.
RLF
|||fniles,
Yes, the Logical File Name is not changed by the restore, but the Physical
File Name is what is changed. After the restore you could run two ALTER
DATABASE ... MODIFY FILE command to alter the logical name from MyOriginalDB
to MyNewDB, then do the same for the Log.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:O2zAvX1ZIHA.4440@.TK2MSFTNGP06.phx.gbl...
> Also, after restoring the database to a new database, when I do
> SELECT file_id, name FROM sys.database_files;
> it returns the original database name like "MyOriginalDB" and
> "MyOriginalDB_Log" instead of "MyNewDB" and "MyNewDB_Log".
> Is that correct ?
> Thank you.
|||Thank you very much for all your help !
To alter the Logical File name, I can also do the following, right ?
In the property of the database under "Files" I changed the logical names
from MyOriginalDB to be the new database name (MyNewDB).
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eIwjW62ZIHA.4180@.TK2MSFTNGP06.phx.gbl...
> fniles,
> Yes, the Logical File Name is not changed by the restore, but the Physical
> File Name is what is changed. After the restore you could run two ALTER
> DATABASE ... MODIFY FILE command to alter the logical name from
> MyOriginalDB to MyNewDB, then do the same for the Log.
> RLF
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O2zAvX1ZIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||Should be fine. If you press the Script button, you will see the TSQL that
the property change produces.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:O9fwVT3ZIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Thank you very much for all your help !
> To alter the Logical File name, I can also do the following, right ?
> In the property of the database under "Files" I changed the logical names
> from MyOriginalDB to be the new database name (MyNewDB).
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eIwjW62ZIHA.4180@.TK2MSFTNGP06.phx.gbl...
>
|||Hello, I've been following this thread and awhile given up on using the GUI
for backup/restore. Using this syntax I do full backups twice a week. This is
definitely working. The incrementals are surely there. Thing is, when I look
at my backup history, only the full backups are shown.
So upon restore, how would I know what logical file # to use? I suppose I
could guess and try File = 1, File = 2.
If Datepart(dw, Getdate()) In (3, 6)--Tuesday or Friday
Begin
Backup Database MyDbp To Disk = N'\\MyPath\MyFile.Bak'
With Name = 'MyFile full backup', Init
Else
Begin
Backup Database MyDbp To Disk = N'\\MyPath\MyFile.Bak'
With Name = 'MyFile differential backup', Differential
End
Thanks,
Ken

Restoring database problem in SQL Server 7

I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.
Have you tried:
restore database MyDB
from disk = 'C:\MyDB.bak'
with replace
, move 'MyDataFile' to 'C:\MyDB.mdf'
, move 'MyLogFile' to 'C:\MyDB.ldf'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Edward Diener" <eddielee_no_spam_here@.tropicsoft.com> wrote in message
news:u%231yYZNXFHA.2468@.TK2MSFTNGP10.phx.gbl...
I have an updated version of a database which was not created by a
backup on my server but on another server. I put the updated copy in a
directory, and attempt to restore it to a database on my system. No
matter what I do, even though I am pointing directly to the backup, SQL
Server is showing me a backup with the size and date/time of my last
backup of that same database and not the updated copy. The amazing thing
is that the backup file which I am trying to use to restore is pointed
to directly, while no previous backup which I have done is anywhere in
that same directory, and yet SQL Server is somehow restoring that
previous backup. Does anybody know what is going on here ? It just can
not be that hard to use another backup from the database on another
server to restore to my server.