SQL Server Performance Forum – Threads Archive
restore database
HI Guys, i think its kind of novice quastion,i am trying to restore database test from invest database backup is that possible or not if not possible,how to do this task i need to check maintanance job before creating in prod or on real databases.i planned this way create a new database and restore rhat database from other database back up create maintanance plan for that newly created database.if i am wrong let me know how to do this task.
error.TITLE: Microsoft SQL Server Management Studio Express
—————————— Restore failed for Server ‘REETHUSQLEXPRESS’. (Microsoft.SqlServer.Express.Smo) For help, click:http://go.microsoft.com/fwlink?Prod…ceptionText&EvtID=Restore Server&LinkId=20476 ——————————
ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘test’ database. (Microsoft.SqlServer.Express.Smo) For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.3042.00&LinkId=20476 ——————————
BUTTONS: OK
——————————
SRJ2005
If I understand your question correctly…
You are trying to restore Invest database on to Test database…
Yes, it is possible you can restore one database backup on to another database but you may need to use WITH MOVE option…. http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1102260_tax301326,00.html?adg=301324&bucket=ETA
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Refer to the books online about using MOVE Option and also check on source server for the path & location of the data files, try to recreate same and if not using MOVE option you can achieve the restore operation. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
yap i did it using move options, thanks. SRJ2005
are both the servers on SQL server 2005
i think i am missing part as i restored database using move option, but i can see logical file name as restored from database name
ex. database to be restored testdb
from database testdb1
after creating this way i see logical name is different from original database.eventhogh i changed it it appears same.
Actually how it works if i create database test and restore it from other database on same server.this way i need to do.if any one have idea please let me know, SRJ2005
WITH MOVE will move the path details from the backup to the specified location, logical file name remains same. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
so that we can do all database activities without interupting any future course problems.
actually i will post request what i got from dev team for this request i used below statement,
RESTORE FILELISTONLY
FROM DISK = ‘D
rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup est1.bkp’RESTORE DATABASE DBTest
FROM DISK = ‘D
rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup est1.bkp’WITH MOVE ‘test’ TO ‘D
rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTESTDBTest.mdf’,MOVE ‘test1_log’ TO ‘D
rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTESTDBTest_log.ldf’GO
request.
Please create new database called data_dictionary in all environments cev.test and prod and restore it from the latest backup available from prod SQL2005
is this correct way or i made mistake please let me have some idea. thanks in advance. SRJ2005
Correct, as you have moved the path as compared to Dev from old to newer location.
Check the difference from the RESTORE FILELISTONLY values to the statement you have used, it is the clue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
THANK YOU, I did not catch exactly ut i understand / SRJ2005
in this case i have taken one full backup and log at a time and i tried to restore them immdtly but i got this error .why it was like .
The log in this backup set begins at LSN 5000000007200001, which is too late to apply to the database. An earlier log backup that includes LSN 5000000005400001 can be restored.
SRJ2005
See the mistmatch LSN number and due to that it fails, only BACKUP Database takes higher hand in this aspect. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
really i did not get it, what is the way i can perform this. SRJ2005
Run the RESTORE HEADERONLY command for both backup file and check the LSN mismatch..
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
LSN is a Log Sequance Number ,when you restores a T-Log it will search and compare for the last LSN of last successful restored T-Log with the newer one you applied, if it mismatch it will fail. Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
Tlog backup FirstLSN should be greater than or equal to Full backup LastLSN… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Due to the reason of full database backup and next process of log backup has difference in LSN, where you can see the LSN information is displayed when you have tried to restore the log backup that has been performed after full database backup.
quote:Originally posted by reethu
really i did not get it, what is the way i can perform this.
SRJ2005
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thank you i got it .but i come across another issue.
please may i know how to do it, i am trying to restore database from network path but this database has replication on (publication)
so how can i perform this restore without distrub that publication.
after restore publication should get immediate access to the users, SRJ2005
Look under BOoks online for " Backing Up and Restoring Replicated Databases" topic in this case and you can use KEEP_REPLICATION option. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi guys actually i have no test environment to test this kind of task. that is only the reason i am taking gurus advise donot think otherwise, when we back databases to disk we split and zip them, so most of the times it splits into multiple files,when i restore them how to do restore.
thanks. SRJ2005
If I understand, you are taking a database backup to a file and using the zip.exe to zip and split the files… Don’t use zip.exe to slit the backup files when you are zipping… You can take the single database to muliple backup files instead of huge one file and you can zip them and unzip before restore and single backup’s multiple backup files can be used for restore… BACKUP DATABASE <DBNAME> TO disk = ‘c:ackup1.bak’, disk = ‘c:ackup2.bak’… See BOL for more details…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Even though if you zip them into various files at the end when restoring back from WINZIP you will have 1 backup file, so in this csae it is always best to see the contents of the backup by using RESTORE FILELISTONLY and based on this (backup file) during restor it will be accomplished. FOr the examples of restoring replication etc look at BOL as advised. Don’t take our advices as final and in order to ensure you must test them before deploying in production. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
You can also use third party tools like LiteSpeed etc.. to compress the backups.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Is there any specific reason to compress the backups? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
storage issues. SRJ2005
If you do not have any problem with WINZIP in compress and uncompress method, then it should be ok and ensure tocheck the backup consistency before and after. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>