I have several questions about checking backup file :
a. When SQL Server receive a command :
BACKUP DATABASE test to DISK = '\pc1
etdbBTEST',
does it verify BTEST file before ending the job ?
b. Is there any special command for checking / verifying
a backup file ?
c. If I send a restore command using a file infected
by a virus, what action will SQL Server take ?
Thanks in advance
John S
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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?
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?
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?
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?
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.
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
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?
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?
Using SSIS, I want to check if a specific file exists on an FTP server or not. If yes, then i'll go with a flow, and if not, i'll go with another flow. Any help with that?
I need to be able to see if the incoming csv file had a head row different than the previous files header row. That will tell me that I have new columns.
OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
How to design ta SSIS package which loops through DESTINATION folder files and checks whether that file is there in the SOURCE or not.
If the file exists then I have to check the modified date on DESTINATION file if it is greater than 1 day delete that file. If the modified date is less than that SOURCE file then I have to copy that
file to DESTINATION<o:p></o:p> <o:p></o:p>
If there are files which exists in SOURCE and not in DESTINATION, then how shall we copy all the files to the DESTINATION that are created on the day of execution of package.<o:p></o:p>
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
I got full backup on daily schedule its taking more space on Drive because each file has more than 25GB.I am using SLQ server 2008R2 so I'm looking to take the backup with compression instead of uncompressed Backup. What are the impacts of compressed backup. Is there any problems with compressed backup while restoring the backup file.
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.
I've read quite a few KB articles on deploying SQL Express silently using the command line and a .ini file. Unfortunately I still have some questions before I can make my own .ini file. This is for a local sql server.
What is the INSTANCENAME attribute meant to be? If it is different from the server installed on my local machine which I have coded the application on, would it be an issue? What should USERNAME/SQLACCOUNT and SQLPASSWORD be? I can't remember having to setup these for the local database so I can't imagine what it's meant to be set as. What is the PIDKEY?Thank you in advance.
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I checked quickly one network and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
Hi there, After a bad server crash, the only remnant we have of our SQL server database are the .mdf and .ldf files in the MSSQL7/Data folder. Can we restore this database from either of these files and if so, what is the procedure? Sorry but I'm an SQL server newbie.
I dropped a SPROC, and was in the process of reacreating it when I lost power. Now I don't have the SPROC except for in an old backup file.
Is there anyway to get this without restoring the DB ? If not, can I easily restore to a different DB name and then delete it? My backups are on my live server and I don't want to overwrite my current DB with my backup :)
I rigth click my project , i choose add -> new item -> sql database and i write
SqlConnection cc = new SqlConnection(@"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|DataData.mdf;Integrated Security=True;User Instance=True");
when i code
cmd.CommandText = "Backup database Data to disk = 'C:\123.bak'";
i recieve Error Could not locate entry in sysdatabases for database 'Data'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.
My database's log file is full. i want to backup it . First i create a backup device named aa. Than i use Enterprise to backup trasaction log. But it popup an error messagebox. The title is "Microsoft SQL-DMO(ODBC SQL state:42000) The content is "write on 'aa' failed,status=112. See the SQL error log for more details.Backup or Restore operation terminating abnormally. What's the matter ?
I am using SQL Server 7 and have about 5 databases. One of them has a data file of about 10 Meg, and most of the others are larger. I do a nightly backup to both a local and mapped drive. On both, the size of the backup file for this database is more than 500 Meg, but the rest appear to be an appropriate size. Does anyone know why this would be happening? The database works fine, it does not get a lot of insert/delete activity and I run DBCC every weekend. If anyone has any ideas I would sure like to hear from them.
