.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

Differential Backup

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
I have SQL Servers both 2000 and 2005 and I wanted to perform backups daily, so I want the backups to have Datetime stamp therefore I can distinguish between the different files. I have 2 questions 1.I'm going to use the following script to run a full backup on the sql 2005 server but I wanted to know if there is a way to perform the same thing in the sql server 2000 server. declare @CheckDate [nvarchar](50), @Title [nvarchar](100) set @CheckDate = replace(replace(convert(nvarchar(50),getdate(),120),':','), ' ', '_') set @Title = N'E:\DataWarehouse_Microstrategy_'+@CheckDate+'+'.bak' BACKUP DATABASE [DataWarehouse_Microstrategy] TO DISK = @Title WITH NOFORMAT, INIT, NAME = N'DataWarehouse_Microstrategy-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10 ; 2. I need to perform a differential backup for both on sql 2000 and sql 2005 and I wanted to know a way of doing the differential backup but delete files that are old e.g any files that are older then 4 weeks. Thank you in advance

View Complete Post

More Related Resource Links

MOSS Search Differential Backup

With the SharePoint 2007's built-in backup tool, will it do differential backup on databases and file system? File system as is Search index files?

Differential backup failed because the previous backup metadata for Database of SQL Server


I'm developing a VSS requestor that backs up Microsoft SQL Server, among other things. Full backups and restores work just fine. A differential backup always fails though, giving an error in the event log with Event ID 8197:

"Differential backup failed because the previous backup metadata for Database <dbname> of SQL Server instance <instance> is corrupted"

and the writer is in the state "VSS_WS_FAILED_AT_POST_SNAPSHOT" with error hr=0x800423f4

When my full backup runs, the differential_base_xxx fields in sys.databases are updated and correctly reflect the time that the full backup was taken.

The documentation I have read on the SqlServerWriter says that the metadata is recorded in the VSS event BackupComplete, which is definitely being called. During backup I see the writer state move from waiting for backup complete to stable.

I'm using SQL Server 2008 R2 on Windows 2008 R2. There are no other versions of SQL Server installed on that machine.

Can anyone suggest why this is happening?



SQL Server VSS Writer -Differential Backup - Does it even work?

I have been trying to develop a tool to do backup/restore of MS SQL Server using SQL Server VSS Writer. I can not get the differential backup working! I am just curious as to whether anyone ever made differential backup using VSS. Here is what I have been doing,
1. Create a full backup and store files and the Backup component document associated with it.
2. Update the tables in the database.
3. Create a new backup by setting 'SetBackupState' as VSS_BT_DIFFERENTIAL
4. Now when adding the existing components to the Backup Component Document, I find the component's time stamp from the document in step 1, and then invoke it with 'SetPreviousBackupStamp'
5. Now after the snapshot is taken, I go through each of the component and invoke 'GetPartialFileCount' API. This always return the value 0. I am assuming if there is a change in an existing component, it should give a value greater than 0.

Am I missing something here? Do we need to start SQL Server VSS Writer using certain parameters or something? I tried different configurations including (Windows Server 2008/ SQL Server 2008)

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.

SharePoint 2010 Unable to restore differential backup


Sharepoint 2010 farm and SQL server are two separate 2008 R2 servers.  Ran full backup in the administration site and ran a differential of part of the site.  Cannot restore the differential.  It is looking for the full.

Here is the error message:

Object WSS_Content_PubSrv failed in event OnRestore. For more information, see the spbackup.log or sprestore.log file located in the backup directory.
SPException: The restore operation cannot locate the backup file F:\MSSQL\Backup\WSS_Content\SQL_WSS_Content_PubSrv_Full_201104111900.safe. Verify that the file exists and then run the operation again.

I tried mapped F to the F drive on the SQL server but it still fails.  I have full rights and the service account has read rights to that directory on the SQL server.   Share is full rights 


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

Restoring a differential backup on a different server



I have a simple backup schedule for one of our database : full backup on fridays and differential the rest of the week.  I now want to restore this backup on a different server.  I can successfully restore the full backup but I can't restore the differential, even if i checked the no-recovery option.  The message I have is "Cannot apply the backup on device...".

Here is the header of my backup set.

BackupName         BackupDescription BackupType ExpirationDate      Compressed Position DeviceType UserName     ServerName DatabaseName  DatabaseVersion DatabaseCreationDate   BackupSize       FirstLsn          LastLsn           CheckpointLsn        DifferentialBaseLsn     BackupStartDate      BackupFinishDate     SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags    BindingId               RecoveryForkId             Collation
--------------------------- ------------------ ---------- ------------------------ ---------- -------- ---------- ----------------- ----------- --------------- --------------- ------------------------ ---------------------- --------------------------- --------------------------- --------------------------- --------------------------- ------------------------- ------------------------

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.

How to backup SMS from windows mobile Pocket PC to computer

How to backup SMS from windows mobile Pocket PC to computer

Many Windows Mobile Pocket PC Phone users could not backup their SMS to txt file or view on computer. I found this software offers a SMS Messages backup solution to Backup all SMS messages for Windows Mobile Phones onto computer and keep them safe. Here is the user's guide:

Toolbox: Easy File Backup, Exploring Files And Folders Inside Visual Studio, Multiple Monitor Softwa


If the responsibility for creating, managing, and executing routine backups is yours, these tools will make it easier. Also see how you can browse folders and files from inside Visual Studio.

Scott Mitchell

MSDN Magazine May 2009

MOSS 2007 Backup Strategies

I saw some backup strategy best practices papers stating you could backup the databases and site content separately, but was told by Microsoft that if you do that, and have to do a bare metal restore you will not be able to recover because the site content and database will be out of sync. Does anyone have a proven DR process for MOSS 2007 that they can share? We have Commvault in house right now, but are using it only to backup the databases and file system separately. Should we be using the built in sharepoint backup stdadm(sp?) tools?
John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

SQL2005 backup Issue

After moving the SPS 2003 portal from one disk to another, I am having a problem in backing up the portal from the spsbackup tool as well as through the SQL Server 2005 database backup option . Following is the error am getting:-   ============================================================================================================================= Backup failed for Server 'sql server name'.  (Microsoft.SqlServer.Smo)   ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476   ------------------------------ Program Location:      at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)    at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)   ===================================   System.Data.SqlClient.SqlError: The backup of the file or filegroup "sysft_ix_ContentDatabaseName" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)   ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=

SMO Backup and Restore Help

Hello Friends, I am new to SMO, Till now I have only worked with Selct,Insert/update Queries.. Now I want to Implement some backup and restore feature for my application. I looked at some documents of SMO and found it is possible using SMO. First of all I will explain my situation. My application wants to store data belongs to different companies(which is maintained with in application) in to single database server, Sql server 2008. I am planning to use Partition Scheme function and create filegroup for each company. Advantage here is, taking backup of each company will be easy. I am generating primary key for each table without taking into consideration of companies. In other words Particular table's ID 1,2 belongs to Company A, 3 Belongs to Company B and 5 belongs to Company A... But these data will be split in to Different filegroups using Company ID.(horizontal partitioning) Ok... Now the Problem.. My backup for a particular company was taken successfully. Now I want Restore function. When I am trying to restore this backup to somewhere else. Target database may or may not contain Companies installed. When Restoring Will there be any Primary key Violation? I know one solution is to generate primary key by taking into consideration Company Id. But this will cause a complete rewriting of code, as it contains lot of edit/delete queries.  I want to know, whether the

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!

What happened to the backup and restore classes in namespace Microsoft.SqlServer.Management.Smo vers

I do not see these two classes in version 10 of this namespace and I am not finding any documentation explaining what happened to them.

Can I backup to a UNC destination not part of domain using Proxies?

Hi, I am trying to backup my databases to a UNC destination that is not part of domain (stand alone machine) Can I use proxy accounts, so the database backup engine uses the UNC path using a stand alone machine’s account? If not, how can I handle this situation? Thank you, Max

Any issues removing backup mount point cluster resources from SQL Server dependencies?

Win 2003, SQL 2005/SQL2008 64 bit enterprise failover cluster with 3 nodes and 6 instances. The mount points configured as cluster resources for the data/backup/logs. The entire cluster mount points are SAN replicated to a DR Site. But backup drives/LUNs are excluded from the replication. All the disk resources including backup LUNs are set to dependencies on the SQL Server server. The SQL Server is not coming on-line in the DR Site due to unavailability of the backup LUNs (failed resources). As per the documentation all the disk resources must be set to dependencies (http://support.microsoft.com/kb/835185). Thought it did not specifically mentions only Data/Log mount points and/or the backup LUNs as well. "The SQL Server 2005 resource depends on the SQL network name resource and the physical disk resources that hold its data. When mount points are being used together with the physical disks, each mount point must appear as a cluster resource. Additionally, each mount point must also be added as a SQL Server dependency. If only the root physical disks dependency is added and the mount points are not added, database corruption will occur on failover. Database corruption may also occur when SQL Server is restarted without failing over. "     What if I remove the backup LUNs from dependencies list. This will allow me to recover the SQL Server
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