Backup - Restore Questions
Feb 12, 2008
Hello,
1) We run full backup on SQL server 2005 on weekly basis (Sundays), diff - every night, and backup of tran logs every hour. Let me descrive the following scenario: if our server would crash on Wednesday at 5 pm, how can I restore it properly? Do I need to restore it from full Sunday backup + latest diff backup from Tuesday night + latest tran logs from 4 pm? We have full recovery mode on the databases.
2) If we change recovery mode from full to simple, is it possible we have data loss in case if the server crashes let's say at 1 pm, and we have full backup only from previous midnight only?
Thank you,
Julie
View 7 Replies
ADVERTISEMENT
Nov 15, 2004
Hi everybody,
I am trying to figure out SQL Backup and restore and I was wondering if you guys could enlighten me.
I figured out how to restore a DB on my local sql box but let's say my server catches on fire and my backup is on tape, how do I restore my DBs on a different box ?
I have a second machine I built for test but apparently it won't restore from the tape.
What is the best way to back up your data in case of major hardware problem ? And how to restore it to a completly different server. I am running tapeware w/ sql agent.
Thank you, any help will be greatly appreciated. -Seb
View 2 Replies
View Related
Jun 25, 2015
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
View 4 Replies
View Related
Apr 19, 2007
Hello Gurus,
I wanted to restore a database to multiple data files from backup which has got only few data files. Is it possible do this?
Example : Backup has 4 data files and I need to restore it to 8 data files.
Thanks
Subbu
View 1 Replies
View Related
Aug 3, 2015
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
View 8 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Oct 17, 2006
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
View 3 Replies
View Related
Mar 22, 2000
Using Backup wizard it would not let me choose to reorg data and index pages and
also update statistics. I don't understand why these are mutually exclusive. Does
a reorg update statistics for non-unique secondary indexes that are not clustered?
I was also asked if I wanted to backup to a backup device or to a file. In 6.5 I would backup
to a backup device which was a file on my drive. In 7.0 I am missing the distinction of
one being better than the other. Can anyone give a clue to the clueless?
View 3 Replies
View Related
Sep 20, 2006
What is the best way to set up this back up solution. We would like to be able to restore to a point in time.
Here is what I'm planning on doing.
Full backup Sunday nights
Diff backups M-F
log backups Sunday - Friday
Now i'm guessing that we need to set up 3 jobs. The full backup job, the diff backup job and the transaction backup job.
If so i would run the full backup at 6PM on Sundays then have a log job run every 30 minuets from m-f and have a differental backup job run once M-F.
This way i can restore to a point in time correct. I'm just confused with the log backups.
If i need to restore to a point in time say Tuesday night and I take diff backups everyday at 6AM. I would first restore the full sunday backup then the Tuesday diff backup and then the all the log backups that took place after the diff backup correct?
thanks for your time.
View 3 Replies
View Related
Aug 8, 2004
If I have a full backup scheduled at 6pm and a scheduled transaction log backup begins during the 15 minutes the full takes to complete, how would I use the transaction log backup during a recovery sequence?
Does the transaction log backup contain transactions that are captured in the full recovery since they both occured concurrently? Using RESTORE HEADERONLY I see they both are stamped as finishing at the same time.
Would I restore the full and then restore this tran log backup or go to the next tran log backup?
Thanks,
Ken
View 1 Replies
View Related
Mar 20, 2007
In SQL 2005 SMO
documentation, there is a DeviceType enum:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.devicetype.aspx
It
seems there is no doucmentation about 'LogicalDevice', what's the
difference between that and 'VirtualDevice' ? (for the latter, I guess
we need to implement a VDI driver)
Another question is that,
suppose we want to backup a database to a hard drive file, I could use
either T-SQL or SMO, what's the advantage of using SMO?
Thanks!
View 3 Replies
View Related
Aug 24, 2007
I made a dsicovery today that must change the way I do backups. I realized that each backup ADDs the whole database to the previous backup without deleting the previous instances (for previous backup days). If it is true, and I want Jens or someone else to confirm it, then what shall I do?
I do not need this old archive. I want to just store the latest version of my database. What I've had so far is a waste.
The log files also grow to enormous size for no reason.
Shall I delete all backup files each time and recreate them before doing backups?
Also, I have trouble opening up log files. I think I did it once or twice in the past but now do not remember how I did it. The system is asking me for a proper editor and anything I tried did not work.
How shall I open a log file
Thanks.
View 5 Replies
View Related
Oct 14, 2007
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
View 16 Replies
View Related
Jul 23, 2005
Hi All,I have several questions about checking backup file :a. When SQL Server receive a command :BACKUP DATABASE test to DISK = '\pc1etdbBTEST',does it verify BTEST file before ending the job ?b. Is there any special command for checking / verifyinga backup file ?c. If I send a restore command using a file infectedby a virus, what action will SQL Server take ?Thanks in advanceJohn S*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Nov 17, 2006
Hi,
I am thinking of using SQL and VB express for a project. Reading different versions information lead me to the following understanding on bakups and report services. Please comfirm my understanding or correct me if I am wrong. Any suggestion is always welcome.
1. SQL express does not include Agent Job Service so I cannot schedule a backup job or daily report to run automatically at a specific time. Is there a work around on this?
2. If I install the SQL express with the Advance Services edition, I still have to install the express tool kit to get the business intelligence developer studio in order to generate the reports. Do I have to install both on the same machine (the server) or the advance edition on the server and the tool kit on the client machine?
Thanks,
Bernie
View 4 Replies
View Related
May 17, 2006
I had to replace my hard drive which was going bad. I reinstalled Sql 2005 and now need to restore the backup that I have. I selected Restore Database and chose From Device, and selected the backup location on J: drive and selected the backup file which has no extenstion. I checked the checkbox next to the backup set to restore. Click OK and then I get this error message:
Restore failed for Server
System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
I don't know why it is looking for a MDF file as this was never in the Backup folder. Can someone please help me on this? I thought I was doing the right thing making a backup all along and now I can't restore it!
View 2 Replies
View Related
Dec 17, 2001
Is it possisable to restore from tape when the backup was taken from a
different server. ServerA has the tape drive which performs the backups
to tape, but ServerB which is under a different domain wants to restore ServerA database on ServerB, both databases on the two servers are the same
is it possiable to restore a database on ServerB using ServerA file?
View 2 Replies
View Related
May 7, 2001
hello all !!
I suffered a crash on one of my disks on a server runing MS-sql 7.0,and found out my backupexec dosnt really restore a runing version of my database.
which solution would you recomend ,taking in acount i may have a copy of the database on another computer,which files i can restore?
thanks
yochai lam
View 2 Replies
View Related
May 24, 2001
Hi I am operating my databases under "trunc log on checkpoint" mode as I do not need point of failure recoverability.I was performing a recovery test using a backup which resulted in it being unsuccessful .Error Message received was
"Recovery has failed because a nonlogged operation could not be redone.Use the RESTORE statement to restore all data in filegroup PRIMARY to a pt beyond the nonlogged changes"
Isnt sql*server supposed to prevent a non-logged operation while a backup is in progress?Also how do i prevent such a situation from recurring.
If i switch to single user mode before a backup how do i prevent exisiting users from accessing ... without bouncing my sql*server?
View 1 Replies
View Related
Sep 25, 2001
Hi Everyone,
I did a restore of database the user the original database is admin where it says the user is dbo.
After i did the restore i was able to add admin as user and dbowner but when i tried to change the user it said the user already exists.
How do i create the same..enviroment as or the original system with users..i saw a problem i i drop the user and them recreate it then it cannot find the object or the object which are owned by it are lost..could any one help me with this ...so as how to and what do i need to keep track of while restoring the database and configure the server back with same user names and permissions.
Thanks.
Kavitha
View 1 Replies
View Related
Sep 15, 2000
Hi,
I have two databases on two different SQL Servers,our application is updating these two databases simultaniously.So every time these two databases will be in synchronized state.
Now i want to schedule backup for these two Databases.How can i take backup of these two databases at single point of time.
so that if i restore these two backups on corresponding servers,There should not be any conflicts.
Any suggetions please....
Ananth
View 1 Replies
View Related
Sep 6, 2000
How can I backup and restore a database from one server to another. I am new in this business. I need your help please!!!
View 3 Replies
View Related
Jan 29, 2001
I'm trying to backup a database to remote server first i did add a device using USE master
EXEC sp_addumpdevice 'disk', 'NetworkDevice', 'cabaanpubs.bak'
then
BACKUP DATABASE pubs TO NetworkDevice
it gives me an error saying
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'NetworkDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
I don't really understand what's goin.
basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.
Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.
Is there any other process that's much faster to implement.
i appriciate your support and help .
View 1 Replies
View Related
Aug 30, 2000
Whenever I restore a backup on sqlserver 7.0 and create a new database from it, I always end up having problems with the users existing in the backup.
To enable those users to have access, I end up creating logins with those names and when I try granting those users Database access, I get the error message that the user already exists in the database.
Could some one tell me the right way to restore the backup and having all the users of the database also restored in the proper manner.
View 3 Replies
View Related
Dec 13, 1999
WE are using sqlserver 7.0 & backing full database using Seagate backup ver 7.2 on a tape drive.
I have following questions if anybody could help
1]How do I restore a single table backup from the tape backup without restoring the full database first on the disk & then running import/export
utility to transfer/ copy that particular table to production database.
Is there any third party backup utility which allows for a single / selected table restore?
2]What are the steps or if anybody has the script for tansfering logins/users
with permisssions & without deencrypting the passwords when transfering from one server to another server.
View 1 Replies
View Related
Nov 26, 2006
Hi there,
I am new to administration. i would like to create a development environment out of production database.
for this i have to backup the production database on a regular basis. then use this backup and restore it to a database on different. i can do this task, but i want to automate this task to do it on regular basis.
i would like to schedule my backup time. and immediately after backup i want to restore it to the database on other server, over writing existing data. what is the best way to do this???
thanks,
kishore
View 6 Replies
View Related
Apr 20, 1999
I have a question related to SQL 6.5. We have a SMS 1.2 server which uses SQL 6.5. Two months ago, one of the NT admins did a NT backup of SMS database(sms.db) and then formatted the HD. Now, we need the SMS server back again. We installed the NT server, SMS server snd SQL server on that machine. The problem is, we now want the SQL server to recognize our SMS database (SMS.db file). We don't have any SQL backup of any database (that means, no copy of master database, no copy of other databases. IS THERE A WAY TO RECOVER SO THAT SQL SERVER RECPGNIZE THE SMS DATABASE(SMS.DB FILE)
Please help me out.
I would really appreciate that
Jazib Frahim
View 2 Replies
View Related
Sep 21, 2006
Does some one know how to back up a dts and then restore from a backup?
View 5 Replies
View Related
Feb 2, 2004
I have a database xxxx on server a, I want to copy this DB to server b.How can I do this By back and restore.
WHere can take this back up?I want to take this to a personal folder and wanted to restore to server b from this folder.Is it possible?
Thanks.
View 2 Replies
View Related
Apr 11, 2008
Hi all.,
How to take Backup for whole database and how to restore it.,
which would be the efficient way.,
View 3 Replies
View Related
Apr 16, 2008
Hi,
Can a SQL server 2005 db be backed up and restored on a SQL Server 2008 db ?
Thanks
View 5 Replies
View Related
Jun 2, 2006
Hi
I have 2 databases namely A & B respectively..... I have written a stored procedure that takes the backup of database A ....... Both the databases are operational .... Can I restore this Backup of Database A ...to Database B .... Is it possible. ....
I have read soimething that we can do it but with the Restore command we have to use the with move option...
I dont seem to understand this can someone please help me on this one....
Thanks .......
Kid.
View 1 Replies
View Related
Jan 30, 2008
I am trying to restore the backup from enterprise management studio
i have selected the source of resource from device.Its not displaying the mdf etc files in the select the backup sets to restore..
I can't proceed further.
Please suggest me..
View 6 Replies
View Related
