I have a full backup scheduled at 12.00AM ET and have a batch import on 11.55PM(5 min before full backup) which takes 30min to complete .Will the backup cover the data which is being imported?
View 2 Repliesquestion 1:
i found that database log file can contain more records after performing backup database statement.
for example:
i create a database and limit the log file to 2mb. then i create a table and insert data.
If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.
If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records
why?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end
question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.
After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.
As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end
any suggestions?
thanks in advance.
I have scenario where I have process that loades data into SQL server 2012 database by doing some manipulation on data like sorting , aggregation, etc. Once this process is completed it's not free up the Tempdb space. If I restart the database, then it does.
is there any way (apart from shirking) to release space for Tempdb, like writing some post SQL queries to delete/ truncate the data and logs from temp db?
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 RelatedUsing Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
View 2 Replies View RelatedNeed 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.
We 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 RelatedI 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 RelatedIf my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc
Hi
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
Hello,
I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time).
I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:
Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.
I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?
Or is it possible to schedule full backup to run if tran log backup fails?
Any advice will be much appreciated.
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
What is the method to execute backups from batch (.bat) files on the server running SQL Server. I have tried the sqlmaint command - doesn't seem to execute, looked into the xp_sqlmaint with no luck. I'm sure the problem lies in my lack of DOS batch programming skills. If anyone has an example of a batch file that executes a backup would you mind sharing. thanks
View 6 Replies View RelatedHi
I'm working on a digital Library project. Can anybody suggest a powerful Asp.net full-text search engine Script or guide?
Thanks
hello
i've got a named instance of 2005 up and working but i've been informed that they now need to use full text queries and this feature hasnt been installed. is it possible to install this retrospectivly?
thanks!!!!
Application is throwing errors.Is there anyway to fix this with out restarting the server on a newly migrated system?
View 9 Replies View RelatedHello, everyone:
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
Thanks
ZYT
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Drew
Hi,
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
Thanks for any advice.
I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally
I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
thanks
Mark
Not sure this is the correct forum, but I'll give it a go. I am in the process of deploying a series of databases from a development environment to a production environment. I've done some searching around best practices, but haven't found anything specifically calls out what is best. I am looking to find out the best approach for moving the code/tables/views/SP's, well everything from my dev environment to the production environment. Due to the complexity and large number of objects to be created, would a backup/restore to the production server be more prudent than creating a batch file type thing that creates all the objects through a series of scripts?
Hello !
How can I install on Microsoft SQL Server Express 2005 Full-Text Search Engine ?
I tried to create a full_text_catalog with SQL Server Management Studio Express and I get an error.
Msg 7609, Level 17, State 100, Line 2
Full-Text Search is not installed, or a full-text component cannot be loaded.
Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches........ I need to do it in batches as the input data is huge.....
The logic is I have to insert thru bcp in fact table...
After that batch execution for 50,000 thousand record.... wise.... if any of the batch failes i need to identify and have to rerun from that point onwards...... this is OLAP thing...
Hi, though i've been using sql server 2000 developer edition for awhile, I guess i overlooked the fact that the full-text search tool was never installed and i never needed it until now. Anyway, tried installing it today and get this error:
Installation of Microsoft Full-Text Search Engine Package Failed (-2147220991) 0x80040201
An event was unable to invoke any of the subsribers
I have no idea what this means - can you help me to get this installed correctly? I did just install service pack 4, and i tried reinstalling the components, tried rebuilding the registery of the current instance, and even tried creating a new instance with hopes that the new instance would install the full-text component - which it did not.
Any suggestions? Maybe I have to unistall the entire Software and reinstall it? Would I lose any of my data in doing this? I should note that all other tools seem to function fine.
Thanks ahead!
Dan
Hi,
I am in the middle of writing a console application that acquires data from dynamic odbc connections and inserts the results into a MSSQL database. I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine.
However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side or sql, or both.
I had a quick word with our dba who spoke briefly about some kind of process where by the application fires the request across to the database and carries on leaving the db to queue the request or something to that effect. He ran away before I could get any sort of sense out of him.
Any help greatly appreciated
Thanks
How do you prevent this:"If you import a very large number of rows, dividing the data into batches can offer advantages. After each batch complete, the transaction is logged. If, for any reason, a bulk-import operation terminates before completion, only the current transaction (batch) is rolled back." How can I let the whole batch rollback, instead only the current transaction (batch) rolling back?
View 1 Replies View RelatedI'm using MS JDBC driver to connect to SQLServer 2005 and trying to perform batch insert. Here is the code i'm using:
Code Snippet
try {
// Assume we have a valid con
con.setAutoCommit(false);
// this is the simplified SQL for the purposes of this example
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO temp_batchOpt (");
sql.append("temp)");
sql.append(" VALUES (?)");
PreparedStatement insertStatement =
con.prepareStatement(sql.toString(),
SQLServerStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < 10; i++) {
insertStatement.setInt(1,3);
insertStatement.addBatch();
}
int[] r = insertStatement.executeBatch();
// the correct number of insertions are made
if(r != null)
for(int x: r)
System.out.println("inserted "+x);
SQLServerResultSet keyRS = (SQLServerResultSet)insertStatement.getGeneratedKeys();
while (keyRS.next()) {
int id = keyRS.getInt(1);
System.out.println("**filling id ="+id);
}
con.commit();
} catch (BatchUpdateException bue) {
bue.printStackTrace();
}
catch(SQLException sqle){
sqle.printStackTrace();
}
finally {
// close the connection...
}
I'm getting the following exception:
Code Snippetinserted 1
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
inserted -2
inserted 1
com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source)
at com.x.U.m.Transformer.prefetchData(Transformer.java:285)
at com.x.U.m.Transformer.init(Transformer.java:51)
at com.x.U.m.XMLParse.main(XMLParse.java:215)
I can get the getGeneratedKeys() to work fine with single update (not usign batch update). Is batch + getGenerated key not supported?
TIA
I have a database that has had a full text index at some point in it's history - but no longer does. The database is currently at SQL 2012 compatibility, but it was created on SQL2000. It still appears to have a row in the internal table sys.sysftinds - but some of the column values are NULL, and NULLs are not allowed. The database fails DBCC checks because of this.
Msg 8970, Level 16, State 1, Line 2
Row error: Object ID 25, index ID 1, partition ID xxxx,Â
alloc unit ID xxxx (type In-row data),Â
page ID (1:xxxx), row ID 0. Column 'sensitivity' was created NOT NULL, but is NULL in the row.
DBCC results for 'sys.sysftinds'.
There are 1 rows in 1 pages for object "sys.sysftinds".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'sys.sysftinds' (object ID 25).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (xxxx.sys.sysftinds).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Running DBCC with repair_allow_data_loss has not worked.
In SQL Server, whether the full backup of a database includes the backup of the log?
View 1 Replies View RelatedHi:
I have 30 databases on sql server 2005 that I need to do a full backup every morning at 7:00 and tran log backup every 30 minutes until 7:00 PM. If I create a maintenance plan for a backup using the wizard I have the option of starting a full backup at 7 am and then an option of doing tran log backups every hour using a different schedule. I plan on selecting the option to create a different folder for every database. I just need to confirm that in this way the way to restore the data would be
1. to restore a full backup
2. apply all the tran logs depending on the time they want to recover back to.
I just think this is the easiest approach to have 30 databases on the same backup scheme instead of creating a separate backup device for each database and doing a full backup on that device and appending all tran logs to that device which means just 1 bak file versus the above strategy with a number of tran log files. Please advise.
Thanks
In SQL Server 2005, the SQL Server full text engine (i.e., Microsoft Full Text Filter Daemon Launcher windows service) was installed as a Generic Service cluster resource in a Windows clustered environment.
However, in SQL Server 2012, the full text engine is not appearing as a "resource" within "Failover Cluster Manager" after installation. Do we need to manually configure the full text engine as a Failover Cluster Manager "resource" for SQL 2012? Or is this not necessary? If it needs to be configured, how do you go about doing it (i.e., what resource dependencies would you setup, etc.)
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
View 1 Replies View RelatedI€™m facing a SQL batch insert issue on Windows 64 bit (IA-64) platform. I€™m using €˜Column-Wise Binding€™ of parameters. The example program hosted on Microsoft site (http://msdn2.microsoft.com/en-us/library/ms709287.aspx) returns error of SQL_NEED_DATA for batch insert on Windows 64 bit platform. The same code works fine on 32 bit platform. I€™m using SQL Server 2000 as the database and tried using both native SQL server ODBC driver and Merant 5.2 ODBC drivers. Any help on this will be greatly appreciated.
View 5 Replies View Related