SQL Server 2008 :: Different Filegroup For Each Schema In One Database

Mar 4, 2015

Can we create the Database with two schema and having the separate file group for each schema.

View 5 Replies


ADVERTISEMENT

SQL Server 2008 :: Moving Into A Specific Filegroup

Jun 3, 2015

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

View 0 Replies View Related

SQL Server 2008 :: Merge Moves Data Into Wrong Filegroup?

Feb 18, 2015

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?

View 1 Replies View Related

Partitioning Schema And Filegroup Management

Jan 17, 2008

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

View 2 Replies View Related

SQL Server 2008 :: Snapshot Replication To Different Schema Name

Apr 25, 2015

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)

View 1 Replies View Related

SQL Server 2008 :: Schema Changes With Transactional Replication

Jun 15, 2015

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 Related

SQL Server 2008 :: Grant User Full Permission Within Schema Scope

Jul 13, 2015

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

View 2 Replies View Related

Problem With Database Filegroup Restructuring In SQL Server?

Mar 7, 2006

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 Related

SQL Server Admin 2014 :: Restore Filegroup From One Database To Another

Mar 14, 2014

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?

View 3 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

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

View 4 Replies View Related

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

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 Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

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

How To Extract A Schema From A Sql Server Database

Apr 4, 2008

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

View 4 Replies View Related

How Do I Change The Schema Of SQL Server 2005 Database Diagrams

May 23, 2008

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

View 2 Replies View Related

SQL 2012 :: Server Referential Integrity Across Database - Schema?

Sep 19, 2014

In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?

View 1 Replies View Related

SQL Server 2008 :: How To Update The Database Physical File Location In Master Database

Mar 8, 2015

I 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

View 1 Replies View Related

SQL Server 2008 :: Script To List All Database Level Permissions In A Database?

Aug 4, 2015

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?

View 7 Replies View Related

Restore Database From File And Filegroup?

Nov 20, 2007

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

View 11 Replies View Related

SQL Server 2008 :: Restoring A Database / Server Using A Remote Server As The Service Call?

Jan 4, 2013

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.

View 4 Replies View Related

How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?

Jun 20, 2008

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.

View 2 Replies View Related

SQL Server 2008 :: GUI View From A Different Database?

Jan 22, 2015

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

View 4 Replies View Related

SQL Server 2008 :: Database Can't Be Accessed

Apr 9, 2015

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 ?

View 9 Replies View Related

What Are New In SQL Server 2008 Database Engine?

Jun 29, 2007

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,



View 1 Replies View Related

SQL Server 2008 :: Capture Database / Server Name In A Derived Column To Identify Source Of Data?

Feb 1, 2012

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)

View 5 Replies View Related

SQL Server 2008 :: Identify If Remote Login Resolved Database Server Via IP / Servername Or FQDN

Feb 16, 2015

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?

View 0 Replies View Related

SQL Server 2008 :: Restore Database That Has CDC Enabled To Another Server That Does Not Support CDC?

Sep 15, 2015

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

View 0 Replies View Related

SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'

Mar 21, 2007


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!?!?)

View 3 Replies View Related

SQL Server 2008 :: How To Check Progress Of Database

Jul 18, 2012

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.

View 9 Replies View Related

SQL Server 2008 :: Master Database Missing

May 14, 2013

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.

View 7 Replies View Related

SQL Server 2008 :: Run All Stored Procedures In A Database

Feb 12, 2015

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?

View 4 Replies View Related

SQL Server 2008 :: CPU Usage Relative To Each Database

Feb 26, 2015

I am looking for the script to find the CPU usage for each database like this

Test1DB76.00%
Test2DB12.00%
Test3DB1.21%
Test4DB20.08%

View 4 Replies View Related

SQL Server 2008 :: Iterate Through Database Tables?

Mar 18, 2015

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?

View 4 Replies View Related

SQL Server 2008 :: Enable RCSI On A Database

Apr 8, 2015

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved