How To Move Datafile On A Standby Database
Oct 9, 2007
I need to move a datafile on my secondary database which is in standby mode. I have attempted to use the Restore command with the move and standby parameters
use master
RESTORE LOG BWP FROM
DISK='L: rans_bkpBWP_20071009080001.trn'
WITH MOVE 'BWPDATA3'
TO 'N:BWPDATA3BWPDATA3.ndf',
standby='L:TRANS_BKPBWP_20071009130001.tuf'
But I get the following error message
Msg 3174, Level 16, State 1, Line 1
The file 'BWPDATA3' cannot be moved by this RESTORE operation.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
View 20 Replies
ADVERTISEMENT
Aug 17, 2006
I obviously did not search the archives on the right terms so what isthe easiest and fastest way to move a 3G database from a nearly full Cdrive to the nearly empty D drive that should have been used.I could back it up, drop it, recreate it using the D drive, and restoreit but it seems like there should be a way to just move the datafileand use if from the new location.I am thinking that detatch/attach is the best method, but I would likeconfirmation or suggestions on how to proceed or things to be aware ofwhen using this method.-- Mark D Powell --
View 8 Replies
View Related
Jan 28, 2008
Hi all
I've found this problem working with a VLDB, six month ago when I install the DBMS (Win2k3 x64+sp2, SQL 2k5 x64 +sp2, 4 dual core processor and 12 GbRAM) I've got 10 disk (actually ten LUN from a Storage Area Network), each 50Gb.
I've put TempDB and Transaction Log on two separate 50 Gb disk and put the database on 8 different data file on the 8 disk; I've created each datafile with a size of 50 Gb (autogrowth disable), so my DB has 400Gb space in it's datafile.
After a while the datafile began to fill and we decide to add a couple more 50Gb disk where I decide to put to new datafile; now my db is around 430 Gb and I've got this strange situation:
The first 8 datafile now are almost full of data, and obviously they can't growth since they already occupy the whole disk.
The two additional datafile are relatively empty (about 15 Gb each).
As far as I understand now each time that SQL should write to the databse it writes only on the 2 new datafile, and I fear that this can affect performance.
I'd like to reorganize the space in order to have 10 datafile, each with 43Gb of data, but I didn't find any instruction/tool able to move data between datafile.
Anyone has any hint ?
Thank you in advance for any suggestion
Stefano
View 5 Replies
View Related
Nov 19, 2007
Hi,
The standby file (.tuf file) got deleted accidentally on the secondary. How can I recover the database without the standby file?
View 3 Replies
View Related
Nov 14, 2007
I've been reading a million and one posts on replication
My scenario is that i have a live SQL 2000 server. In a DR invokation, i'e i've lost my live sever, i want to be able to access the same data at the DR (SQL 2000) site and have it accessable to the users. DR server has a different name to the live box.
Replicate
Master
CRMDatabase
Data changes all the time but can have hourly replication of transaction logs for this example. I've currently researched a sp called update logons but this has to be fed each account name to enable them on the new server. There must be a way to activate all CRMDatabase logons with the new server?
Could someone be kind enough to lead me through a step by step guide on the best solution.
Thanks a lot
David
View 3 Replies
View Related
Dec 22, 1999
In order to perform an automatic way
to link a software using .txt database
to our SQL Database, I need some tips.....
I think about the following solution
- maybe using a "data-base extractor" (Access)
and convert the result into a .txt file,
Which can be automaticly refresh using a
.bat file which will open the .txt file created
3 times a day to refesh the data.
If you have some solution less complicated,
Please send it to
atoarlindo@yahoo.com.hk
Arlindo
View 1 Replies
View Related
Oct 13, 2000
Does sql server have hot standby database? what are the pros and cons of using replication vs hot standby database? Thanks for helping me.
View 1 Replies
View Related
May 15, 2015
one of my database is configured in availability group , I need to add another datafile to that database , how can I do this?
View 6 Replies
View Related
Jan 16, 2008
How can I get a SQL Server 2005 database out of Standby/Read-only mode? I just restored this DB sent from a customer and it is in Standby/Read-only mode. I need to add a user so that I can authenticate from my app for testing purpose.
Thx
View 9 Replies
View Related
Feb 7, 2006
Hi all,
we want to create a offsite redundant server which can be a standby machine if something happens to our primary machine, any input how to do this over internet? what type of bandwidth we need, and for security ssl or vpn? or anything i missed thats important to consider. Plans are to use sql server 2005. Thanks all
View 1 Replies
View Related
Jul 31, 2006
Hi,
I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupLogShipCI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....
To sum up:
Is there anyway to access the standby server without causing the restore process to fail?
Both the Primary and Secondary are SQL 2K5.
Thanks!
View 9 Replies
View Related
Jul 20, 2005
SQL Users/DBAs,I'm trying to move data files around for a database that is in standbymode. I can detach/attach the database fine usingSP_DETACH_DB/SP_ATTACH_DB , but when I re-attach the database the logsequence is broken and I can't restore any more logs. Does anyoneknow if there is a way to move around data files and keep the databasein standby mode?Thanks,JB
View 4 Replies
View Related
Mar 23, 2015
We log ship our production database tansaction logs to a secondary database on a separate remote domain.
Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
View 3 Replies
View Related
Aug 5, 2015
I need some clarification about adding file in to mirrored dataabse in primary server without downtime and breaking the mirror server.
In our environment we are using monutdisks in both the servers. in primary for ex we have F drive for data files under mount disk 3 in mirror server also we have same drive but in mount drive2.
As per my knowledge if it is same drives we can add the ndf files in the primary that will reflect on mirror. but in current situation i am confusing about mount points with different names.
View 3 Replies
View Related
Apr 23, 2007
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
View 8 Replies
View Related
Aug 27, 2007
hi,
I have a access database. In this database there are 10 tables and this tables are related to each other.
I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
how can I do this?
thanks..
View 1 Replies
View Related
Jul 18, 2006
I’m working with an msde database on my local machine, but need to take that database and move it to my remote host. This is problematic because I can not just do a back up and restore.
I was wondering if there were any asp.net applications that would generate lots of sql statements to reproduce the database on my remote host.
I had built a program to back up all the tables in that way, but, forgot that the membership stuff uses stored procedures, and I don't not know how to modify my program in order to back those up too.
Any help will be much appreciated, thank you very much for reading my post
View 7 Replies
View Related
Oct 29, 2005
Hello,
What is the easiest way to move a database from one server to another with the all table structures, view and stored procedures?
Thanks,
View 3 Replies
View Related
Aug 30, 2001
Using sp_detach and sp_attach we can move different databases to different locations.
how do we do the same for system databases such as master,model,msdb and tempdb.
As off i know we got to be in master database to runthis command, if not this how do we go about this.
Thanks,
sheena
View 6 Replies
View Related
Nov 2, 2001
I read the one of the replies to a question.
"Subject:
From:
Date: SQL 7 Restore (reply)
Manish (manish45@yahoo.com)
11/1/01 9:03:43 AM
If you want to move all the databases to new server. Stop the SQL server service on existing server. Copy mdf and ldf files of all databases to same location iin new server. Also take the backup of master and msdb database from existing server. Then restore master and msdb on new server.
"
My question is how to move my database to a DIFFEREN LOCATION on a different server. My two servers are different file layout (not my choice though.)
Thanks a lot in advance.
Wuchun
P.S. Should I use the DTS at all to perform this task?
View 1 Replies
View Related
Jul 21, 2000
Hi:
I need your suggestion or experience about how to move a database from SQL 6.5 to SQL 7.0. The server where SQL 6.5 resides locates in different network than SQL 7.0. DTS does not work for the database move because of two server can not talk directly.
We need to do this task very soon in production environment. I tried to backup database to a disk (backup device in SQL 6.5) but I failed to restore it in SQL 7.0 because the backup file was not detected by restore utility in SQL 7.0.
Please tell me what I should do. Besides BCP, are there any other methods to move the database from SQL 6.5 to SQL 7.0 in diffferent networks.
Thank you very much.
Joan
View 2 Replies
View Related
Jan 19, 2001
Hi
I am new to sql server 7 and dont know how to 1.make a copy of a database
and 2 to move a database i know there are sys stored procedures but the syntax looks crptic and i dont know where to type them in if any one could give me any really clear examples of how to do both of the above i would be really grateful, thanks!
View 3 Replies
View Related
Jun 24, 2004
i've got to move 37 SQL Server databases over to new server. My manager tells me that i have to conduct some "check totals" to make sure that the databases contained exactly the same data from what it was when it got moved.
Is there some stored_procedure to do this ? to make sure that all the stored procedures, views, tables got move across ?
View 2 Replies
View Related
Nov 10, 2005
Hi,
I'm new to SQL Server. How do I port the modifications of my table structures, store procedures, functions on the staging server to the procduction server without affect the data?
Can I make this modification to production database while user are still accessing the database or I have to bring down the database to make these changes?
Thanks in advance,
View 1 Replies
View Related
Sep 14, 2007
Is there a way to move database from developement to production servers along with the logins and their associated roles as well as passwords. I did backup and restore and used sp_change_users_login to fix the logins
I mean is there a way to copy logins and roles Thanks
View 3 Replies
View Related
Jul 23, 2005
Can anyone tell me if it is possible to move the data directory of SQLServerfrom the application server where SQL runs to another system (the file andprint server) in the network?regards Joop
View 1 Replies
View Related
Jul 23, 2005
I'm needing to move a database from SQL 7 to SQL 2000. What is thebest way to accomplish this and keep all tables, data, permissions, etcin place throughout the move.Should I use BCP, Replication or something else?Thanks for any help.
View 2 Replies
View Related
Sep 5, 2007
I have a web project in VS.NET 2005 on my development computer that uses an ASP.NET database (ASPNETDB.MDF) to manage user roles. Now I would like to move that database to a test computer with IIS. After copying the web project and database I am able to read from it but when I try to update I get the following error:
Failed to update database "C:INETPUBWWWROOTMyWebAppAPP_DATAASPNETDB.MDF" because the database is read-only.
I think this is because the user 'Network Service' that IIS uses does not have access to the database. So how do I give 'Network Service' access to the database using SQL commands?
View 1 Replies
View Related
Jan 25, 2008
I'm trying to move a database created on SQL Server 2005 Express Edition to the SQL 2000 Server (that's the version the company uses). I understand that this is done by making a script that, when executed, creates an exact copy of the database structure on the target server as well as copying the stored procedures.
After that I somehow need to export the data from SQL Server 2005 Express to SQL Server 2000. How can I do that. There's no export function in Management Studio Express.
View 8 Replies
View Related
Jul 31, 2006
I am using VWD 2005 Express, with SQL 2005 Express. My web hosting company supports ASP 2.0 and SQL Server 2005, but have put SQL on a database server, which is separate from the application server. I successfully published a "Hello world" test application, but my database application failed after displaying the logon page, presumably when it first tried to connect to the database.
VWD has created my database within the application folder (appApp_DataASPNETDB.MDF) and created the configuration string in web.config: -
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
This all works fine on my local development server, but naturally fails when the application is copied to the web host, and the database can no longer be run from an application sub-folder. With SQL 2000 the problem would have been simple to fix. I would have created a database in the host DB server. I would then have backed up the development database, and restored it into the target database. I would then have changed the connect string, and all would have been OK.
How do I get the development database into the target database with SQL 2005 express? I can't use backup/restore, since there is no backup within VWD 2005 Express, and although there is a backup within Management Studio Express (MSE) I would need to get the database into MSE first. I tried opening the MDF file, but MSE wouldn't recognise this. I can't find any way of exporting the database - the equivalent of SQL 2000's DTS is not available within MSE!!!!!!!!! I tried installing an eval. version of the full Management Studio, but the install failed because "there is no upgrade path".
Once all that's sorted (if it can be sorted - I may have to go back to SQL 2000), where can I find out what to write in the configuration string?
Thank you, Robert Barnes
View 15 Replies
View Related
Aug 22, 2007
I need to move two databases from my old PC to my new one. Both machines are running Sql Server 2005 Express. I have tried a backup on old, copy the files to the new but I can't get restore to work. I type in the name of the new database to be created when the restore is complete and then select "From Device" and point to the backup file. I have tried both backup of entire database and backup of files. The error says: "Operating system error. System cannot find the path specified" Well, it found the path and backup file in the previous step when I pointed it to the backup media.
I have also tried the "Copy database wizard". However, Express does not come with "Agent" and the two machines can't see each other without Agent even though I have configured Express to allow remote access.
Any other ideas?
View 3 Replies
View Related
Jan 20, 2008
HiAs i have read, there have been discussions before how to move a local MDF databas to MSsql server at a webhost.But i cant figure it out, i know the answere is out there but i cant find it.I have downloaded the Host Deployment tool that generates a .sql file from my database, it looks ok.The problem is that my host dont allow me to execute this. I have also tried to run a file called RunSql.aspx that should help me execute my .sql file.But i recieve errors
Opening url http://mydomainz1.com/mydbfile.mdf.sqlAn error occured: System.Net.WebException: The remote server returned an error: (404) Not Found. at System.Net.HttpWebRequest.GetResponse() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in d:hsadminaccoundidmydomainz1.comRunSQL.aspx:line 58The line 58 is empty
Is there anyway that a mdf file that i have build in VWD 2005 can work as a standalone database?I would like it to be as a .mdb database that is simple to move and so on.
Or do the mdf database have to have a MSsql Express or a MSsql Server?
Grateful for help!
/ mitmit
View 1 Replies
View Related
Jul 21, 2004
Hi,
Can someone suggest a good way to move the CSK database from ine instandc of MSDE200 to another on a different PC. I tried exporting using MS Web database administrator which moved the tables but stripped off the defaults and all stored proceedures.
Any better ideas?
Hal
View 1 Replies
View Related
