I have a database running on sqlserver 2000. This database (let's call it TestDatabase) has 2 filegroups called 'PRIMARY' and 'SECONDARY', and is bulk-logged. The 'SECONDARY' filegroup is set to readonly since no data gets changed there by the applications, it is read-only data. Sometimes this read-only data needs an update, so I need to update the data in the 'SECONDARY' filegroup by running some long data-operations. These data-operations are executed and checked on a another database (which is a backup of TestDatabase) called TestDatabaseDemo. When everything is correct in TestDatabaseDemo we can copy all objects from the 'SECONDARY'-filegroup of TestDatabaseDemo to the 'SECONDARY'-filegroup of TestDatabase using DTS. This takes a long time and I want to try if I could speed up things by using the following strategy:
Take a full backup of TestDatabase
Restore full backup as TestDatabaseDemo
Run long-running data-operations on TestDatabaseDemo
Take a backup of TestDatabaseDemo
Restore only the 'SECONDARY'-filegroup from TestDatabaseDemo to TestDatabase, and keeping the 'PRIMARY' filegroup from TestDatabase as it was at that moment.
Suppose we arrived at step 5, I am executing the following commands:
Code Snippet
--make full backup of TestDatabase
backup database TestDatabase to DISK='G: emp estdatabase.bak' with init
--make full backup of TestDatabaseDemo
backup database TestDatabaseDemo to DISK='G: emp estdatabasedemo.bak' with init
backup log TestDatabase to DISK='g: emp estdatabase.log'
--restore secondary filegroup from TestDatabasedemo-backup
restore database TestDatabase FILEGROUP='SECONDARY' FROM DISK='G: emp estdatabasedemo.bak'
with move 'TestDatabase_Data_Secondary' to 'G: empTestDatabase_Data_secondary.ndf', NORECOVERY
--restore primary filegroup from Testdatabase-backup
restore database TestDatabase FILEGROUP='PRIMARY' FROM DISK='G: emp estdatabase.bak'
with move 'TestDatabase_Data' to 'G: empTestDatabase_Data.mdf', NORECOVERY
--restore log and try to get db onlin
restore log TestDatabase FROM DISK='g: emp estdatabase.log' with recovery
I get the following error:
The log in this backup set terminates at LSN 6000000021500001, which is too early to apply to the database. A more recent log backup that includes LSN 6000000022400003 can be restored.
When trying to execute "RESTORE DATABASE TestDatabase WITH RECOVERY; " as last statement
I get error:
The database cannot be recovered because the files have been restored to inconsistent points in time.
How i can restore the read-only filegroup correctly?
Hi , I am restoring a VLDB from a Standby server which has just .mdf and .ldf to my production server which has .mdf and a file group with a single .ndf file, i want to restore the databse on to the Secondary file(is this possible) i checked the BOl but could not figure out of what command is to be given
SQL 2000I have server1 with two filegroups.I want to backup FileGroup Primary on Server1 and Primary has aphysical file called MyData.mdf.I have server2 with the same database and two filegroups BUT thePhysical names are different. The Primary FileGroup is in physical fileOurData.mdfHow can I get server1->MyData.mdf to Server2->OurData.mdfThanks !
Using SQL Server 2000 SP3 I'm developing a data warehouse where datawill be archived off to a filegroup, this filegroup backed up and thetables in this filegroup truncated to free up space on the server.So using Enterprise Manager I've copied data to the filegroup tables,backed up the file group, truncated the tables, but when I haverestored the file group the database is stuck in a state of'(loading...)' and I can't get it to resume. I've tried running thecommand RESTORE LOG databaseName WITH RECOVERY but get an error messagethat the database has been restored to different time (sorry I can'tremember exact syntax but I'm not at my work computer)Is this familiar to anyone? Ignoring the filegroups is there a way Ican get the database .MDF back up and running?Thanks
Last time I applied new filegroup to our database. I call it HISTORY, bacause it stores all moditications of tables on PRIMARY filegroup. I.e. if on PRIMARY filegroup is table User, then on HISTORY filegroup is table UserHistory which, stores changes apllied to User table. It's simple database changes monitoring. We use SQL SERVER 2000 Standard Edition.
One person of our team (Artur) needs copy tables from PRIMARY filegroup to his computer to perform some experiments. Previously there aren't problem. He make full backup of our database, copy it to DVD drive and restore it on his computer. Backup was size of 3GB.
When we added monitoring full backup is size of 20GB which is too large to DVD disk. So I thoght that we can backup only PRIMARY filegroup, because history of changes is useless for Artur.
Unfortunately it is not simple as we wish. I tried those steps:
Perform PRIMARY filegroup backup to file. Copy this file to Artur's computer by DVD disk. Perform our database's Trnsaction Log on Artur's computer. Restore PRIMARY filegroup on Artur's computer (then database is "Loading" is Enterprise Manager) Restore backup done in step 3. --- Step 5. yelds error (File 'TeleDB' has been rolled forward to LSN 51...01. This log terminates at LSN 50...01, which is too early to apply the WITH RECOVERY option. Reissue the RESTORE LOG statement WITH NORECOVERY.
What is wrong? Is it possible to move data to Artur's computer this way, without full backup and serie of log backups? Can I use other alternative solution to move data?
I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?
I have backup my database with "file and filegroup"selected. I have dropped my database by accident. is it possible to restore my data back from "file and filegroup".bak??
Our current application which is deployed to numerous client sites usually requires the database to be returned to us to upgrade for the next release. The current process is:
- Perform a full backup at the client site - Send the backup to us for upgrade - Perform a full restore process to our local db server - Modify the database - Perform a full backup - Send the backup to the client - Perform a full restore over the original database.
This has served our purposes so far, however, we are about to introduce functionality that will enable the client to upload files for storing in the database.
To manage this we have a single DBFiles table which due to the potential size of it’s contents we determined that it would be best to store it in it’s own FileGroup. The idea being that then we could backup just the Primary FileGroup for returning to our offices as the DBFiles table would remain untouched.
The proposed process would now be:
- Perform a partial backup of the primary filegroup at the client site - Perform a partial backup of the DBFiles filegroup at the client site. This only ever stays at the client site and is never restored at our office. - Send the primary filegroup backup to our office for upgrade - Perform a partial restore or the primary filegroup to our local db server. This will leave the DBFiles filegroup offline which is OK as we do not need to change it. - Modify the database - Perform a partial backup of the primary filegroup - Send the partial backup to the client - Perform a partial restore of the primary filegroup over the original database. This will leave the DBFiles filegroup offline. - Perform a partial restore of the DBFiles filegroup over the original database.
This last step results in the recovery failing due to a reason like “The roll forward start point is now at log sequence number (LSN) 66787000000001800001. Additional roll forward past LSN 66787000000008900001 is required to complete the restore sequence�.
I have tried a number of variations including backing up both the client and local log files for recovery and using copy_only but I have yet to be able to successfully complete the process. Is there a means we can synchronize these 2 backup files without worrying about the logs for this part of the process?
Our requirements are basically to be able to perform a partial backup and exclude the DBFiles table as it is likely to be very large so we can send it back to our offices for upgrade and then return it to the client for restoring back to the original database while maintaining the data in the clients DBFiles filegroup and table.
Any guidance on what we need to change or a better process would be appreciated.
System.Data.SqlClient.SqlError: The backup of the file or filegroup "CUSTBilling" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
I did a file group backup and restore of a database and then when I tried to perform a full backup I am getting the above error. Any thoughts. I indeed happen to see this error http://support.microsoft.com/default.aspx/kb/921106 but not sure if both errors are the same.
I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
I am new to DBA activities. I have a database name Sample_DB in SQL Server 2014. Â
This database has below files
LogicalName FileType FileGroup Sample_DB_Rows  ROWS Data PRIMARY Sample_DB_C1 ROWS Data Country1 Sample_DB_C2 ROWS Data Country2 Sample_DB_Docs FILESTREAM Data FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem: I need to take backup of each file group separately. I need to restore the backed up file group separately. Is this possible, how to do it.
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have a production SQLServer 6.5 on Nt 3.51 SP4 that had problems two weeks ago, an I had to restore the backup of the previous night.
we are testing our disaster recovery procedures on another server, identical to the production one.
We have installed NT 3.51 SP 4 and SQLServer 6.5 in the same order and with the same configuration of the production server, but i can't restore on the test server the backup files that i have succesfully resotred on the production server.
we make the SQLServer back up on file ( and then we backup those files on tape with NT backup) so I don't think is a problem of HW Tape Bios or Compression because i have tried to resotre directly the files without taking them from a tape.
1) I have created a new database , without data, with the same devices, in megabyte, that I have on the production server. than from enterprise manager I have started the restore from file, and after two seconds everything stops, and I can't even shutdown the task but i MUST turn the server off using the power button.
when I start the server again there are no specific errors in the event log nor in the SQLServer error log. The db i was trying to restore is marked "loading" and it is not available.
This same procedure works perfectly on the production server.
2) I have then created another db on the test server, and I have succesfully tranferred ( but not resotred) the produciont db onto the test one, using the transfer menu on SQLEnterprise manager.
3) another strange thing is that I have tryed to expand the tempdb, which is only 2 mega by default, but whenever i try to expand it, using enterprise manager, only the LOG area of tempdb sucessfully expands, not the data area. I don't know if this behaviour is related to the unsuccesfull restore or if it is another problem.
i have checked the sort order and character set of the 2 servers and they are the same.
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
Hi, I wanted to see what are all the users in a windows nt group that has a group access to sql server 2000. I have a windows 2000 group access to sqlserver 2000 as "xxxsomegroup". How can I list all users that belongs to this windows 200 group? is there any stored procedure to find out this? any information could be greatly appreciated.
I recently had to reinstall a new instance of SQLServer 2000, but was unable to use the previous server name. As a result, my Access2000 front end is not happy with it's linked tables. I can't seem to find anyplace within Access to universally change the address of the SQLServer used as the back-end for all linked tables.
When I do try to access the linked tables through Access, I get an error, and the option to change the server location. When I try to type-in the new SQLServer location, there is an attempt to reconnect to SQLServer, but a whole lot of errors are generated, and none of the data is transferred into the Access table.
I really don't want to have to re-do my Access front end, so it seems it would be easiest to somehow reinstall SQLServer to have the same server location it used to. Is there a good way to completely erase all traces of SQLServer so that I can have better luck reinstalling it to the same location it used to be in? Just using the uninstall program from SQLServer doesn't seem to be cutting it.
Hi guys! When it comes to writing sql statements, I do ok. But I just know the basics on sql server dba stuff.I need to create a sql server login account that has the ability to backup and restore databases, BUT NOTHING ELSE.No ability to change anything other than via the restore process. I figured out how to do the backup portion of my requirement, but not the restore part.Any ideas? Thanks in advance!
When I try to make a connection to an Access .mdb I get the following error: "Unable to open application. The workgroup information file is missing or opened exclusively by another user"
Yet, I am able to open the file through Access and have necessary permissions and I know no one else has it opened. The mdb is password protected and I have provided the correct login information in the DTS connection.
I have a performance related question about the DTS package in sqlserver 2000 which i have developed We have developed a DTS package which will migrate a view 'ATTRITION' from Sqlserver 2000 to an Oracle database.The design of the package is as follows First step: It checks for the existance of the table 'ATTRITION' in oracle database, if table 'ATTRITION' is not there it will create a table called 'ATTRITION' in the oracle db.If the table 'ATTRITION' is already present in the oracle db,then the table is truncated.
Second step: The view 'ATTRITION' is migrated to Oracle table 'ATTRITION'. For the migration, i have used a connection object which connects to sqlserver 2000 and for oracle connection i have used another connection object 'Microsoft ODBC driver for oracle' and i have joined both the connection objects with 'Transform data task' task which maps one to one from sqlserver 2000 where view 'ATTRITION' exists with oracle database where Table 'ATTRITION' exists. Roughly i have around 65000 rows in 'ATTRITION' view of sqlserver 2000 which needs to be migrated.When im running the package on my system it takes around 4 minutes to migrate all the rows but when im running it on the server it takes a lot lot of time more than 1 hour.
The view definition im using has more than 10 tables joined together.But if its a problem of query used in the view,and if i run the view seperately it quickly displays the data hardly takes 1 minute. and even if i run the package on my local pc it doenst take much time.Now my confusion is why its taking soo much time on server.If i create a indexed view then will it solve my problem.Please suggest... Thanks in advance
Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.
From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?
And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.
I have an sqlserver 2005 Enterprise edition (Evaluation version) installed on one of my servers.It got expired after 180days and now i want to transfer the database that was residing on that Evaluation version sqlserver to a new Development server.
Do we have any way to restore the database from a already expired Sqlserver trail version to a new Development server. I dont want make an upgrade. Please suggest me.
I am trying to restore a database backed up using SQL Server 2005 Express Edition to a server using MSDE. I get an error 3205 "Too many backup devices specified...64 max..."
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLAlamoDB.mdf'. (Microsoft.SqlServer.Express.Smo)
--------------------------------
Hi,
I got this error while trying to restore my database (which was running on SQL Server 2005 Express Edition) onto another machine's SQL Server 2005 Express Edition.
I copied over a small website to 2003 Web Server and got: Exception Details: System.Data.SqlClient.SqlException: Failed to update database "D:INETPUBWWWROOTLUNCHCOUNTAPP_DATALUNCHCOUNT.MDF" because the database is read-only. It works just fine in VS - what to do? Thanks
