June 2, 2009 at 5:05 am
hi
I have build a new Sql server Machine(64 bit )
i am restoring Master database ,
whose Backup has been taken just few seconds before restoring.
Its giving error :
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'master' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The used script is :
RESTORE DATABASE [master] FROM
DISK = N'D:\REPORT_DEV\MSSQL.2\MSSQL\Backup\master_backup_20090602.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, STATS = 10
The Server is in SINGLE USE MODE
Please help
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 2, 2009 at 5:18 am
Try appending "REPLACE" in the restore command.
.... WITH RESTRICTED_USER, REPLACE ......
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
June 2, 2009 at 5:22 am
Could it be that your backup device contains more than one backup?
I see you're using the option WITH FILE =1, which will restore the first backup which has been added to the device.
You can use RESTORE HEADERONLY to check what backups are in the file.
[font="Verdana"]Markus Bohse[/font]
June 2, 2009 at 5:29 am
i modified the script and use WITH REPLACE option.
Script used:
Restore database [master]
from disk = 'd:\report_dev\mssql.2\mssql\master_backup_20090602.bak'
with replace
but this time i got new issue of "version mismatch"
Msg 3168, Level 16, State 1, Line 1
The backup of the system database on the device D:\REPORT_DEV\MSSQL.2\MSSQL\Backup\master_backup_20090602.bak cannot be restored because it was created by a different version of the server (9.00.3310) than this server (9.00.1399).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 2, 2009 at 5:34 am
As suggested by Markus use "RESTORE HEADERONLY.." to verify that you are restoring from the right backup set. If you backup file has only one FILE, then you have to install the hotfix from http://support.microsoft.com/kb/959420 to bring this server to 9.00.3310. After that try restoring the master database.
Hope this helps !
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
June 2, 2009 at 5:44 am
Are you trying to restore a master database from a different server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 5:45 am
You cannot restore master database to a server which has different version. Both versions should be similar. As advised you should be able to upgrade it and then restore it with REPLACE command as the master datsbase already exists.
June 2, 2009 at 5:48 am
hi
yes from Different Server.
Following are the different versions
Backup taken from :
Microsoft SQL Server 2005 - 9.00.3310.00 (X64) Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Restore at server :
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 2, 2009 at 5:59 am
bhuvnesh.dogra (6/2/2009)
hiyes from Different Server.
That's not a particularly good idea. There's a fair bit of stuff in master that's specific to the machine it was built on. Taking a copy from one server and restoring on another may give you some odd problems in the future (service accounts and built-in logins specifically)
What's your goal here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 6:09 am
Actually ,
currently we have low configuration MAchine
and now we need to clone the databases to High config Machine.
bascially replica of current one to new server .
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 2, 2009 at 6:19 am
bhuvnesh.dogra (6/2/2009)
Actually ,currently we have low configuration MAchine
and now we need to clone the databases to High config Machine.
bascially replica of current one to new server .
I have no idea what you mean by Low config or High config, but you should be aware that most of the configuration settings for SQL2005 are not stored in the master database. Some are stored in the registry and others in the resourcs database. I really don't think that your approach is working and in the worst case you might end up with a non-working system.
[font="Verdana"]Markus Bohse[/font]
June 2, 2009 at 9:41 am
Hi Bhuvnesh,
Before restoring the master database to the new server you have to apply all the service packs/updates that are applied to the old server. Both the sql versions should match otherwise you cannot restore the master db. Also checkout which service pack is installed on both the servers using
select serverproperty('productlevel'). And there are some other settings which you need to checkout system specific things like RAM that can be alloted to sql server and to OS etc...... Once you had restored ur master db please test your new server thoroughly before making it production server.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
June 3, 2009 at 12:43 am
Accidently i installed Wrong SQl server Service Pack
Can some body told me How to remove it ?
Do i need to remoce complete Sql server 2005
it is not showing in Control panel >> Add remove Program
bhuvnesh
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 3, 2009 at 12:48 am
What did you install and what did you mean to install?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2009 at 12:49 am
bhuvnesh.dogra (6/3/2009)
Accidently i installed Wrong SQl server Service PackCan some body told me How to remove it ?
Do i need to remoce complete Sql server 2005
it is not showing in Control panel >> Add remove Program
bhuvnesh
Yes you should completely uninstall it and install it again. Once you install it, its not possible to roll back.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply