Backup File Names Truncating
Apr 18, 2002
I'm performing a full backup of a database, and the file name that I am creating for the backup is truncating. This keeps happening and can't figure out why.
I have a stored proc that I run:
CREATE PROC usp_Backup_old_srvra_to_srvrb
AS
DECLARE @DOW varchar(10),
@Filelocation varchar(50)
--Get Day of Week and set backup file name
SET @DOW = DATENAME(dw,GETDATE())
SET @FileLocation = 'SERVERAE$MSSQL7BACKUP'+ @DOW + '__old_BKUP_SRVR_0418_0800.bak'
BACKUP DATABASE TEST
TO DISK = @FileLocation
WITH INIT,
STATS
RETURN (0)
The backup file that was created ended up being:
THURSDAY__old_BKUP_SR
Any idea why it didn't pick up the whole file name that I wanted?
View 2 Replies
ADVERTISEMENT
Nov 13, 2007
Hi All,
any suggestion on creating date time stamped files names for backup file.
I want to create new file for daily backups with date time stamp so i can use Maintenance plans to clean older files in each 4 month cycle.
thanks,
View 8 Replies
View Related
Dec 20, 2007
I've got a Sql server 2000 box that currently backs up to tape in full every night. I also want to back the box up off site but not in full (as 30GB is a little too much to transfer every evening).
So my plan was to do a full backup to tape at 7pm then a differential at 8pm (to transfer off site).
The problem I am having is that after my differential has been done the logs get truncated so if I want to replay them for any reason I need to get that differential back to site.
Anyone have any suggestions please?
View 7 Replies
View Related
Mar 1, 2015
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
View 1 Replies
View Related
Dec 11, 2001
Can someone please tell me where in SQL2000 I can truncate the log file and
set the database to truncate the log at checkpoint?
Thanks,
Dianne
View 3 Replies
View Related
Dec 8, 2001
Hi everyone,
Although I truncate the log file and I have no pending transactions, its size does not shrink at all (it stays a 0.5 GB).
Does anyone know why or what can I do to solve this issue.
Thanks,
Vasilis
View 1 Replies
View Related
Jan 30, 2004
Hi,
I am having a problem with growing transaction log size, it has grown to 10 gb and I need to truncate it. How can I do it without interfering the users since it's our production database with 24/7 operational service.
Thanks in advance!!!
View 4 Replies
View Related
Sep 16, 2004
I have a Database of size 200 MB and my transactio log is 13GB(very high).So can I truncate the log file by taking a fresh full backup?
Thanks.
View 3 Replies
View Related
Feb 8, 2007
Hi,
I'm using sp_OAMethod to write to a text file, like this:
DECLARE @i INT, @File VARCHAR(1000), @FS INT, @RC INT, @FileID INT, @Date DATETIME
SET @File = 'E: extfile.txt'
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1
EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date
This always appends to the file. I want to truncate the file and write to it afresh.
Please inform me how to do that.
View 3 Replies
View Related
Jul 14, 2014
I have a production database which uses merge and snapshot replication. The Merge is for 3 tables. The snapshot is to update the rest of the data once daily. I use a Full recovery model and perform database backups (full, differential) and transaction log backups.
I have a database optimization plan which runs 4 times a week. This plan performs and integrity check and rebuilds the indexes. This optimization plan is growing the transaction log by about 8MB each time it is run and we are running out of space on the drive for our log files. The space is not being reused.
I saw in another post where Gail Shaw suggesting using SELECT name, log_reuse_wait_desc FROM master.sys.databases to see why the log space is not being reused. On the database in question, the above returns "REPLICATION".
A colleague tried to backup the transaction log a couple of times to truncate the log this weekend. She was going to perform a DBCC Shrinkfile command afterwards. But the truncate failed. Again looking into things it seems replication prevented the truncation.
We are looking at stopping the merge replication or even removing it to truncate the log file and then recreate the merge replication. How to handle shrinking the log file for now and then seeing if there are any checks or changes I can perform which will allow the transaction log space to be reused.
View 7 Replies
View Related
Jul 31, 2007
Hi,
My ReportServer Log File (ReportServer_log.LDF) is now 1 GB in size !!
How do I truncate it ?
Is it OK if I detach the ReportServer DB , delete the old log file and attach the DB with a new Log file ?
Can Anyone tell me the repercussions of this ?
Thanks in Advance for your Reply,
Regards,
Sundar
View 3 Replies
View Related
Apr 18, 2007
Okay, here is a real mellon scratcher. I've got a server I've gotsemi-automated backups on. A typical backup looks like:BACKUP DATABASE MasterTO DISK = 'c:ackupsMaster20070418.bak'WITH FORMATThen I'll do a log backup if applicable (Not for Master). I then getsome error about the device being offline. But if I run:BACKUP DATABASE MasterTO DISK = 'c:ackupsMaster20070418.log'WITH FORMATthen it works fine. The ONLY thing I've changed is the extension onthe backup file. But that is what I wanted to name the log. As aworkaround I have the backups saved as:Master20070418-bak.log and then name the log files the way they shouldbe. That works, but I don't like it.Security is full access to Everyone, so I don't think it is an access/permissions issue.Any ideas?Thanks,-Utah
View 1 Replies
View Related
May 27, 2015
I need to move specific files from a server to another server on a monthly basis. There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server. I would like to easily add or delete the file list as needed. I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them. With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable. Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....
View 10 Replies
View Related
Jan 19, 2007
I'm exporting reports daily to a file share and I need to rename the reports with a pseudo time stamp.
Example: I have a report named "Disk Usage" and when I export (using a data-driven subscription) I want to rename it "Disk Usage - (Jan07)" - or something to that effect.
Can anybody tell me how to accomplish this.
View 1 Replies
View Related
Jul 11, 2007
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?
View 11 Replies
View Related
Dec 5, 2006
What is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
View 3 Replies
View Related
Jan 2, 2008
Hi All,
I am using query analyzer to execute my sqls .
I want to take out .mdf and .LDF files and put it in another machine .
But there is no .mdf or .LDF file with prefix as my database name . I think the file name might be something else .
Please help me in finding out the .mdf and .LDF of a particular database .
Thanks ,
Sushi
View 6 Replies
View Related
Jan 2, 2008
Hi,
Every day I ftp a zip file. Inside the zip file are 4 text files. Every day the names of the 4 files are different, so I can't know beforehand what the names are.
I am wondering what is the best (most efficient) way for me to get the file names inside variables? Should I use a script task? Or is there another way to do this?
Thanks
View 3 Replies
View Related
Sep 8, 2015
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.
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
May 29, 2002
Hi ALL!
Our application needs to search for exact match of file names (which will be 70 characters in length) in a table which has thousands of file names in its file_name field. What is the best way to this? Shall we index the table on file_name column? Or any othe way, as the application will do this search several times all day. Any ideas are appreciated.
Thanks.
KSN.
View 1 Replies
View Related
Apr 24, 2002
Hi,
Does anyone know how to change the logical file name of a database?
Appreciate any help.
Steve
View 1 Replies
View Related
Oct 5, 2000
I receive several TXT files daily that need to update information in SQL Server databases. The process requires that all TXT files be appended to a master file and also update individual files' information based on the TXT file name. For example:
File TABLE1_x_ddmmyy.TXT (ddmmyy = date, x = "O" or "B") is to be appended to the master file and also update SQL table "TABLE1" by setting a flag for those records in the table that match a unique key that is provided in the TABLE1.TXT file.
In VFP, I had the following process in place:
a) open the TXT file.
b) read its file name and open the corresponding VFP file
c) update the VFP file based on the key provided in TXT
d) append the key to the master file.
e) repeat c-d for next record in TXT
f) repeat c-e for next TXT file
Using the same process with ADO takes a considerable time since I am processing one line at a time.
Is there any way to do this using a DTS package of some sort? How can I read the TXT file names in SQL Server?
Thank you.
View 3 Replies
View Related
Mar 26, 2015
I have below requirement and it needs to be addressed using sql.
There are 18 text files(file names are dynamic) and each file having different row counts in some “xyz” folder.
Here, I need to generate 19th text file which has the list of file names with row counts.
I am planning to import all of the text files into database and trying to generate 19th one.
View 1 Replies
View Related
Jul 23, 2005
Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?
View 1 Replies
View Related
Jul 28, 2006
I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.
I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.
thanks,
Scott
View 3 Replies
View Related
May 9, 2008
Hi SQL server experts,
I'm trying to restore from a backup file.
I need to restore the datafile and logfile to different names.
I modified the options page, changed the 'Restored As' file name.
I clicked on the General page and Clicked on the Options page again, the old values came back.
How do I handle this?
Thanks in advance.
Pingx
View 3 Replies
View Related
Feb 20, 2007
Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
View 14 Replies
View Related
Feb 19, 2008
Background:
I am new to SSIS.
I have a data flow task within a For Each Loop Container.
Its reading a Demilited Flat File and inserting into a DB table.
The Flat File Source reads a specified folder and picks up all files with extension .txt
Question:
How can I record and save the file names the package picks up and loads it in ?
For eg:
If I have under C:Test folder
File1.txt
File2.txt
File3.txt
And the package picks up all the files above and loads them in, is it possible to read the file name its processing ?
I have a need to read these file names ( in this example File1,File2,File3 ) and store them in a DB table.
Appreciate your help.
Thanks in advance.
AkB
View 4 Replies
View Related
Mar 27, 2006
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
View 2 Replies
View Related
Apr 24, 2007
Hi,
A collegue of mine is having some issues. I hope someone can help
Hi all,
This is my first call for technical assistance so go easy on me.
I'm having a problem in one of my SSIS packages. In brief, the process involves copying the rows from an untyped table to a typed table. There are about 45,000 rows in this table and during the copy ("OLE DB source" to "OLE DB destination") the process appears to hang for about 5 minutes eventually crashing with a "The buffer manager could not get a temporary file name. The call to GetTempFileName failed."
After several attempts using all the trace info I could master this is the order of events with some interesting numbers and facts:
1. The SSIS package goes swifty through "Validation", "Prepare for Execution" and "Execution" in less than 1 second, with "Execute phase is beginning" being the last message on the "Progress tab".
2. Using Performance counters I note that in the next 5 mins the values for "BLOB bytes read" slowly rises and then after a couple of mins so does ""BLOB files in use" the latter reaching a figure of 65534
3. When this figure is reached, SSIS starts creating thousands of zero-size files with the name DTS####.tmp (where #### is hex e.g. DTSB4C1.TMP) in the TEMP folder (C:Documents and Settings<username>Local SettingsTemp in my case).
4. When I started running this package there were 130 files in my TEMP folder; As soon as the combined total of files in TEMP reaches 65664 (i.e. 65534+130), SSIS starts producing the errors list which includes the one I listed above and eventually it clears the TEMP folder down to the original 130 files.
5. My conclusion (thus far) is that SSIS creates all these 1000s of tmp files but in my case hits some kind of maximum (either a folder limit or runs out of hex combinations for the file names) and then crashes.
6. The only thread I found on the internet suggested setting up an environment variable "BLOBTempStoragePath" and assigning a value of "C:Temp1;C:Temp2;C:Temp3;C:Temp4" so that SSIS can span across a number of "temporary" folders instead of the 1 default folder contained in the "TEMP" environment variable.
7. Setting the above environment variable in Windows 2000 did not work for me (tried it as both a user variable and a system environment variable). So here are the facts so far - ANY assistance will be hugely appreciated - I have no idea why all these temporary files are being generated - I have created SSIS packages handling data sets 10 times bigger than this one without these problems so I don't think it's size related.
View 3 Replies
View Related
Apr 30, 2015
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
For instance:
database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf
Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].
View 3 Replies
View Related
Mar 19, 2008
Hi,
On x64 versions of Windows server 2003, there is a folder called "Program Files (x86)". The use of parenthesis in this name has caused a few issues so far, particularly with connecting SQL Server 2005 to Oracle using SSIS. After several hours of searching I realized that the parenthesis was causing it, and eventually I found that the best way is to avoid creating such a name while installing the OS itself, by using winnt.sif. This would be better than renaming it and changing registry entries after the installation is done.
I realize this may not be the best question to ask of Microsoft, but why not just call it "Program Filesx86" by default so no special procedure has to be done to get around these issues? The use of parenthesis is likely to cause even more compatibility issues with other software later?
BTW I use Microsoft products regularly and love most of them
View 8 Replies
View Related