SQL 2012 :: Ignore One Filegroup From Backup
Sep 3, 2014
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.
View 2 Replies
ADVERTISEMENT
Apr 22, 2014
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?
View 3 Replies
View Related
Jul 21, 2000
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?
View 1 Replies
View Related
Jul 23, 2005
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 !
View 1 Replies
View Related
Aug 18, 2014
I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.
View 4 Replies
View Related
Jun 4, 2008
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.
View 3 Replies
View Related
Oct 26, 2007
hello,
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 appreciate your help in advance!
View 1 Replies
View Related
Jul 20, 2015
I have 2 stored procedure, insert and update.
They both drop and use a static table, this will be changed for production to use a temp table. there is a slight difference in how the create the table and when it comes to executing them if the other has been run first it errors because of the column definition or names.
Now as I said I will change these to use a temp table rather than a static staging table for production but as one of the first parts of my proc uses the IF OBJECT_ID ('dbo.tab', 'U') IS NOT NULL DROP TABLE dbo.Tab;
Then I would expect the table difference to not matter as the table is being dropped...
View 2 Replies
View Related
Oct 16, 2015
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.
View 3 Replies
View Related
Aug 22, 2006
Hi There,
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.
===============
Can anybody please suggest me what I should do?
Thanks.
View 19 Replies
View Related
Dec 4, 2014
I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies
View Related
Dec 4, 2014
i must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies
View Related
Dec 5, 2014
I create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
View 1 Replies
View Related
Dec 5, 2014
I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
View 1 Replies
View Related
Jul 10, 2014
How to add some more ranges to existing partition schema and function?
Already My table partitioned on date ranges,
6 partitions , each partition contains 6 moths data, so total data is 3 years.
i.e. 1 partition data- from jan2012 to Jun2012
2 partition data- from july2012 to dec2012
3 partition data- from jan2013 to Jun2013
4 partition data- from july2013 to dec2013
5 partition data- from jan2014 to Jun2014
6 partition data- from july2014 to dec2014
After Jan2015 data will go to Primary file group(Default)
Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.
File group and ndf file adding is OK, But
how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?
partitioned table size is 200 GB.
View 1 Replies
View Related
Dec 5, 2014
i have a DB in SQL Server 2008. and i wants to store file in DB.
how can i create filestream for filegroup for a DB in SQL Server 2008.
View 1 Replies
View Related
Aug 23, 2013
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
View 9 Replies
View Related
Feb 19, 2015
Using 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 Related
Dec 29, 2014
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies
View Related
Nov 3, 2014
I scheduled automatic backup process but its only showing backup of the only one .sql file in the backup folder. Other created .sql files are not backed up. Why is it so?
View 5 Replies
View Related
Sep 1, 2014
I'm trying to create a maintenance plan to backup one of my databases however it's failing and I'm receiving the below erorr.I am able to back up other databases without issue to the same location and I have sysadmin permissions.
Message
Executed as user: W2K8SQLSYSTEM. ...ion 9.00.5000.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:13:53 AM Progress: 2014-09-02 10:13:54.01 Source: {A7AE97F0-9186-4385}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER
EXECUTE msdb..sp".: 100% complete End Progress Progress: 2014-09-02 10:13:54.35
[Code] .....
View 4 Replies
View Related
Oct 21, 2014
I'm wondering if I can take native SQL log backup in addition to DPM backups. We're using DPM for both databases (daily) and logs (every 15min). So, if I take SQL log backup separately, will it break anything in DPM and we won't be able to perform point it time recovery?
View 3 Replies
View Related
Jan 5, 2015
On a SQL 2012 Enterprise (SP2) instance, I have a database that is 12gb. The size of its backup file is 10gb. With compression I was expecting the backup size to be around 4gb. This database was migrated from SQL 2005 along with other databases that seem to be compressing ok. The instance is set to compression as the default. The database is not encrypted. It contains mainly character data. Compression is also specified in the maintenance plan. Have also trying running backup as a script. The instance is hosted on a virtual server (Windows Server 2012 R2).why this backup is not compressing?
View 9 Replies
View Related
Jan 28, 2015
Looking at the documentation, it would suggest that as well as data files, when a backup file's created it will also be zeroed out unless the service a/c's been given Perform Volume Maintenance.
We take our backups to dedicated backup servers, meaning backup performance should improve significantly if instant file initialization's given to the Service account logins for the source boxes if I'm right.
View 4 Replies
View Related
Feb 23, 2015
We need to setup an AO availability Group for a database for which a full backup exists but the DB is in simple recovery mode now. If i change the recovery mode to full and try to configure AG will this full backup will be used or do i need to create a new full backup.
View 4 Replies
View Related
May 13, 2013
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
View 8 Replies
View Related
May 21, 2014
We're changing Netbackup to DPM but not sure if "backup compression default" option should be ON or OFF or doesn't matter.
View 3 Replies
View Related
Aug 21, 2014
If I try to backup of database in sql 2012 with t-Sql as follows:
Backup database db1
to disk='c:myfullbkup.bak' with init
go
This code works absolutely fine.
But if I try to take backup through SSMS -Task -Backup in object explorer and gave the same path of c: drive or any other folder outside sql default "Backup" folder.It gives error as access denied.This was not the scene in Sql server 2008.
View 8 Replies
View Related
Sep 2, 2014
When performing a backup I'm reciving the error message: "System.Data.SqlClient.SqlError: Cannot open backup device. Operating system error 5(error not found). (Microsoft.SqlServre.Smo)
This is backing up to a network location and I'm able to backup other database on the same server/instance without error so it shouldnt be a permissions issue?why I can backup some databases to this location and not others?
View 1 Replies
View Related
Sep 23, 2014
I am using Ola's solution for db backups by the way and its main command is something like this:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBgrowth -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'MyDatabase1, MyDatabase2', @Directory = N'serverpath', @BackupType = 'FULL', @BackupSoftware = 'SQLBACKUP', @Compress = 'Y', @CompressionLevel = 2, @NumberOfFiles = 5, @Verify = 'N', @CleanupTime = 504, @CheckSum = 'N', @LogToTable = 'Y'" -b
where MyDatabase1 and MyDatabase2 are clients abover 100GB.
Now, I would like to make this dynamic, no user intervention. I already have a table where I'm keeping current and historical data of db sizes.
How can I make this job dynamic, so when a new database passes the 100GB threshold, their names will be part of @Databases parameter? In other words, added automatically, based on my query.
View 7 Replies
View Related
Jan 29, 2015
I have TDE backup one serverA but There is no backup of certificates or keys from Server A. And no one knows the password used to create those backups. How do you restore the database XYZ at that time on Server B?
View 9 Replies
View Related
Jan 29, 2015
Looking at the recovery model on MSDN - was under the belief from it that if you set your database to use the simple recovery model, then when you preform a full back up it would truncate the transaction log file but this doesn't seem to be the case?We aren't looking to do a point in time recovery for this particular databse.
View 7 Replies
View Related
Jul 6, 2015
what is the concept of smart backup in sql ?
View 1 Replies
View Related
