Setting Recover Model With TSQL
Jan 27, 2007
I have a TSQL script that restores a production database to my local
development machine. Every time I do a restore, I have to go into
Enterprise Manager and set the recover model to simple to prevent
Windows XP Backup from having shadow copy problems (that is a rant in
and of itself). I would like to just add a statement at the bottom of
the script to do this, but have yet to find the proper commands
documented. It seems that the instructions that come with the server
only mention enterprise manager.
Thanks in advance for any help.
View 2 Replies
ADVERTISEMENT
Jan 4, 2008
Hi
I just deploy a report model and want use report builder to create ad-hoc using this report model.
I want some entitis and attributes are not visiable for some user, so I config the model item security for this model.
But no matter which user I use to login the report server, I always can access all the entities.
Even I delete all the groups and users in "Permissions" property of the model, I still can access this model through report builder.
All the user I used to test are local user of server with report service, my server is SQL Server 2005+SP2.
How can I fix it?
View 3 Replies
View Related
Jan 30, 2008
Hi,
I would like to demonstrate mining temporary models in an ASP.NET application.
Creating, trainning, predicating actions are all witten at C# codes as follows:
Code Snippet
using (AdomdCommand cmd = new AdomdCommand())
{
// Build temporary mining model
cmd.Connection = asConn;
cmd.CommandText = "CREATE SESSION MINING MODEL " + modelName +
" (" +
"HCVS_MemberId Text KEY," +
"HCVS_MeasureDate DATE KEY TIME, " +
"SysPressure LONG CONTINUOUS PREDICT, " +
"DiaPressure LONG CONTINUOUS PREDICT," +
"Pluse LONG CONTINUOUS PREDICT" +
") " +
"USING Microsoft_Time_Series(Missing_Value_Substitution='Mean' ) "; // Periodicity_Hint = '{12}'
cmd.ExecuteNonQuery();
// Train Data
cmd.CommandText = "INSERT INTO " + modelName + " (HCVS_MemberId, HCVS_MeasureDate, SysPressure, DiaPressure, Pluse) " +
"OPENQUERY([Healthcare], " +
" 'SELECT HCVS_MemberId, HCVS_MeasureDate, SysPressure,DiaPressure,Pluse" +
" FROM v_VitalSignForecast WHERE HCVS_MemberId=''" + id + "'' AND HCVS_MeasureDate>=''" + from.ToShortDateString() + "'' AND HCVS_MeasureDate<=''" + to.ToShortDateString() +"'' ')";
cmd.ExecuteNonQuery();
// Predict upon the Train Data. In addition, the standard deviation of each predicated value is retrieved
cmd.CommandText = "SELECT FLATTENED " +
"( SELECT *, " +
" SysPressure + PredictStdev(SysPressure) AS [SysPressure_PlusStdev], " +
" SysPressure - PredictStdev(SysPressure) AS [SysPressure_MinusStdev] " +
"FROM PredictTimeSeries(SysPressure, " + fDays + ") AS SysTable " +
") " +
"FROM " + modelName ;
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
DataSet sysDS = new DataSet();
adapter.Fill(sysDS);
The problem is that I do not know how to configure my Analysis Service Server to let ASP.NET account can utilize it. And ASP.NET account in trun impersonates the account who is authorized to use Healthcare DB in the Openquery. Please give a help. Thanks a lot.
Ricky.
View 4 Replies
View Related
May 21, 2013
My company is new to MDS. I am trying to set an attritube in an entity to read only so the users can't change the value in that field. When I did that, the whole model disappeared. I thought I had deleted it by accident so I created a test model and tried to do the same. The test model disappeared. This time, before saving the new settings I took a snapshot. After saving I took another snapshot. You can see that the whole model is gone (zz_RN_Permissions_Test). I tried every other coworker with admin rights and nobody shows it on the Models list. The behavior on the Excel add-in is correct. I can't change any values on that column. But I need to keep the models available.
See before and after snapshots below.
View 8 Replies
View Related
Jul 31, 2007
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
View 1 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Oct 19, 2015
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.
Productnr Productdesc Language
1 product EN
1 produkt DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
View 2 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Aug 10, 2005
hi !!i have a question about the connected and disconnected model to access the Sql server DB.......i know that there is better to choose one rather than the other in some situantions and there is no better model in all cases...... os i hope you can help me to decide what shall i choose...i will use the DB to connect to Web services and read data from the DB and wrtie some data back.......i do not know that to use ..... i hope you advise me and tell me about the rules that will allow me to choose what model to choose .... i appreciate your help!!Thanks !!!
View 2 Replies
View Related
Nov 15, 2006
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
I have OWC 11 installed. What am I missing here?
Thanks
View 7 Replies
View Related
Feb 2, 2007
Is there a way to recover the database without defining a device to restore from?
Restore Database TestDB With Recovery
MTmace
View 4 Replies
View Related
Feb 1, 2008
Hi,
By mistake i've damaged my .mdf file and i have no backup on this database . However i have the transaction log intact and an older version of the database .
Is it possible to recover the database using the transaction log? How can it be done?
I do not have the transaction log backup, just the .ldf file.
Thanks to anyone who can help me.
View 6 Replies
View Related
Feb 27, 2000
How do I begin? I had previously installed the SQL Server 7.0 with NT Authentication and everything was fine until I Rename domain Name and Computer name. I decided to uninstall SQL Server, and then reinstall it.
From this action unable to see my database in enterprise manager. I Rename old domain and computer name again. and reinstall with Upgrade Version. but still same effect. Can anyone help? What do I need to do to get my database to work again? Any input on this will be appreciated. Thanks in advance.
View 1 Replies
View Related
Aug 25, 2004
I had MS SQL installed on my comp.I had to reformat my hard drive due to some problems.There was a db in the sql server which i needed.So i saved the .mdf & .sdf files.Then i installed sql server in the formatted hard drive & pasted the .mdf & .ldf files in the Data forlder in MSSQL folder.But the problem is that even after doing this the enterprise manager doesnt show that database.Can anyone help me with this?Is there any way to recover the database?
Thanks a lot
P.S.- I also have whole of the earlier MSSQL folder present in the earlier program files backed up.Would that help in anyway.
View 3 Replies
View Related
Aug 5, 2004
Hello, everyone:
A guy think that the DTS packages can be recoved by restore MSDB database. Is that true? Does anyone have experience on that? Thanks.
ZYT
View 2 Replies
View Related
Nov 23, 2004
Hi
I've had a powerfailure, that resulted in I had to scratch my DB server, I've managed to get all the .mdf and .ldf files from the server. On the new server I get a "Error 823: I/O error (torn page)" when I'm trying to attach the .mdf file to the New server.
Both the new and the old server is a win2003 with SQL 2000 SP3
Is there anybody who knows how I can recover the DB?
/Rune
View 1 Replies
View Related
Oct 6, 2005
Hello,
Is it possible to restore a SQL 2000 database with data from a transaction log?
I am not sure what happened to my database, all the data in the tables is gone. I generated a db script to create a duplicate database. I saved the .sql file on my local machine, separate from the db server, renamed the database name in the .sql file to a new database. Tested script in query analyzer for syntax, all was fine. Running it at this point would have created a new database with a different name, right?
Now my original database is empty. Can someone please provide me with information on getting my data back. Is there a way of using the transaction log to recover the data.
Thanks
View 2 Replies
View Related
Jan 30, 2004
I have changed the roles of primary and secondary servers due to server failure.
Can anyone advise how to revert back to previous main server? Are these any links available?
View 2 Replies
View Related
May 3, 2004
Hi
I need help to recover database from its MDF & ldf files as my server crahsed today .
BTW i installed afresh copy of MS SQL 7.0
I need to be able to restore the database. Pls reply if
you can help. It would be greatly appreciated.
:( :confused:
View 1 Replies
View Related
May 9, 2011
is there any way to recover a database? I accidently drop my database .I don't have any backup.
View 5 Replies
View Related
Aug 5, 2005
Paul,
Having read your useful insight into the sorts of issues around the corruptions that folk have posted in this forum I'm interested in preparing for the day when one of our servers gets corrupted :-(
Should I expect to be able to:
Restore FULL backup from Before the corruption first happened
Restore all Tranaction log backups since
and would get me back to ground zero?
I'm thinking:
if I do a DBCC CHECKDB once a week, say, immediately after a FULL backup - then if that gives me some error messages I've got last weeks Full backup and a weeks worth of Trans Log Backups to restore - which is OK for a rare disaster.
I apprecaite that the thing that caused the corruption might be a memory or disk fault, which might also have broken the creation of the backup files too - in which case I'm fairly stuffed!
Does a RESTORE do a checksum of somesort that would detect a corruption caused by, say, a memory fault or a disk controller that wrote the block faultily [twiddled some bits, but did not report error]?
In which case continuous restores of backup files to A.N.Other server would discover the moment at which a backup was "known bad"
Am I close?
Thanks
Kristen
View 9 Replies
View Related
Feb 2, 2006
I am using sql server7.0
I want to know how to recover pubs and northwind data bases in case they are deleted.
Gajanan Kulkarni
View 2 Replies
View Related
Jun 22, 2006
raju writes "while attaching the mdf file we are getting the following error
error 823
i/o error (bad page id) detected during read at offset
0x0000001404000 in file d:filename"
View 4 Replies
View Related
Mar 9, 2007
OS: Windows 2003 Standard Edition
MS SQL: Standard Server 2005
I am looking to implement a disaster recovery plan for an MS SQL 2005 server, this is what I have done so far but would like the benefit of your experience to provide feedback and help improve the strategy.
MS SQL Server:
I have two maintenance plans, one to backup the system databases to individual backup files (.bak) and one to backup all the user databases to a backup device (mssql_backup) The backup files & device are in a directory (backup) and is backed up using NFS. I keep a copy of the backups on a USB HD which is stored offsite.
Disaster:
Office burnt down
Recovery:
New server, install OS/MSSQL, patch up to same version as burnt server.
Copy .bak files to new server, copy mssql_backup file to same location as previously held i.e. X:ackupmssql_backup
Start MS SQL in single user mode, restore master database using .bak.
Start MS SQL in normal mode, restore other system databases using .bak
Restore user databases using mssql_backup device
Testing:
Using the above steps I was able to restore up to the point of "user databases" Getting to this point I realised that there are a number of databases to restore, in my novice ways this seems to be a lot of right-clicking and restoring.
1) is there a command that would in effect be able to restore all databases using the backup device?
2) do I have a working version of the databases to the point of the last backup? Or am I missing something?
3) in my current setup in mind (SQL backup to NFS directory and picked up my backup software) is there a 'better' way I could be doing this which would make the restoration procedure more efficient
View 4 Replies
View Related
Mar 28, 2007
how can i recover sqlserver 6.5 datbase from tape.
pls help me?
View 1 Replies
View Related
Jul 23, 2007
Hi all,
Is it possible to recover or find the password of database or resetting is the only option? I have a database that has a two users in the Users tab. How to find out the password for these?
Thanks,
View 4 Replies
View Related
Feb 1, 2008
Hi,
By mistake i've damaged my .mdf file and i have no backup on this database . However i have the transaction log intact and an older version of the database .
Is it possible to recover the database using the transaction log? How can it be done?
Thanks to anyone who can help me.
View 3 Replies
View Related
May 13, 2008
Hi,
Some times my laptop will hang for some reason and i will have open few sql 2005 query editors working with scripts. I am loosing them if i restart my system. Is there any way to recover the previous instance of my sql 2005 query editors after restarting my system. Please let me know if yo have any idea to handle.
Thanks
Dilip
View 4 Replies
View Related
Jun 3, 2004
Hi All,
Accidentally I have dropped a table and that is the main table for my application.
I dont have a backup of it. Is there any way to recover it.Please help me guys.
Thanks,
Kumar.
View 1 Replies
View Related
Apr 16, 2002
Hello,
I have some procedure in SQL Server 7.3 they are
working fine. Now my SQL Server is not working that is
it not opening Query Analizer or any other tool
in SQL Server ok.
I have no back of my procedures which are written
in SQL Server any where like in notepad or in some
places.
Now i have a plan to load Sql server again
but i want my old procedures before loading
Sql Server again.
How to do it?
How to restore my procedures?
Where actually SQL Server stores these procedures?
I searched in my system with my procedure name but it
shows nothing with those names.
In my case there is no chance of recovering my old SQL Server
i have load again SQL Server.
So kindly help on this issue asap.
thanks
View 4 Replies
View Related
