SQL Server 2008 :: Different Filegroup For Each Schema In One Database
Mar 4, 2015Can we create the Database with two schema and having the separate file group for each schema.
View 5 RepliesCan we create the Database with two schema and having the separate file group for each schema.
View 5 RepliesSQL 2008 R2
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
Hi everyone,
I have successfully managed to create filegroup Function and partitioning
scheme.
My question is how then Next month incoming increaze 2 functions.
Next will be 200812...how I made an add to the logic?
I found an example that explain me strategy...but with a fixed number of
Filegroup, making the switch IN / OUT. My case necessarily the filegroup
file are always on add
Thanks,
Alen Italy
PARTITION CREATE FUNCTION [] (DateCreazioneLogIIS datetime) AS
RANGE LEFT FOR VALUES (
DATEADD (ms, -3,'20070901 ')
DATEADD (ms, -3,'20071001 ')
DATEADD (ms, -3,'20071101 ')
)
CREATE PARTITION SCHEME [RaggruppamentiDateLog]
AS PARTITION [DateCreazioneLogIIS] TO (
[200709]
[200710]
[200711]
[PRIMARY]
)
I need to setup snapshot replication for 1 large table.
Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1
In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)
How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?
View 2 Replies View RelatedThe requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
Hi,I have the following SQL procedure I am running to clean up a filegroupand move all data to a single .MDF file:use <db_name>print 'Move <db_name> db contents to MDF file'DBCC SHRINKFILE ('<db_name>_1_Data', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_Log', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_2_Data', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_log2', EMPTYFILE)goTrouble is that I get the following error:Server: Msg 1105, Level 17, State 2, Line 3Could not allocate space for object '<company_name>. Inv. Line' indatabase '<db_name>' because the 'Data Filegroup 1' filegroup is full.There are five files:- a .MDF file (which is already set as the primary filegroup, 43MB insize);- <db_name>_1_data.ndf (this file is 11GB in size);- <db_name>_2_data.ndf (this file is 4GB in size, empty & cannot beshrunk);- 2 .LDF filesI have been able to remove the "<db_name>_2_data.ndf" filesuccessfully, but moving the objects from filegroup 1 to the primaryfilegroup has yielded the same error message every time. Any ideas onwhat I can do to resolve this?
View 1 Replies View RelatedI 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?
View 3 Replies View RelatedLocally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedHello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedHi,
I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??
Any idea will be appreciated..
Thanks
Karen
Hi all. I'm mapping FKs in a DataWarehouse and I'm wondering if there is a way to change the schema of a database diagram from dbo.
Thanks,
Charlie
In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies View RelatedI had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
Hi guys
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??
Thanks
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
We have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB ...
I was having issue with one of my databases in SQL EXPRESS. It is offline this morning …it said “Database 'MyDB' cannot be opened due to inaccessible files or insufficient memory or disk space. When I checked the error log ..it only said “FCB::Open failed: Could not open file D:DatabasesMyDB_Data.mdf for file number 1. OS error: 32(failed to retrieve text for this error. Reason: 15100).
I did alter database offline and and online ..it works for me( I can access the database again but I need to find the cause of this issue )I checked the max memory setting is still by default .. not limited for SQL ..it could be the cause ?
Hi, all here,
Just heard about the coming release of SQL Server 2008. Anyone here got some ideas on what are significant new features in SQL Server 2008 Database Engine compared to SQL Server 2005 Database Engine? Would be very interesting to know that.
Thanks.
With best regards,
Yours sincerely,
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).
Using these DMVs I can get a lot of relevant information:
sys.dm_exec_sessions
Program Name (eg. Microsoft SQL Server Management Studio),
Client Interface Name (eg. .Net SqlClient Data Provider)
sys.dm_exec_connections
Net Transport (eg. TCP),
Client Net Address and TCP Port
but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?
UAT environment : SQL Server 2008 R2 SP3 Enterprise Edition
SANDBOX environment : SQL Server 2008 R2 SP3 Standard Edition
I have a database backup (.bak) that was taken from UAT environment that has CDC enabled on some tables. I want to restore that database into my SANDBOX environment which does not support CDC (because of standard edition). The restore process fails due to this incompatibility. Is there any way to restore without CDC (I dont CDC enabled on my SANDBOX; just my data from the backup) ?
The error message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
Our database crashed this morning and went into recovery mode.how I can track the progress of the recovery to determine how long it might take?The error log shows that it started up all the databases, then shows the recovery messages fr the msdb database, then shows that sql server is ready for client connections. I don't see any messages about my database recovery or the number of transactions to roll forward or background. If i run the sys.sp_readerrorlog and search for my database name, the only line returned is the starting up database message.
I do expect the database to take a while to recovery as it is about 8TB, there is plenty of free disk space about 3TB.The database started recovery while a transaction log backup was running so that backup failed,the last transaction log backup was taken 2 hours before recovery started. The last full backup completed about 5 days ago. The transaction log backup occurs every 2 hours and is typically around 16GB.
Wierd issue of a missing master database - wierd because I would have thought this was a newbie topic but I've found nothing for it. I googled and had a 'decent' look through this forum and only found a bunch of topics on 'how to restore master database'.
I wouldn't have thought I need to restore the master database because my SSMS works fine and I can query the master database. I can also see it in the drop down list of available databases in the Query Designer toolbar. The problem is just that I can't see it in the list of databases. I can see all the other databases I've created, and I can see the master database in the DATA folder. But not in the SSMS.
I'm looking for an easy way to run all stored procedures in a database that match a specified criteria.
Under normal curcumstances, I'd create a "master" procedure that would call each one in turn using the "EXECUTE" syntax.
As there will be around 140 procedures (there are a few more but they are used for different purposes), I'd like to try and execute them dynamically.
Is there any way of finding out which procedures match the pattern of "usp_merge_*" for the name and executing it?
I am looking for the script to find the CPU usage for each database like this
Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%
MS SQL 2008.I want to execute a delete query on certain tables in my database to delete some rows in the tables.The tables selected has a certain name pattern (the name ends with "Temp").
So I can do this to get a list of the table names
SELECT name
FROM sys.Tables where
name like '%Temp'
Now I want to check each table to see if it has a column with the name "DateStamp" and then execute a delete query as follows:
delete form TableName where
DateStamp is < '2010-01-01'
In other words I need to iterate through the tables names, How to do this?
The following is from an accidental dba perspective.To enable RCSI on a database, referencing these two parameters:
is_read_committed_snapshot_on (value 0 or 1)
and
snapshot_isolation_state (value of 0,1,2 or 3) - based on specific needs. At a minimum, it should be a 1..Both parameters are required.