How Can I Take Backup Of Logfile Of One Specific Database?
Jul 20, 2005Hi,
I want to take backup of database logfile periodically and
automatically. What should i do then.....
Thanks
Hoque
ADVERTISEMENT
Schedule Backup Of Specific Database While Only Saving The Last 7 Days In Repository
Apr 15, 2008
Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple
With SQL Server Agent I am running schedule task of the backup script
The question is:
I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on
I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP
I am in situation that the command above only append to the backup file and without deleting the expired backup days.
What I need to change in the parameters that the script will delete the expired backup days.
Thanking in advance everyone who assist.
Schedule Backup Of Specific Database While Only Saving The Last 7 Days In Repository
Apr 19, 2008
Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple
With SQL Server Agent I am running schedule task of the backup script
The question is:
I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on
I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP
I am in situation that the command above only append to the backup file and without deleting the expired backup days.
What I need to change in the parameters that the script will delete the expired backup days.
It is too much work when working with Database Maintenance Wizard.
lets take a scenario that I have 10 database.
I would like that each database will backup into seperate file.
I need to create 10 * 7 Maintenance plans.
I have created a directory with the name SDBbackup.
Now under the directory SDBbackup I have created 7 directories (1,2,3,4,5,6,7).
Each directory is for Sunday=1 , Monday=2 , and etc.
It's very much work if I need to accomplish it with Database Maintenance Wizard.
This is why I am asking a specific question regarding T-SQL.
Thanking in advance everyone who assist.
Shrinking The Master Database Logfile
Feb 9, 2001Hello
Can anyone help, I have a master database on my server which has an extremely large log file size. I need to shrink this down and so I have tried using dbcc shrinkfile (master, 50, truncateonly) and dbcc shrinkdatabase although it does not seem to reduce in size
Please can anyone help
Database And Logfile Growth When Attaching SQL2000 DB To SQL2005
Jun 25, 2007Can anyone tell me why my SQL2000 database has grown aprox 15 % and my Log file 20,000 % when I attach it to SQL2005 .I've Thousands of Databases to Upgrade, but with the log file increasing to more than the size of the Database Its going to be a struggle !
It also takes a fair ammount of time to attach,
I suspect there is some reindexing going on , as when I try to reattach to SQL 2000 I get index errors ?
Is the re anything I can do in advance to reduce the database growth ?
I know I can truncate the log afterward but the peak diskspace consumed during my Migration may be an issue !
Thanks for any help
Urgent DataBase Restore Logfile Path Error Message
May 8, 2007I am having difficulty restoring a database (DB_1) with 2 datafiles and one log file, DB Structure is as following:
Filegroup PRIMARY with file name 'fnm_data' with physical file name and location D:dbfile_1.mdf,
Filegroup 'FG1' with file name 'fgnm1_data1' with physical file name and location F:dbfile_FG1.mdf,
One log file with file name 'fnm_log' with physical file name and location (E:loglog_db.ldf)
{Note} Logfile resides on E drive whereas Datafiles reside on several other drives.
I need to restore the DB on another machine. And I need to move the log file to a drive other than E.In this case it is K drive. Hence I have used the following code,
restore log DB_1 from disk='M:kupccciclog.txt'
WITH RESTRICTED_USER , MOVE 'fnm_log'
TO 'K:DBlog_db.ldf', recovery, replace,RESTART
go
When I try to restore from backup files, I keep error message saying "Physical file name E:loglog_db.ldf may be incorrect." 'fnm_log' cannot be restored. Use Move command to identify a valid location for the file.
Even though I am using Move command to move the log file.
Then I found that if I create a drive with E: and a folder named 'log' then restore program runs alright. That is, when E:log exists then code does run smoothly. All the restore code is looking for the presence of path of the log file from where it is been backed up. In this case it is E:log folder. Once the database is restored I could kill that directory and nothing happens. Also log file has been restored on K:DB only.
Why the restore code is looking for the initial drive letter and path even though I have used Move command? Is there an issue in my code?
Any help will be highly appreciated.
Backup But Only Restore Specific Tables
Sep 29, 2014In SQL 2008, there is a way to backup a database, but specify the tables backed up, and, during the restore on the destination server, restore only those tables in the database?
View 2 Replies View RelatedThe Backup Set Holds A Backup Of A Database Other Than Existing Database. Restore Database Is Terminating Abnormally
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
Can You Backup And Restore Tables That Belong To A Specific Schema
Apr 30, 2008Hi all,
does anyone know of a way in which you can backup and restore objects that belong to a specific schema?
So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.
--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2
Thanks in advance,
Andy.
EDIT: I forgot to add that I want to backup the tables and the data contained within.
SQL Server Restoring Specific Objects - Full Backup
Mar 25, 2008
Hi,
Is there any option to restore a specific object from a full database backup file?
Whqt Is The TSQL Statement To Make A Backup Of A Specific Databese??
May 10, 2008i have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????
pls help
BACKUP LOG Cannot Be Performed Because There Is No Current Database Backup. BACKUP LOG Is Terminating Abnormally.
Jan 31, 2008
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
SQL Server 2008 :: Query To Delete All Backup Files Older Than A Specific Date?
Jul 29, 2015I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'
Who Can Tell Me What's Mean Of My Db Logfile?
May 13, 2004In my database named aa ,there are always some words like this:
2004-05-12 09:44:48.96 spid73 Starting up database 'aa'
2004-05-12 09:44:49.17 spid73 Starting up database 'aa'
2004-05-12 09:44:49.36 spid73 Starting up database 'aa'
2004-05-12 09:44:49.56 spid73 Starting up database 'aa'
2004-05-12 09:44:53.37 spid79 Starting up database 'aa'
2004-05-12 10:02:41.96 spid73 Starting up database 'aa'.
2004-05-12 10:02:42.24 spid73 Starting up database 'aa'.
2004-05-12 10:02:43.61 spid73 Starting up database 'aa'.
2004-05-12 10:02:43.80 spid73 Starting up database 'aa'.
2004-05-12 10:02:44.02 spid73 Starting up database 'aa'.
2004-05-12 10:02:44.24 spid73 Starting up database 'aa'.
2004-05-12 10:36:42.84 spid82 Starting up database 'aa'.
who can tell me how to resolve this problem?
SQL Security :: Full Backup Needed After Restoration Of Database Before Transaction Log Backup
Jul 15, 2015We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedRecovery :: Differential Backup Much Larger Than Database / Full Backup
Nov 16, 2015I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedWhat If The Logfile(s) Are Lost?
Nov 27, 2000Hi, maybe someone has some clues to the following situation. Suppose from a SQL 7 database, only the log .ldf files are lost. All datafiles are present. Suppose there is no backup of this database. SQL Server will put the database in the SUSPECT status. The following question is very interesting to me: Can one recover from this situation??? I ask this, because when datafiles are lost, it's obvious that you have lost data, but since the transaction log files are a "bit special", I just wonder how one can survive such a situation.
I know that IF a database has only ONE log file associated with it, and this one log file gets lost, SQL Server will create on startup a new log file. But with multiple log files, it does not behave in this sympathic manner. Is there perhaps any way to recover (for example, an undocumented dbcc command or so) ?
I surely will appreciate any tip or suggestion !! Thanks!!!
Shrink Logfile ?
Jun 9, 2000How do I shrink my transaction log file ? The physical file are 10Gb in size but only contains 100Mb data so I want to shrink it to save diskspace. I tried DBCC SHRINKFILE command but the file dont shrink.
Any other methods to shrink a transaction log file ? Like backup/restore ?
Help !
Thanks
How To Clear The Logfile
Jan 15, 2006Hello guys!
I would like to clear the logfile. Could anybody tell me how to do this?
What I would like most would be to run SQL-Server without any log-file (while development). But I suppose that this is not possible.
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
Erase LogFile
Feb 14, 2006How to erase the content of logfile?
View 2 Replies View RelatedTruncate Logfile
Feb 3, 2007Dear all,
I am using SAP with MSSQl 2005. My transaction logfile is full. I took backup of transaction logfile and it should be truncated and shrink after the backup, but it did nothing. There is no error but the file size remain same.
I tried from SQL server management studio selecting database--right click--shrink--file. Then i try to release unused space, shrink, but not worked.
I also checked these steps while the SAP system was stopped.
Can anyone guide me what to do now?
Thanks
Imran Hasware
Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)
May 23, 2015I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
Backup Failed To Complete The Command Backup Database
Aug 4, 2007Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View RelatedHow To Restore A Database From Backup With A Splitted Backup File
Apr 1, 2008I 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.
How To Restore Database From Full Backup And Several Diff Backup
Oct 17, 2006I 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
SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?
Jun 30, 2015We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?
Oct 10, 2007I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Command Line Printing To Specific Printers, And Specific Trays
Jan 23, 2007Hi All,
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
Any suggestions is appreciated
Thanks A lot in advance
e,g :
http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render
Establishing Logfile Size And % Used Using Sql
Apr 27, 2001Hi there,
I want to write a sql query (for SQL7 & 2K) that gives the size and space used of the log file for a given database. I can do it for data files using sysfiles/sysindexes, but can see how I can do it for the logfile.
I know I can use DMO or dbcc sqlperf(logspace), but I want to understand how and where the information is kept in system tables.
Logfile Lost In HDD Crash
Dec 18, 2000Hi, i am not familiar with MSSQL so maybe it's not as difficult to you as it is to me - in brief:
SQL version: Microsoft SQL Server 7.00 - 7.00.699 (Intel X86)
Errorlog shows:
Starting up database 'CH2'.
Opening file E:CH2DATA1CH2DATA1.mdf.
Opening file E:CH2DATA2CH2DATA2.ndf.
Opening file E:CH2DATA3CH2DATA3.ndf.
File D:CH2LOG1CH2LOG1.ldf does not exist - unable to activate.
Enterprise Manager
Shows database CH2 (suspect)
Executing script:
sp_add_log_file_recover_suspect_db CH2, CH2LOG1, 'D:CH2LOG1CH2LOG1.ldf', 1000
Shows errormessage "The database 'CH2' does not exist."
How can i get a new logfile?
What if i created a new db 'CH2' and then replaced the data files?
What do i have to tell MSSQL to create a new logfile on startup?
All scenarios shown in Books On Line only seem to solve problems
if you do have a logfile and need more space.
Greetings from Switzerland /Lothar
Moving LogFile From One Disk To Another
Apr 11, 2002I am trying to move a logfile from one disk to another. I've detached the database, moved the logfile to another disk and reattached the database. I used the sp_detach and sp_attach_db to specify the paths of the *.MDF and *.LDF. I've tried it through Enterprise Manager and with boths ways I get an error message saying: "Error 9003: The LSN (201:48:2) passed to log scan in database 'DatabaseName' is invalid". Does anyone know what this means and how I can get around this. I've even tried to attach the database with the logfile in the original path and I still get that error. Need Help Please!
Thanks
Hughe Logfile After A DELETE
Feb 3, 2004Hi
I have a business case where I have a database with a size of 2 GB. In my business case I want to delete 70% of the contents in the database. During the delete the size of the database(including the logfile) grows up to 10 GB. When I look on the database in the SQL Server Enterprice Manager there are only 700 MB used of these 10 GB.
The deletes are performed with ordinary SQL-statements "DELETE FROM X WHERE Y = 1 AND Z = 2..."
Thankfull for hints on how to solve this problem.
Regards,
Peter
Automatic Logfile Truncation
Apr 10, 2008Gurus,
Everyweek I am truncating the logfiles. I want to automate the truncate logfiles on every sunday. Anyone can help me to automate this process..
Thanks
-- Krishna