|
There is a series of articles by Joe Celko 'Stairway to Database Design' Step 1[^], I think there are 6/7 in the series. You may have to sign up to read them (can't remember) but it is a useful site for database stuff anyway.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Normalize![^]
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
hi..
i'm trying to retrieve the data from database using ssis package..
database name is employee which contains 48 tables and it is around 20GB of memory.. it is a production
database..
now what i want is to retrieve the data for each four hours..
i.e i want to execute the package for every 4 hrs..
now what i did is i have taken a database transfer task and from the
source i.e production database i want to transfer to my database
which works fine..
but what is the problem here is for every 4 hrs whole DB is replaced in my system
i've given overwrite TRUE..
Here user enter the data into database for every 4 hour is minimum 10 MB of data..
instead for replacing the whole database for every 4 hrs is there any
solution that what user enter data into database only that much amount of data should be append into my system.. is it possible
please help me..
|
|
|
|
|
Have you thought of doing a maintenance plan?
It will allow you to do full backups and differential backups on a given schedule
Maintenance plans[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
Hello,
I would like to write a select statement at the database server level, like trying to find all the existing indexes for all the databases in that server - something like this? But for all the databases in that given server and not individual database servers. This below piece works only for each databases.
--
select t.name, i.name
from sys.tables t, sys.indexes i
where i.object_id = t.object_id
--
thanks!
|
|
|
|
|
AFAIK It's not implicitly possible with T-SQL as Particular index information is not stored globally, It's in corresponding DB.
But there's some suggestion that you may apply to get.
a) Get name of all the database in Server
select name from master.dbo.sysdatabases
b) Take database one by one from the above query result and make a custom query and execute it using EXEC statement.
Like
Use
select t.name, i.name
from sys.tables t, sys.indexes i
where i.object_id = t.object_id
c) Store each result in one temperory table with the required column.
d) Finally SELECT * FROM #Temptable
I Hope you are getting me.
|
|
|
|
|
Thanks for the reply, Hiren.
I have tried to use the cursor to loop through all the databases, passing in one database at a time to th inner cursor at the master level database. But it is not getting the correct results.
I believe you cannot use the reserved word USE in stored procs?
|
|
|
|
|
vanikanc wrote: I believe you cannot use the reserved word USE in stored procs?
I know that we can't use it plainly.
IMO like following way.
Declare @sql varchar(max)
set @sql = 'Use AdventureWorksDW
select * from dbo.ProspectiveBuyer';
EXEC(@sql)
|
|
|
|
|
vanikanc wrote: I believe you cannot use the reserved word USE in stored procs?
A table name can be specified with the following form.
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name
That allows one to do a select from another database.
Thus for example the 'master.dbo.sysdatabases' is using the form that starts with the database name.
|
|
|
|
|
Hi vanikanc, I got the solution regarding BUT I am going to publish a TIP/TRICK on that today so please wait and I will provide you a link to TIP/TRICK Soon.
|
|
|
|
|
Hi,
try something like this and build on a sql string, I hope this helps.
DECLARE @sql VARCHAR(MAX), @db VARCHAR(255)
DECLARE c CURSOR FOR
SELECT [name] from master.dbo.sysdatabases
OPEN c
FETCH NEXT FROM c INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'Use ' + @db + SPACE(2)
SET @sql = @sql + ' SELECT t.name As TableName, i.name As IndexName FROM sys.tables t,
sys.indexes i
WHERE i.object_id = t.object_id
ORDER BY t.Name'
FETCH NEXT FROM c INTO @db END
CLOSE c
DEALLOCATE c
--PRINT @SQL
EXEC(@sql)
TJR
We Came! We Saw! We Listened! We Eliminated Ambiguity and developed a system the user wanted, not what we thought they wanted.
Enough Said!
|
|
|
|
|
I don't know a way to do what you require using SQL/T-SQL but it can definitely be done using SMO (Example of a C# utility using SMO to script indexes[^]) or a combination of SMO and PowerShell.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Try Like this in Your SP
If You Know all the Database Names.
select t.name, i.name
from DatabaseName1.sys.tables t, DatabaseName1.sys.indexes i
where i.object_id = t.object_id
union
select t.name, i.name
from DatabaseName2.sys.tables t, DatabaseName2.sys.indexes i
where i.object_id = t.object_id
Regards
Senthil Raja.J
modified on Thursday, December 30, 2010 4:34 AM
|
|
|
|
|
Please wrap the code in PRE tag.
|
|
|
|
|
For few of us, its seems to be a simple question, I already have a solution for this...
I don;t need solution, I need explanation.
Problem:
I'm trying to backup the database by selecting the database source, backup type = full, name the backup set, and leave the rest as default. The backup seems to be successful.
The problem comes when I need to restore the backup database.
I 've tried many times and a few different kinds of selection, all fails. I've selected the source for restore from device, that is the .bak file from backup folder and remains the rest of selection as default. Here is the error message I get:
System.Data.SqlClient.SqlError: The backup set holds a backup of the database other than the existing 'testing' database. (Microsoft.SqlServer.Express.Smo)
I know the solution to this:
In Restore window-> Options tab, check "Force restore over existing database"
I need why should I check force restore.
Can some one please explain?
G-U-R-U
|
|
|
|
|
|
As per the above mentioned link ....
Force restore over existing database means if a database already exists with the same name that you are restoring, then overwrite it. If one doesn't exist, just create it.
But for mine case, I don;t have any existing database and still I'm getting the error.
G-U-R-U
|
|
|
|
|
In a simple file copy pasting scenario what happens ?
If you're pasting file at the location where file with the same name exists there, They will ask you whether you want to overrite it ?
Same happens with DB, If you're restoring DB that sets is already existed in DB then you need to override it rather then creating duplicate DBs.
I hope you get your answer.
|
|
|
|
|
I agree with you.
But in mine case, I don;t have any DB with same name.
I just got a BAK file from some other server and tried to restore the same on another server.
G-U-R-U
|
|
|
|
|
All records of some of tables in my database were deleted. Database is for a website on internet. Can anyone help me with some hints and a solution . This happened just today. Can I restore my lost records without a backup file ? Is there a way to find out how this happened ? (hacked ? exceeding db size limit ? ... )
|
|
|
|
|
|
|
I don't know a way to read the log file. And the only tool i know which can
possibly help you is Apex SQL Log.
G-U-R-U
|
|
|
|
|
devboycpp wrote: Can I restore my lost records without a backup file ?
No, however depending on how the data was deleted, it still may exist on the disk. You might want to see if there are any tools available to recover sql server data on a hard drive.
devboycpp wrote: Is there a way to find out how this happened ?
There may be. I would start with any logs you have at your disposal. Check the Web Server logs, the SQL logs, Audit logs, the SQL Server Service logs.
If you can not find anything there, look at how you insert, update and delete data from the affected tables for bugs.
Check the web code for possible sql injection.
|
|
|
|
|
All depends on the recovery model.
if you have a "full" recovery model then use a third party product to do if for you
how do i recover data from sql server s log files[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|