I have two SQL Databases on separate servers, live and test. I have been asked to copy the data from the live system and put it into test. They are SQL Management Studio 2008 running on MS Server 2008R2.
Could a simple backup of the database, then copy that file to the test system and restore the database from that point work or it there more to it?
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies View RelatedAre global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?
I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.
So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?
Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.In my Web.Config file, I have this:<connectionStrings> <add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True" providerName="System.Data.SqlClient" /></connectionStrings>I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.I tried this in my Global.asax, but it told me the value was read_only: ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True" Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site? Thanks.
Hi, I wasn't sure if this was the correct place for this question since it involves both T-SQL and ODBC but i thought I would start here. A little system information to start. I'm using SQL 2005 on a Windows 2003 server. An application is connection to my database through ODBC and adds records to a table one at a time (no batches).
I have an Instead of Trigger created on a table that tests for the existence of a valid foriegn key. If the key doesn't exist in the related table I want to insert the record into a "bad records" table.
My trigger works perfectly when I add a bogus record to the table directly in the SQL Management Console table view or thorugh an insert query. However, when I try a live test with an application connected via ODBC that is adding records to the table the trigger fails to catch the errors and will not update the "bad records" table. If a record is legitmate (i.e. has a valid foreign key) then the final bit of trigger code fires without a problem.
Does anyone know of a problem with Instead Of Triggers working on multiple tables when connection through ODBC? It just seems weird that the trigger works perfectly in one situation but then doesn't work in another.
Thanks and advance for any help you can give.
-Will
new to SQL Server 2000. We have an obsolete database that we need tosave off for x number of years. DB2 has utilities (DB2Look/Export)that allows for the export of the data along with a schema and scriptthat enables the future recreation of the structure of the databasesand tables to include RI etc. You can save off the architecture andrelationships of the tables as well as the data.Does SQL Server have anything similar?Thanks in advance.Gerry
View 3 Replies View RelatedLive Webcast tomorrow
Essential Team System for Database Developers
1 PM Central time 12/28
https://www.clicktoattend.com/invitation.aspx?code=112602
I am trying to understand an environment and provide a solution to Banking system so that they can enter user data (transactions) online and at the same time we can provide users online reporting as well. Using same sql server or server/hardware on other machine.
There are so many branches/customers/ATM machines accessing online data as well as updating their balances. I want to understand how can we provide online reporting. Through replication, transaction log backup, log shipping or what other solution is available. I need to understand this and provide a solution that is already implemented running/successfully. Need some proposals and their pros and cons. cost and maintenance are the constraints with the real time reporting on live system/database.
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
In Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
Hi all:-
I administer about 100 databases. I back them up to a file on the server hard drive everynight. Once a month I would like to test restore the backups . Due to the huge number of databases now its almost impossible to manually test restore them one by one so I came up with an automated script to do it. I have a database called testrestore and I restore each backup file to it get the count on certain crucial tables, throw it in a different table for later comparison and replace the database with the next backup file. I need to run this script on production, do you think its okay to test restore 100 databases one after the other using the 'replace' parameter ? Can it cause any memeory issues ? Is there any other way to test restore such a huge number of databases ? Suggestions are welcome.
Thanks.
I am trying to copy a production db (26.5 gigs) with a 3 gig log from production to a test server. The Prod db name is EDD_Cat which resides on one logical drive for the data (.mdf) and another logical drive for the log (.ldf). The test server does not have the same physical raid allocation. The only way that I can get that much space is to spread the data across 3 logical drives. I have preallocated a database called EDD_CatT with the same total physical db size. I have not been successful in restoring from a sql backup device (copied from production) to the new test db. Here are my tsql statements and error:
Restore Database EDD_Catt
from Iloc01bkp
with File=2,
Move 'EDD_Cat_dat' to 'D:Mssql7DataEDD_Cat.mdf',
Replace,
Move 'EDD_Cat_dat' to 'E:Mssql7DataEDD_Cat2.ndf',
Replace,
Move 'EDD_Cat_dat' to 'F:Mssql7DataEDD_Cat3.ndf',
Replace,
Move 'EDD_Cat_log' to 'G:Mssql7DataEDD_Log1.ldf',
Replace
start db restore
---------------------------
2001-01-02 12:23:31.610
(1 row(s) affected)
Server: Msg 3257, Level 16, State 1, Line 0
There is insufficient free space on disk volume 'E:' to create the database. The database requires 20447232000 additional free bytes, while only 1732972544 bytes are available.
Server: Msg 3013, Level 16, State 1, Line 0
Backup or restore operation terminating abnormally.
I also tried using EM but basically got the same type of error.
I could do this with SQL 6.5 as long as the db size was the same or larger.
Any advice/suggestions will be greatly appreciated. BOL and the manuals that I have seem to only give examples that have one file for the data and another for the log but I could not find one that gave an example of what I am trying to do.
Thanks much for your time
Calvin Matsumoto - State of California
Hello
Can anybody give me an idea or a script which can be used to Restore a production Database to Test Database on another server. As I need to do this 3 days a week, I would like to make this automated.
Thanks
Hi All:
Please advise me about restore a master database from production to test server.
The reason to do this because I need to test and evaluate some login in master database.
I tried to restore master database to test server, but I got some errors regarding about user databases are not exists in test server. I don't want to restore user databases, I only need master database for evaluate user login.
Again thanks for your advice.
I have performed several restores due to poor equipment(in the past where I used to work, now I have a brand new HP); this database has never crashed. I read an article in which the guy said a good DBA should be doing a test restore at least once a month. I see where that would be extremely important since i test my ups weekly, and my genset once a week, why not ensure that the data is good also.
OK, HOW?
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
Hello,
We are trying to attach a database in Sql Server 2005 Express on Windows Vista.It will Show the following Error.
"Unable to open the physical file "Test.mdf". Operating system error 5 :". And then we installed the SQL Server 2005 Service pack 2. This error again comes.Could you please give me the steps to resolve the problem.
Thanks,
Mahesh
I created a .net console application within which I connect to a sql server (not local) as follows,
string conn_str = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=QuantEquitySql;Data Source=server name";
SqlConnection conn = new SqlConnection(conn_str);
conn.Open();
.
Everything works fine when I run it from my computer. When I try to run it from a network share I get the following error,
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am using Visual Studio 2005 and .Net framwork v2.0.50727.
Does anybody know a fix to this problem?
Thanks
I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Here is the code :
************************************************************************
Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string
i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()
for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j
rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function
******************************************************************
Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes
how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?
Chi
I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.
here are some ideas I have but have not yet tested:
Create a maintenance plan with each 3 jobs:
--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3
I would like to use seperate jobs as to get a report on the duration and status of each job
Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.
Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
Hi,
I've written this code multiple times now. But for the first time i get an error at the line underlined. My procedure runs perfectly when i execute it through Sql Query analyzer.
plzz help.. Its urgent and am unable to find the reason for this error "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
Thanks !SqlConnection conn = new SqlConnection(DbConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.rqryTradesPRR";
cmd.Parameters.Add("@COBDate",SqlDbType.DateTime).Value = "2002-10-31 00:00:00.000" ;
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
// have written something here
}
Thanks in advance !
I have a sql express database with a couple tables of information. I have a user modifying one of these tables with a data access page using Office Web Controls.
To backtrack a bit, the sql db used to be an access db. The same user modifying the data the same way. I also had a asp page making odbc calls to the access db to lookup information. But if the data access page was open, the odbc page would error. So i moved the data to sql.
Now, I'm getting ready to setup an application to make odbc calls to the sql db. I still want the user to modify the data using the DAP. Should I allow the user to modify the live data used for the app or should I make a copy of the data everytime its updated?
trying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
View 1 Replies View RelatedThis is a bit confusing but here goes: I need to access data in SAP via OLE DB. I can't go direct to the back end database (Oracle), we have to use RFC or BAPI calls to access the SAP data. That's part works, we have a DLL that accesses the SAP data we need.
View 1 Replies View RelatedI have a live server running windows Nt and sql server We brought a new
backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
Regards
Ramesh
I want to build a windows application in Visual Studio 2005 that grabs some data from a SQL DB and displays it in a gridview. That is easy and I already have it done, but I also want to know how to show this data in real-time. For example: right now we have a application that pulls some login information for all the users and displays their phone extension, their name, and their status. They can change their status by clicking on some radios below the datagrid and on change it updates the data and then posts back. However, I dont know how the guy who did it made it so that these local changes are automatically shown on everyone elses computers around the office. In other words, they are shown the live data as it changes, without having to refresh it or anything. How would I go about doing this or does anyone have any places/resources that could help me accomplish this task. Thanks.
Oh and the guy who wrote this is no longer working here and he deleted the source code so i cant look at it.
We have an MIS system which has approx 100 reports. Each of thesereports can take up to several minutes to run due to the complexity ofthe queries (hundreds of lines each in most cases). Each report can berun by many users, so in effect we have a slow system.I want to seperate the complex part of the queries into a process thatis generated each night. Then the reports will only have to querypre-formatted data with minimal parameters as the hard part will havebeen completed for the users when they are not in. Ideally we willgenerate (stored procedure possibly) a set of data for each report andhold this on the server. We can then query with simpler parameterssuch as by date and get the data back quite quickly.The whole process of how we obtain the data is very complex. There arevarious views which gather data from the back office system. These arevery complex and when queries are run against them including othertables to bring in more data, it gets nicely complicated.The only problem is that the users want to have access to LIVE datafrom the back office system, specifically the Sales team who want toaccess this remotely. My method only allows for data from the nightbefore, so is there an option available to me which will allow me todo this ? The queries can't be improved on an awful lot, so they willtake as long as they take. The idea of running them once is the onlyway I can see to improve the performance in any significant way.True I could just let them carry on as they are and let them sufferwith the performance on live data, but I'd like to do something toimprove the situation for them.Any advice would be appreciated.ThanksRyan
View 2 Replies View Related
Hello,
I have requirement to cache report server as soon as data get refreshed in datbase.My database get refreshed every 10 minutes.
I am working in stock and bond domain were data changes very frequently.My user want to see almost live data whenever accessing report in report manager.they also want to cache some data for better performance.Can any one tell me step's to improve performance of reporting services .
Kindly suggest me.
Thanks.
Monika Singh
Can system databases (master, model, msdb) backed up from SQL2k SP3 be restored to an instance of SP3a? Can they be restored from SP3a to SP3? TIA!
View 4 Replies View RelatedHi All,
I need to do a restore of all my system databases, master, msdb and model, and of course my production database.
The Windows 2003 OS and Sql Server 2005 have both been reinstalled on the server.
I understand I need to be in Single User Mode to restore the master database and I just wanted to clarify the procedure for a successful restore.
I've found this page: http://msdn2.microsoft.com/en-us/library/ms190679.aspx which has instructions for restoring a master database.
So, all I have to do is:
1. Be in single user mode
2. Using SQLCMD run:
C:> sqlcmd
1> RESTORE DATABASE master FROM DISK = 'Z:MyBackLocationmaster.bak';
2> GO
From the instructions:
"After master is restored, the instance of SQL Server shuts down and terminates the sqlcmd process"
My Questions:
When I've restored the master do I repeat the steps above to restore the msdb and model databases?
Are there any other pitfalls?
Thanks for any advice
Melt
Hi all,
I have to make many tests on datas i have in 2 specific tables.
My goal would be to backup datas somewhere to be able to restart from clean environment and tests many things with the same datas into my tables.
Could someone explain how to do it ?
thanks
Florent
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:MSSQLBACKUPDBPRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ????
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik