Help Needed - How Can I Set Up A Backup SQL Server Machine As An Exact Copy Of My Production SQL Server
Jul 20, 2005
Any help would be greatly appreciated.
My problem is that I need to set up a backup SQL Server 2000 machine
which can be used in case of a failure to my primary. All databases
(30 as of now) must be an up to the minute exact copy of production
and include most recent changes in data as well as any structure
changes (Tables, Views, SP's, Triggers, Users . . etc).
When I tried this using Transactional Replication, the replication
process gets fouled up once I introduce any kind of structure changes
to the DB. I've considered the idea of doing periodic backups and
restoring it to my backup SQL server, but this does not give me the
concurrency needed with 0 latency.
I've seen articles that recommend using Transaction Replication with
'Scheduled Table Refresh', and also doing database dumps to restore on
the backup machine, but I have not been able to find any documentation
regarding this to try out. How can I implement this type of backup
strategy in SQL 2000?
View 2 Replies
ADVERTISEMENT
Dec 13, 2007
Hi SQL folks,
I've googled for this the whole yesterday and I couldn't find a complete solution.
I'm having a sql2005 database in my development machine and I need to copy all the content "Tables, Diagrams, PK columns and other data" to the remote production machine.
Using the Import/Export method didn't copied the relationships between tables, also it turned the PKs into just a standard columns
Can anyone help?
View 1 Replies
View Related
Jan 31, 2007
I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
View 3 Replies
View Related
Aug 1, 2007
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies
View Related
Nov 16, 2006
Hi to everyone,
I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.
View 2 Replies
View Related
Apr 20, 2007
How can I create a copy of an existing sql server 2005 (live or offline) database and put it on the same server in a test database ?
View 4 Replies
View Related
May 29, 2007
I have a 20 GB SS2005 database that I would like to be replicated to a dev server for testing purposes. I might have some issues with the firewall blocking ports. I haven't read up on the specifics of how log shipping works yet, but I assume that is the best approach. A daily transfer would suffice.
A few options I am considering:
1. use the built in stuff, but I'm not sure it will work with our security settings, and if the trans logs are large it might be strained.
2. writing a small custom app to zip up the log file then ftp them down to the dev server. This could run nightly.
3. some third party util, such as FolderShare could transfer the log files
Any comments or suggestions?
View 4 Replies
View Related
Sep 14, 2007
Hi and thanks in advance for your help.
I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
Dollarjunkie
View 1 Replies
View Related
Jul 23, 2005
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
View 4 Replies
View Related
Jul 23, 2005
I have a database file approximately 30GB in size which creates 3 datatables daily. I need to reduce the size of the DB due to disk sizelimitations. Could I EXPORT some tables, let us say over thirty daysold, to something like DB_Name_Archieve and them delete them from theproduction DB to reduce the size? I also need the ability toreference/read them at a later time as needed.As a side note, could SQL Server use data on a redirected drive i.e.NET Used. This would allow me to move the old tables to the redirecteddrive and reference them as needed.As always, thanks in advance.Peter
View 1 Replies
View Related
Oct 18, 2005
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advance
View 1 Replies
View Related
Jul 23, 2005
I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler
View 2 Replies
View Related
Jul 23, 2007
Hi,
I need help finding a good back up solution for SQL Server 2005. I am hosting several databases in a shared environment and do not have admin rights to detach & copy & reattach the database file. I can have the hoster back up the file, but that is a manual process and I have had other issues with it in the past (access rights, cost, etc.). I have also tried creating a new business intelligence project, but have found it difficult to do a complete exact copy (while maintaining primary key values) as I run into many concurrency violations. This is especially true in databases that use the standard asp.net membership services tables.
Is there a way to get an exact duplicate of the structure & data of a sql server 2005 database without having to create a new Business Intelligence Project? Or is there an easy way to do this within BI that doesn't require a table-by-table setup? The primary purpose of this backup for me is for use on my development machine. Backup for data security/recovery is handled by the ISP.
Thanks in advance for your help!
View 5 Replies
View Related
Oct 9, 2007
I want to create an exact copy of a system running SQL 2000.
I want to copy MDF and LDF, but I cannot bring server down cause it works 24x7. What can I do?
View 4 Replies
View Related
Sep 19, 2007
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
View 2 Replies
View Related
Jan 23, 2004
Hi people!!
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)???
Thanks
View 1 Replies
View Related
Jun 29, 2015
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]
I tried doing above still no luck.
View 3 Replies
View Related
Oct 9, 2014
SQL Server 2012-SP2 and Windows 2008R2
I need to copy the last differential backup file from the production server directory to a data warehouse server directory. There are 3 differential backup files on the directory and I need to grab the latest one. I have the following syntax which work from a batch file but it does not work when I put it into a job step as a cmd even though I remove the double %% to only one %.
echo @off
set path1="192.29.305.213$SqlserverProductionBa ckupsKBR_PROD"
set path2=K:SqlserverDatawarehouseBackupPROD
for /f "tokens=*" %%a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%%~a" "%path2%" & goto nextstep
:nextstep
Echo File Copied.
Even when I change it to (only one % for the variable),
for /f "tokens=*" %a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%~a" "%path2%"
It does not work..........It runs successfully but no file is copied or I get
Message
Executed as user: PROD23Sqladm024. The process could not be created for step 1 of job 0xEAAF943771FF304A9E7AD8ADAC24F96C (reason: The system cannot find the file specified). The step failed.
I know that the file is there. It works with batch file. Poweshell script can be OK. No SSIS - Not installed
View 6 Replies
View Related
Sep 23, 2014
I've an emergency requirement to copy Source server database backup files to destination Server through xcopy command. Backup job on source server runs daily, so once this job get completes all databases backups needs to be moved to destination server. But here the main concern is "the backup files on destination server shouldn't be overwritten, they should be placed separately as Source server job runs daily".
We've a command which overwrites backups on destination server. But we need to keep backups on destination at-least for 4 weeks (means : retention should be 4 Weeks).
View 5 Replies
View Related
Nov 21, 2007
I'm looking for a way to copy/migrate all of my SSIS packages from 1 SQL2005 server to another SQL2005 server. I see export/import options but they are for 2000 DTS packages. And it seems like I can only do this one package at a time, which is tedious. Anybody out there who's done all packages at once?
Thanks!
View 9 Replies
View Related
May 2, 2007
1.) Can an aspnetdb.mdf database be configured and setup on one server and then be moved to a production server or is there something machine specific that keeps this from being possible? 2.) Is putting this file in the app_data folder something that is used only for SQL 2005 or SQL express? I had to set up a connection string in my SQL 2000 installation to get the connection to work.
Thanks for any input!
Colelaus
View 4 Replies
View Related
Nov 22, 2007
Hi
i can install and run my package in my development system work fine.
The same package when i was installing in the production system error
error: the acquire connection method call to the connection manager "con name" failed with error code
0xc0202009
I used sql server authentication and
tried the protection level
both encrypted password protected and sensitive with user key
Ezhil
View 4 Replies
View Related
Oct 2, 2007
Hi everybody.
I have such problem:
After I move my integration services packege to another machine it failed. I tried to recreate connection manager, but result is the same. If I create a simple SQL task in that package and try to execute it - it fails with info:
SSIS package "Main_Package.dtsx" starting.
Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "SRV-DSA3.TIA_EXTRACTS_OriginalData.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Execute SQL Task
SSIS package "Main_Package.dtsx" finished: Success.
But when I create it(SQL task) in the new package - it works perfectly. Maybe somebody could help solving this problem.
Thanks for any information.
View 4 Replies
View Related
Apr 28, 2006
I need to know the exact upper limit of the Integer Data Type in SQL Server 2005Thanks
View 3 Replies
View Related
Jul 23, 2005
Hi All;We are going to change our application server. We will copy all of ourDatabase from Mic 2000 Server OS to Mic 2003 Server OS. I found anarticle that how to move all the folders from same OS. My question isthat Would the 2003 OS be a problem when we copy all of the MC SQL 2000folders to New OS ? Has anybody done this before? Could you gimme yoursuggestions please?ThanksASA
View 1 Replies
View Related
Oct 23, 2006
Hi,
If I use GetDate() function from client machine (using "\ServerMachineSQLExpress" instance from client machine "ClientMachine"), then from where I will get the DateTime value ?
From "ServerMachine" or from "ClientMachine"
Thanks
View 3 Replies
View Related
Apr 12, 2015
I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my staging table
Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)
I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.
View 5 Replies
View Related
Oct 21, 2015
Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.
I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.
In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.
Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN?
View 11 Replies
View Related
Nov 1, 2007
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
View 2 Replies
View Related
Jul 15, 2015
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies
View Related
Jan 28, 2008
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies
View Related
Feb 18, 2015
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies
View Related
Apr 23, 2008
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
View 6 Replies
View Related
