Our current application which is deployed to numerous client sites usually requires the database to be returned to us to upgrade for the next release. The current process is:
- Perform a full backup at the client site
- Send the backup to us for upgrade
- Perform a full restore process to our local db server
- Modify the database
- Perform a full backup
- Send the backup to the client
- Perform a full restore over the original database.
This has served our purposes so far, however, we are about to introduce functionality that will enable the client to upload files for storing in the database.
To manage this we have a single DBFiles table which due to the potential size of it’s contents we determined that it would be best to store it in it’s own FileGroup. The idea being that then we could backup just the Primary FileGroup for returning to our offices as the DBFiles table would remain untouched.
The proposed process would now be:
- Perform a partial backup of the primary filegroup at the client site
- Perform a partial backup of the DBFiles filegroup at the client site. This only ever stays at the client site and is never restored at our office.
- Send the primary filegroup backup to our office for upgrade
- Perform a partial restore or the primary filegroup to our local db server. This will leave the DBFiles filegroup offline which is OK as we do not need to change it.
- Modify the database
- Perform a partial backup of the primary filegroup
- Send the partial backup to the client
- Perform a partial restore of the primary filegroup over the original database. This will leave the DBFiles filegroup offline.
- Perform a partial restore of the DBFiles filegroup over the original database.
This last step results in the recovery failing due to a reason like “The roll forward start point is now at log sequence number (LSN) 66787000000001800001. Additional roll forward past LSN 66787000000008900001 is required to complete the restore sequence�.
I have tried a number of variations including backing up both the client and local log files for recovery and using copy_only but I have yet to be able to successfully complete the process. Is there a means we can synchronize these 2 backup files without worrying about the logs for this part of the process?
Our requirements are basically to be able to perform a partial backup and exclude the DBFiles table as it is likely to be very large so we can send it back to our offices for upgrade and then return it to the client for restoring back to the original database while maintaining the data in the clients DBFiles filegroup and table.
Any guidance on what we need to change or a better process would be appreciated.
Hi - I am using partial backup & restore on a Data Warehouse database currently in development.
When I recently tested the restore procedure I got the following error when trying to an online restore of one of the ReadOnly filegroups:
Msg 3125, Level 16, State 1, Line 1
The database is using the simple recovery model. The data in the backup it is not consistent with the current state of the database. Restoring more data is required before recovery is possible. Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup.
I believe I received this message as the Filegroup I was attempting to restore had been set ReadWrite since it was backed up.
So - I am looking for a query to test that all my filegroup backups are consistent with the live database.
I think I can achieve this by checking the read_write_lsn & read_only_lsn values for the filegroup to restore are the same as the values in sys.master_files for the live database.
I am reading the lsn values for the backup from msdb.dbo.backupfile
Can anyone confirm this is the correct approach? or is there a better way to do this??
SQL 2000I have server1 with two filegroups.I want to backup FileGroup Primary on Server1 and Primary has aphysical file called MyData.mdf.I have server2 with the same database and two filegroups BUT thePhysical names are different. The Primary FileGroup is in physical fileOurData.mdfHow can I get server1->MyData.mdf to Server2->OurData.mdfThanks !
I am new to DBA activities. I have a database name Sample_DB in SQL Server 2014. Â
This database has below files
LogicalName FileType FileGroup Sample_DB_Rows  ROWS Data PRIMARY Sample_DB_C1 ROWS Data Country1 Sample_DB_C2 ROWS Data Country2 Sample_DB_Docs FILESTREAM Data FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem: I need to take backup of each file group separately. I need to restore the backed up file group separately. Is this possible, how to do it.
Hi , I am restoring a VLDB from a Standby server which has just .mdf and .ldf to my production server which has .mdf and a file group with a single .ndf file, i want to restore the databse on to the Secondary file(is this possible) i checked the BOl but could not figure out of what command is to be given
Using SQL Server 2000 SP3 I'm developing a data warehouse where datawill be archived off to a filegroup, this filegroup backed up and thetables in this filegroup truncated to free up space on the server.So using Enterprise Manager I've copied data to the filegroup tables,backed up the file group, truncated the tables, but when I haverestored the file group the database is stuck in a state of'(loading...)' and I can't get it to resume. I've tried running thecommand RESTORE LOG databaseName WITH RECOVERY but get an error messagethat the database has been restored to different time (sorry I can'tremember exact syntax but I'm not at my work computer)Is this familiar to anyone? Ignoring the filegroups is there a way Ican get the database .MDF back up and running?Thanks
Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.
One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.
When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.
Unfortunately it is not simple as we wish. I tried those steps:
Perform PRIMARY filegroup backup to file. Copy this file to Artur's computer by DVD disk. Perform our database's Trnsaction Log on Artur's computer. Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager) Restore backup done in step 3. --- Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.
What is wrong? Is it possible to move data to Artur's computer this way, without full backup and serie of log backups? Can I use other alternative solution to move data?
I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?
I have backup my database with "file and filegroup"selected. I have dropped my database by accident. is it possible to restore my data back from "file and filegroup".bak??
System.Data.SqlClient.SqlError: The backup of the file or filegroup "CUSTBilling" 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)
I did a file group backup and restore of a database and then when I tried to perform a full backup I am getting the above error. Any thoughts. I indeed happen to see this error http://support.microsoft.com/default.aspx/kb/921106 but not sure if both errors are the same.
I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment. Suppose we arrived at step 5, I am executing the following commands:
Code Snippet --make full backup of TestDatabase backup database TestDatabase to DISK='G: emp estdatabase.bak' with init --make full backup of TestDatabaseDemo backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init
backup log TestDatabase to DISK='g: emp estdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak' with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY --restore primary filegroup from Testdatabase-backup restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak' with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY --restore log and try to get db onlin restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery
I get the following error: The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement I get error: The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
Is it possible to back up one filegroup independently from the second when tables in one group have foreign key relationships with tables in the second group?
My intention is to make a copy of a database with some data excluded. One way to do this:
- Make a copy-only backup of the source database - Restore the backup as a new database - Delete undesired data from the new database
In my case, the source database has two file groups, primary and secondary. The secondary has just one table with huge amount of data and this file group appears as one physical file. Is there any way to make a partial backup, which does not include this file group or table, and it would still be possible to restore the backup to a new database? My point is to avoid copying huge amount of data if it won't be needed.
my full backup job failed and it's giving me the below error message on the backup job for MSCRM database. If I start the job manually if I remove the full-text index on the table, the job ran succesfully.
The backup of the file or filegroup "sysft_ftcat_documentindex" 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. [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
I am looking at the file / filegroup level backup and recovery options within SQL Server and I'm struggling with the following concept.
Books online assures me that it is possible to perform a file restore whilst the database is in the simple recovery model.
So I have set up a database with two separate file groups, a read/write primary and a read only "secondary". Each filegroup has 2 underlying data files.
I have then created a "live" customers tables within the primary filegroup and assigned my existing "archive" customers tables within the secondary filegroup.
If I try to perform a file or filegroup level backup within management studio, those options are greyed out. I can only perform a database backup.
If I switch back to the full recovery model, the options are no longer greyed out.
So my question is this, is file level backup and recovery actually supported in the simple user model, do you have to perform this task outside of management studio, or (as is likely) am I missing something crucial?
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them. 2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
I am trying to do a full back up of a user database which has full text indexing on it. I use the maintenance plan wizard to set up a full backup. For some reason, I get this error.
=================
Executing the query "BACKUP DATABASE [XXXX] TO [XXXX] WITH NOFORMAT, NOINIT, NAME = N'XXXX_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The backup of the file or filegroup "sysft_XXXX_FT" 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. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
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.
I 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.
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
There is some strange behaviour i've recently noticed while watching synchronization progress in Replication Monitor on SQL 2005 Server Standard with merge replication configured. The merge process seems to repeat several times.
This is the initial synchronizaion (reinitalization at the subsciber). Client is using Microsoft.SQLServer.Replication objects from .net framework assemblies.
The synchronization starts normally (status is "Running"). The last message of selected session box shows (among other messages): "Beginning evaluating partial replication filters" then "Finished evaluating partial replication filters" and finally "Merge completed after processing xxx changes... etc." after a few seconds. Status changes to "Completed" and then... the merge process starts again!! "Beginning evaluating partial replication filters" etc. And this repeats about 15-20 times.
And so whole process takes about 15 minutes instead about 45 seconds to complete initial synchronization. The number of changes is "Merge completed after processing ..." never change since the first such message.
Is this some bug in web synchronization or some invalid configuration setting? Why does merge process repeat itself so many times??
I had to replace my hard drive which was going bad. I reinstalled Sql 2005 and now need to restore the backup that I have. I selected Restore Database and chose From Device, and selected the backup location on J: drive and selected the backup file which has no extenstion. I checked the checkbox next to the backup set to restore. Click OK and then I get this error message: Restore failed for Server System.Data.SqlClient.SqlError: Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo) I don't know why it is looking for a MDF file as this was never in the Backup folder. Can someone please help me on this? I thought I was doing the right thing making a backup all along and now I can't restore it!
Is it possisable to restore from tape when the backup was taken from a different server. ServerA has the tape drive which performs the backups to tape, but ServerB which is under a different domain wants to restore ServerA database on ServerB, both databases on the two servers are the same is it possiable to restore a database on ServerB using ServerA file?
hello all !! I suffered a crash on one of my disks on a server runing MS-sql 7.0,and found out my backupexec dosnt really restore a runing version of my database. which solution would you recomend ,taking in acount i may have a copy of the database on another computer,which files i can restore? thanks yochai lam
Hi I am operating my databases under "trunc log on checkpoint" mode as I do not need point of failure recoverability.I was performing a recovery test using a backup which resulted in it being unsuccessful .Error Message received was "Recovery has failed because a nonlogged operation could not be redone.Use the RESTORE statement to restore all data in filegroup PRIMARY to a pt beyond the nonlogged changes"
Isnt sql*server supposed to prevent a non-logged operation while a backup is in progress?Also how do i prevent such a situation from recurring. If i switch to single user mode before a backup how do i prevent exisiting users from accessing ... without bouncing my sql*server?
I did a restore of database the user the original database is admin where it says the user is dbo.
After i did the restore i was able to add admin as user and dbowner but when i tried to change the user it said the user already exists.
How do i create the same..enviroment as or the original system with users..i saw a problem i i drop the user and them recreate it then it cannot find the object or the object which are owned by it are lost..could any one help me with this ...so as how to and what do i need to keep track of while restoring the database and configure the server back with same user names and permissions.
I have two databases on two different SQL Servers,our application is updating these two databases simultaniously.So every time these two databases will be in synchronized state. Now i want to schedule backup for these two Databases.How can i take backup of these two databases at single point of time. so that if i restore these two backups on corresponding servers,There should not be any conflicts.
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'NetworkDevice'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
I don't really understand what's goin.
basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.
Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.
Is there any other process that's much faster to implement.
