SQL 2012 :: File And Filegroup Backup Components Greyed Out
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?
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 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.
Why is that some of the databases has their "Transaction Log" & "File and Filegroup" options in Backup greyed out? I was trying to create a backup job using the sql enterprise backup wizard. However, the database that is the backend of our erp application has its "Transaction Log" and "File and Filegroup" option greyed out.
I am looking to make the primary key auto increment by 1. I have found by looking around the internet that you need to do this in the Tables -> [Table Name] -> Columns -> [Column Name], Properties window, and I see the "Identity Increment" however all of the properties there are greyed out -- I can't access them. Any ideas on how to make this work?For some background: I'm running SQL Server 2005 with Visual Studio 2005. To create this database, I right-clicked on my project, and went to 'Add SQL Database', I filled in the columns all from within visual studio.
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?
finding the database size from the backup file.I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?
One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?
I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.
Is there a way to use something in sql to compress this already existing .bak file down?
I need to create a new db from a backup file of an allready existing database (vehicledb) on this server.So i created a new database with name "vehicledb2" .But when i try this, the backup says that this file is used...The backup set holds a backup of a database other than the existing 'Vehicles2' database. Restore of database 'Vehicledb' failed.
I have accidentally taken the backup of a database twice into same .bak file. Now the file is twice the size. Will it be fine if I restore this backup? Or will I screw up any data?
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 !
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.
I am running backups on SQL Sever express and I take backups via the batch file executing the TSQL as below. This works perfectly fine, I just need to be able to spool out the backup completion log. What TSQL command can I use to do that?
My batch file looks like this:
@echo off sqlcmd -S SERVER01 -E -i H:master_scriptsTOM_FAM_log.sql
Which will the call the TOM_FAM_log.sql :
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'H:BackupTOM_FAM'
[Code] ....
Usually if we schedule the backups using the maintenance plan, we can chose reporting options that can spool the result of the backup to a text file. What is the T-SQL for the spool report to a text file.
This option produces a file that writes logs with the details I posted below. I would like to do the same or similar using T-SQL in my code.
Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.3000 Report was generated on "SERVER01". Maintenance Plan: Backup logs Duration: 00:00:00 Status: Succeeded.
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.
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.
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.
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?
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??
Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
I am running SQL Server 2005 Enterprise Edition, i want to split my data and indexes on different drives.
In 2000 i had to recreate clustered indexes and non clustered indexes on the correct filegroups to accomplish this.
In 2005 i see there is a ALTER TABLE MOVE TO Filegroup option, thats cool.
Does this effectively do the same as rebuilding the clustered index on the new filegroup? Will this leave the other indexes of the table on the primay filegroup or move them as well ?
If i wanted to also move the non clustered indexes is there a better way to move them that drop and re-create on the new filegroup in 2005, i see the ALTER INDEX statement does not support a move to filegroup option.
In a nutshell what is the best/easiest way to move exisitng table data and indexes to new file groups in Sql Server 2005 Enterprise Edition?
In another thread Jamie Thomson very informatively said "The components in SSIS are deliberately atomic (i.e. they do something very specific) so that its easy to put them together to build something greater than the sum of the parts". Which does make a lot of sense. However, I've been finding that I end up having to create exactly the same "pattern" of combined transform components again and again in order to solve the same problem but in different dataflows (or even within the same dataflow). Cut-and-paste-tastic! In order to obtain real re-use, it seems to me like SSIS is crying out for an easy way to create new components by using composition - i.e. the ability to take commonly-used combinations of existing components and create new "super" components (without having to write Custom Transform Components in C#/VB.Net and handle everything in code).
Does anyone know if this sort of functionality is likely to make it into SSIS in the forseeable future?
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?
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?
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?
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?
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
