.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Full Backup

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
Hi All, I want to do a Full Backup Daily but is there a way of doing a complete Full Backup by writing over the pervious day full backup. I have done Differential backups by using INIT keyword but I wanted to be sure that I can use it for Full backup as well. Thank you    

View Complete Post

More Related Resource Links

SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

Full database backup takes so long

Today several full database backups took so long. Normally it took 2 hours to backup. But today the 5 backups didn't finish by the end of the work day. The backups went well until yesterday. I don't know what happened overnight. Is there a performance tool embedded in SQL Server? How can I find out why the backups take so long? What could be the possible reasons? Thank you,  

Full Farm Backup Fails on Windows Sharepoint Services Search Database

I am just now starting to us central administration to backup the entire sharepoint farm.  All content backups fine except for the Search Database.  In looking through the backup log, it appears that the search database may not exist.  How can I determine if the search database actually does exist and if it does not can it be recreated?

How to determine a partial vs full backup set?

I've written application that uses SMO to backup / restore database. I largely mimic the interface found in SQL Server Management Studio 05. In the restore display I display all available backup sets and I query msdb to get the info for each backup set via the following query: "select backup_set_id,name,description,recovery_model,type,server_name,database_name,position,  backup_finish_date,backup_size,user_name,expiration_date  from msdb.dbo.backupset Where msdb.dbo.backupset.database_name = '" + dbName.Text + "'"; How do I determine if the backup was full or incremental, so I can set the Partial property??

Moving full-text catalog from one server to another using backup / restore


Dear Sirs,
I'm having a problem with Full-Text Catalog in SQL SERVER 2005.
The problem is: I did a backup of a database that has a full-text catalog from one server and restored on another server.
The restore worked perfectly, but the catalog is down because it is pointing to the physical path from the source server.
To recover this full-text catalog I tried the commands below:

use master

sp_detach_db 'SiteEng2004'

(FILENAME = 'D:\SQL SERVER\DADOS\SiteEng2004\SiteEng2004_Data.MDF'),
(FILENAME = 'E:\SQL SERVER\LOGS\SiteEng2004\SiteEng2004_Log.LDF'),

Unfortunately it did not work and the following error message occurs :

Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file "D:\SQL SERVER\DADOS\SiteEng2004". Operating system error 5: "5(error not found)".

Could anyone help me on what I should do to restore this full-text catalog on the destination server ?
Thanks in advance.

Maintenance Plan - Full Backup


Hey all

I am running a maintenance plan (Sql Server 2008 Enterprise) every night at mid-night. All the plan entails is doing a full backup of one database (about a 3GB database). We are getting timeouts on our site at exactly that point when the database is being backed up and they last up to 20min. I was under the impression that doing backups this way does not take the db down? Or am I wrong about this?

Has anyone else experienced this and if so what steps did you take to rectify it? Sorry if I have not provided other information, just let me know and I will give it to you.


log files doesn't clear after full backup


I have SQL 5 and for some odd reason log files doesn't clear after Full back up.  I have to manually shrink the log files.

can someone explain what could be wrong

Create automatic full backup


Our maintenance plans do a full backup on Sunday, Diff backups nightly and log backups every 4 hours.  However, if the users bring a new database online during the week, it will fail every backup until the full backup is taken.

Is there any way to detect the creation of a new database and force a "benchmark" full backup to be taken?  I should say any simple way; I'm quite sure I'll wind up writing some sort of proc to run under the Agent and look for new arrivals, but I wanted to see if anyone else has a simpler solution.

Thanks in advance!

SQL 2005 Backup question - Differentials seem to big right after a full backup has occurred.

i have a sql2005 database in Full recovery model.
(Microsoft SQL Server 2005 - 9.00.3042.00 (X64)   Feb 10 2007 00:59:02   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) )

MDF is 12GB & LDF is currently 6.4GB.
I do weekly fulls on Friday at 10pm.
I also do daily Diffs at 8am, 12pm, 4pm.
We also do hourly Transaction log backups during biz hours since this is an ERP system.

The hourly transactional logs seem to vary predictably given how much work is being done on the database.

But the diffs just seem to large in proportion to the size of the db, EVEN AFTER A FULL BACKUP WAS JUST DONE.

For instance, i just ran a full back up which was 18.7GB. 
Then i IMMEDIATELY ran a diff and it's 6.4GB.

Shouldn't a diff immediately following a full be almost nothing?
Am i missing anything here?

my backup syntax that i just ran was as simple as:
BACKUP DATABASE coll18_production
TO DISK = 'X:\SQL Backups\Weekly\coll18_production\coll18_production_FULL__20100201_1425.bak'

BACKUP DATABASE coll18_production TO DISK = 'X:\SQL Backups\Daily\coll18_production\coll18_production_DIFF__20100201_1430.bak'


full backup script


i have to take full backup mannually on shared location not through maintenace plan.Please send me the script...... help me..


Restore an SQL 2008 full backup to SQL Serveer 2005



Dear All,


I have been working on a database on my laptop that has SQL Server 2008 Ktma installed.

The database was restored from a full backup that I had taken from the production SQL 2005 server.


Now that I have finished my job I want to restore it back to the production Server (SQL 2005). I took the backup with "No compression" on a disk file through the SQL 2008 Management studio.


However when trying to restore the SQL 2008 backup onto the SQL server 2005 I get an error:


The media family on device xxxx is incorreclty formed ....


Can someone help me on this. Is it possible that sql 2008 backups are not compatible to the sql 2005 structure?

Is there anyway to have my database restored back on the sql server 2005?


Thank you in advance,


S. Menegos


PS: It is not an issue of malformed file since I did the process several times (taking the backup, coping to the sql server 2005 machine).



SharePoint 2010 backup is failing - Error - "Unable to read full interval from database - Operation



I am having a problem backing up a site collection, when trying to backup the collection I get the following error "Unable to read full interval from database" and "operation is not valid due to the current state of the object".

The backup does run and manages to backup 182MB of the site collection and then the backup fails, it fails at the same point what ever backup method I use e.g. STSADM, Powershell or Central Admin.

I'm getting the same error whether I use CA "granular backup" or "Farm backup", and using either STSAMD or Powershell. I can backup CA or the mysite collection but not the Home Portal site collection. I even tried installing the commuvaults 2010 agents and running a commuvault backup but this is also failing. i have included the log file entry below.

Does anyone have any suggestions on what i can do to either fix the problem or an alternative way to backup the collection or farm, I am currently taking a full SQL backup but am not sure how reliable this is as I haven't tried to restore the sites. 

P.S. I am not using the "UseSQLSnapshot" parameter.

11/30/2010 09:34:25.23  OWSTIMER.EXE (0x17E8)                 

TLog backup size in Full vs. Bulk logged recovery model.

When executing some minimally logged operations one can notice higher in TLog growth in Full recovery vs that of Bulk. But when it comes to talking a TLog backup one can notice its bigger (because the modified extents are also backed up along with TLog) Questions: 1. Are the pages backedup only those which were modified by minimally logged operation? 2. TLog backups in bulk recovery will be bigger than those from Full recovery but it depends if or not any minimally logged operations were done.

Will full or differential backup break LSN chain if compatibility level = 80 in SQL Server 2005/2008

My understanding is that full or differential backup will not break LSN chain starting in SQL Server 2005.  However, I'm not sure whether this applies to databases with compatibility level 80 in SQL Server 2005/2008 or not.

Schedule full and differential backup in sharepoint 2010



Hello guys ,


I need to Schedule full and differential backup in sharepoint 2010 using powershell how can I do that ?


Appreciated your answers in advanced

How to Specify Backup File Name in Full Backup Maintentnace Plan ?


Dear Sir,

I have created maintenance plan to take full backup of selected database. In property I have selected to create sub directory for all database.

Now Maintenance plans create backup file name DATABASENAME_backup_YYYYMMDDHHMM.bak file (e.g. MICROBRAIN_Backup_201105171555.bak.

I want that Maintenance Plan should Create File Name as DATABASENAME.BAK (e.g. MICROBRAIN.BAK)

Please Help me How to do this ? Because Creating Seperate Backup file on every backup will exhaust space in removable Hard Disk.

Sanjay Shah

SQL Server Backup and Restore (video)

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend