.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

Restoring a differential backup on a different server

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


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
--------------------------- ------------------ ---------- ------------------------ ---------- -------- ---------- ----------------- ----------- --------------- --------------- ------------------------ ---------------------- --------------------------- --------------------------- --------------------------- --------------------------- ------------------------- ------------------------

View Complete Post

More Related Resource Links

SQL Server Compatibility problem while backup and restoring ????


Hi ...

SQL Server Compatibility problem.

1. I have taken backup from sql server 2008 R2 , it is not restoring in sql server 2005 ?

Wats the problem and how to solve this issue ??

2. I have taken sql server 2008 R2 database and log file (mdf and ldf). it is also not attaching in SQL server 2005.

Plz help me to solve this .....

Restoring Differential to New SQL Server instance



I want to restore a full/differential backup from SQL Server to an instance other than the one where the files were created.  The Full Restores o.k., but I cannot seem to get the differential to be added on.  I understand both must be restored at the  same time.




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?



Errors restoring DB from backup created on another server


I'm trying to restore a DB from a backup created on another server. The restore finishes but then I get the following error and everything rolls back. Has anyone seen this error before? Is there a resolution? Here's the response received.

Executing the query ...

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">





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.

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.

Failure in restoring an database from tape - SQL Server 2005

Hello everyone, we are trying to restore one of our datebase from tape using the T-Sql commands. Using the GUI results in an timeout. The command we are using to start the restore is: RESTORE DATABASE RECOV FROM Tape = '\\.\tape0' WITH  FILE=2, RECOVERY, NOUNLOAD, STATS=5, MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\data\RECOV.mdf', MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\log\RECOV_log.ldf', REPLACE the restore proces start without any problem, only ending with this: Processed 2598808 pages for database 'xxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Data' on file 2. Processed 3 pages for database 'xxxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Log' on file 2. Msg 3229, Level 16, State 2, Line 1 Request for device '\\.\tape1' timed out. Msg 3213, Level 16, State 1, Line 1 Unable to unload one or more tapes. See the error log for details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. In the eventlog, the following is recorded: BackupTapeFile::UnloadTape: PrepareTape(UNLOCK) failure on backup device '\\.\tape0'. Operating system error 21(The device is not ready.). For as far we can judge, the tapedrive is functioning without problems. Using the same tape in an other tapedrive on an different server results in the same error. Hardware used: HP Proliant Server (DL580) with additional SCSI interface card HP StorageWorks LTO 4 tapedriv

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

SQL Server backup failing on one file

Hi I have a server which is not backing up a db each day. When I look at the event log it displays the following...   SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000008e00000 2010-08-16 16:07:38.19 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:07:38.19 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15100) 2010-08-16 16:07:38.20 Backup Error: 3041, Severity: 16, State: 1. 2010-08-16 16:07:38.20 Backup BACKUP failed to complete the command BACKUP DATABASE ########. Check the backup application log for detailed messages. 2010-08-16 16:12:14.32 spid5s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000009900000 2010-08-16 16:13:07.02 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:13:07.02 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15105) 2010-

Sql Server 2005 backup misses files

I have a SQL Server 2005 database that contains 3 files.  Sometimes when I run rhe backup, it appears to only backup 2 of the three files.  The only way I can get it to return to a full backup is to restore the database from a backup tape.

Compatibility problem: an "sql server 2008 R2" db backup cannot be restored to an "sql server 2008"

To verify if the "2008 R2" version can be restored back to a "2008" dbserver I make an experiment:  construct a database on R2 version ( version : 10.50.xxx )  and backup it; then I try to restore the db backup to a "sql server 2008" db-server ( version : 10.0.xxx ) but get failed. Error message is as shown: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.   Originally I want to upgrade a "2008" server to "2008 r2" , but this problem withdraws me: if I do so , I am afraid all the existing clientuser's dbs cannot be backed up to themselves if they don't have "2008 r2" locally:   if a database is backed up under "2008 r2" environment whereas client user only has "2008" db server, does it mean he cannot restore it anymore?   So I wonder if there is an approach that helps me to create a "backward-compatible" database; when creating database on wizard there is an option to choose "compatibility level" but I don't think it meets my requirement..    Anyone can help me on this ??    

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Differential Backup

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

Backup SQL Server 2008 R2 Restore on SQL 2008

I have a SQL Server 2008 R2 instance that I need to backup and restore onto a SQL 2008 instance. When I attempt to restore the back up (.bak) database from the R2 SQL server onto the SQL 2008 server I get the following message: The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo) How can i backup the SQL 2008 R2 database to be compatible with SQL 2008?Ian Ceicys

Backup server

I have a 2008R2 server that does AD, sharepoint and file shares.  What I need to have to is if the main server goes offline a second server pick up where it left off. What is the a good solution for me?   Thanks

Restoring a database on server X but while in a database on server Y

I am trying to execute the following but it always fails. I am on server 'X'. I back up a database on server X, then restore a database on server Y using the newly created backup. The database DB1 exists on both servers of X and Y.How can I execute the intended SQL statements below without errors? I am executing these statements from server X.BACKUP DATABASE [X].[DB1] TO  DISK = N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1.bak' WITH NOFORMAT, INIT,  NAME = N'BFI_SCPJD-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GORESTORE DATABASE [Y].[DB1] FROM  DISK = N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10GO
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