This is kind of a elementary question, but how do you export an empty copy of a SQL 2005 database to a backup file? Basically I need a backup without any of the data. Thanks!
I have an MS SQL database that appears to have all the data in it but when I do a check on the tables they are all "0 Mb". How can this be? Is my table data being hidden?
I have this complex logical model with 60 tables which i have converted to phycical model and into sql 7.0 database now the database is completely new for a new internet project without even single row of data now i have to optimise database and determine if the indexes created by the the designing team are good in sql 7.0 will the give me optimised database or not . I just dont know what to do
1) is it possible for me to use indextuning wizard without the data whenever i try to to use indextuning wizard it askes me to use a workload file or make workloas file useing a trace as i do not have the data i just have to capture a trace by saying select * from <table> as the work load file does not return any records the index tuning wizard does not suggest any changes In the indexing tell me HOW DO I DETERMINE WHAT INDEXES TO CREATE FOR A OPTIMISED DATABESE ?
2) IT IS NOT POSSIBLE FOR ME TO PUT DATA IN ANY ONE TABLE AS ALL THE TABLES HAVE fk AND pk RELATION SHIP so what do i do
3) WHAT DOES dba normally do when he recives an empty data base to optimise
4) where do i make views to optimise the data
5) how do I populate data in so many tables
will puting dummy data help ?
Can some one help me with some SP which will populate the table with dummy data by some kind of do while loop with counter incrementing with 1000 rows
please help me and if i am not clear pl tell ask me the questions on my default email or
Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x
hi,my sql database log file has been fulled recently ..... becuasethere are 55 millions records in main 3 tables .... so how i can emptylog file ...i don't want to attach new log file or save any pervious log info.....thanks for helping me ... and my company ..Abdul SalamSr. DBA + ProgrammerXebec Groups of Business.
I have an interesting problem. I am unable to see any database triggers in the Database Triggers folder under programmability in SQL 2005. I have been able to create and alter triggers in the database that function exactly as their supposed to, yet they never show up as visible in the Triggers folder. I would love for one of the luminaries on this forum to share some wisdom with me and let me know what setting is the culprit. It is driving me batty.
I have a database which gets its daily feed from a ftp file. Now is there any way i can figure out the empty tables in the databese which doesnot get any data on the feed.
I need to find all empty files in the database (SQL Server 2008R2 SP2) The files become empty after the archival of a partitioned tables.
I was trying this:
select f.name, * from sysfiles f (nolock) left join sys.filegroups fg (nolock) on f.name = fg.name left join sys.indexes i (nolock) on i.data_space_id = fg.data_space_id left join sys.all_objects o (nolock) ON i.[object_id] = o.[object_id] where i.name is NULL and o.name is NULL
Did not work.
My file names are the same as the filgroup names containing the file, this is why I was joining by name.
Greetings! I just found this group after wasting hours trying to make SSRS work with databases that are already created (that is, the DBA created two empty databases for me, X and XTempDB).
I keep getting "this connection string is invalid can't use this to connect to SQL Server" in the Database Setup tab.
The problem is that to create its own database, SSRS needs dbcreator and securityadmin roles (please correct me if I'm wrong here). And I'm having a helluva problem trying to convince the DBA to give me temporary dbcreator and securityadmin roles!
Is there any document or something that I can use to convince him that SSRS will not do anything like recreating the login (because he said that the login has already existed and has its role set up in many other databases)?
Any help, pointers, suggestions, etc. is very much appreciated.
I am making a form that takes input for 1 to 5 students using VWD. With the help of previous posts I have been able to make the database insert query work properly. In my form I have a radio list that has the user select if they are entering information for 1, 2, 3,4, or 5 children. Depending on how many children are selected on the radio list, I am displaying the proper number of textboxes and validating the data using the handy RequiredFieldValidator. Now I am at the point where I want to perform the instert to the database depending on the selected number of children in the family. What is the general rule for best practices. Please keep in mind that it is my understanding that ALL fileds in a SQL insert statment must have data. Should I ...1) create alternative SQL statements depending on the textboxes displayed OR2) is it more common to insert a standard string or integer, depending on the datatype, into the unused textboxes to populate the unused fields? Sincerely,Mike
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
In my source table, I have columns FirstName and LastName, both of datatype nvarchar. In my dataflow, I created two new derived columns mapped to these two columns. When pushing data to the source, I noticed that the FirstName column had a value of NULL while LastName was just an empty string (for rows that did not have any value).
My question is, why my source table column FirstName is showing a NULL value when the derived column datatype is string and the source is string? It should just be showing an empty string right?
I'm trying to find out if this is possible.. i know that you can do an execute script command and have it export the results to a script file or clipboard.. then in a new database, run that in the query window and it will recreate the table schema.. but is there a way to do the entire table, contents and all via a script in a similar fashion?
SQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True") Dim cmd As New SqlCommand
Dim returnValue As Object cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)" cmd.CommandType = Data.CommandType.Text cmd.Connection = sqlConnection3 cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar) sqlConnection3.Open() cmd.Parameters("@Names").Value = test If test = "" Then Response.Write("An error occured") Exit Sub Else returnValue = cmd.ExecuteScalar() sqlConnection3.Close() Label6.Text = "Number " & returnValue
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)" when i see the datable in "data visualizer" i find no rows. It is confused eventhough it does not throw any error
Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"? The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot.
Hi, I have a SQL Server 6.50 - 6.50.415 (Intel X86) and one database has 8Gb of data and 2Gb of log, the problem is that the log space used is never below 44%, even if I back it up this way:
DUMP TRANSACTION DBName TO DBNAME_log_930 VOLUME = 'SS0003' WITH NOUNLOAD , STATS = 10, INIT , NOSKIP
This should clean the log, right?
I check for open transactions via: dbcc opentran (GMS), but there aren't. The way I check for the log space used is: dbcc sqlperf(logspace) The recover interval is 5.
I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.
use monkey go
create table tab1 ( myColumn varchar(10) null ) go
insert into tab1 (myColumn) values ('') go
exec master..xp_cmdshell 'bcp monkey..tab1 out D:data.csv -c -T' go
drop table tab1 go
I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?
Okay, so i have a procedure where i'm trying to build a left join, and for some reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer i have no problem.
if @spsir='1' select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
if @spsir='1' select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'
select @sql=@sql+' WHERE 1 = 1'
SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '
here is the execution:
exec search_orders_11 @spsir=1
i get the whole command completed sucessfully, but no result. am i missing something here...
I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data. Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!
I will be really thankfull if somebody can help me on this issue.
Is there any way to sort your results in a way that the empty results comes last? When I sort my recordset on a certain column I first get the empty values and the A, B, C etc. I would like to have it A, B, C, ..., Empty ones.
