hi
To restore files and filegroups in a different server. First, Do i have to create the database with same name as the old one? or it doesn't matter... Can we use Enterprise Manager to do this restore?
Thanks in advance
What is the best method to restore a DBTest1 (with one .mdf and one .ldf) into DBTest2 (with one .mdf, multiple .ndf data files and with 4 filegroups associated with specific data files). I do not see how the one .mdf file (in DBTest1) can be separated into the other 4 filegroups (in DBTest2). This does not sounds like it is possible with Backup DBTest1/Restore to DBTEST2 or (Detach/Attach) because the underlying filegroup and file structure is different.
What method should be used to get the data and structure from DBTest1 (includes 1100 Tables and 550 GBs of Data) into DBTest2 (with 4 filegroups)? Is the following possible:
1) First, in DBTest2, execute a script to create tables/indexes on appropriate filegroups.
2) In DBTest2, use scripts to pull data from DBTest1 into DBTest2, for example INSERT INTO DBTest2.dbo.tables with SELECT FROM DBTest1.dbo.tables OR use SELECT/INTO DBTest2.dbo.tables FROM DBTest1.dbo.tables.
Or, is it possible to use the BULK INSERT or BULK COPY Options? Export/Import Wizard?
Does the Create Index step needs to be done after the data is loaded into DBTest2?
I am trying to restore from a backup of an individual filegroup in SQL Server 7.0. I have backed up the individual filegroups, and I want to restore one of them (not the whole database). The process in EM is almost the same as restoring the entire database, only you choose the radio button 'Filegroups' and the files/filegroups you want to restore.
The restoration appears to complete sucessfully, but afterward, my database seems to be permanently loading. In other words, in EM the database is greyed out and you cannot use it anymore. It stays like this until you drop the database altogether. Is this a bug? Has anyone else had this problem or does anyone have experience restoring from filegroups sucessfully???
Hi,
I want to take the backup of a particular filegroup in SQL server 2000 and then restore the same filegroup backup.
Currently I am using the following steps :
1. Creating the database with 2 filegroups. The syntax that I used for it is as follows :
create database test
on primary
(name = test1,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est1.mdf'),
(name = test2,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est2.ndf'),
filegroup group2
(name = test3,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est3.ndf'),
(name = test4,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est4.ndf')
log on
(name = test_log,
filename = 'D:Program FilesMicrosoft SQL ServerMSSQLData est_log.ldf')
2. Now I am creating two tables table1 and table2 using Enterprise Manager. Both the Tables, table1 and table2 are on the "Group2" Filegroup of test database.
3. After creating the tables, I inserted three records in each table.
4. Then I performed the backup using the following commands:
backup log test
to disk = 'e: est.lg1' with init
backup database test
filegroup = 'group2'
to disk = 'e: est_group2.bak'
5. After the backup has been taken, I deleted the two records from both the tables. Now both my tables have one record each.
6. Now I tried restoring the database using following command :
backup log test
to disk = 'e: est.lg3' with init
restore database test
filegroup = 'group2'
from disk = 'e: est_group2.bak'
with norecovery
restore log test
from disk = 'e: est.lg3' with norecovery
restore database test
with recovery
7. After all these commands are executed, when i checked my database, it contains again the one record in each table. Ideally, according to me the tables should contain 3 records as i had taken the backup when each table had 3 records.
How can I bring the database back to the state in which I had taken the backup.
Pls. help me as soon as possible.
And also let me know, if I need to do any SQL level or Database level settings for the above task.
Waiting for your reply. and thanking you in advance.
Regards,
a_k93
I was under the impression that if you are using multiple files in your filegroup to store your data that the data should be written evenly across the files.
I have 5 files and the data is being written to only one file which is growing rather large, while the other files are still at 5 - 7 MB. They are all marked for Automatic Growth at this point, but only the 1 file is growing.
Is there something else that needs to be done to enable this? BOL left me with the impression that this is automatic.
Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.
1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go
2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
.....
TO FILEGROUP filename_table
go
If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?
I think jthis is a bug. I have a table created and populated on its own filegroup. I backup the db(all filegroups) and the trans log then I drop the one table. When I try to restore from my backups, it insists that I back up the trans log again. I do, then do the restore of both the filegroup and the trans log. The restore finishes, but my table is still not there and I can never get it back.
Any one with comments please help
Brad kreuzburg
Hi, I have a database with about 200Gigs allocated to filegroups and 200Megs data in it presently, and am trying to create a copy of the database on the same server - except I don't have enough diskspace free for another 200Gigs. Is there a way using copy database wizard, or restore that the filegroup allocations can be resized?
Thanks,
Chris
I hope this is the right forum, if not sorry about that. On Friday I will be doing support with another orgnaization on a SQL 2000 cluster system. The database will be backed up , another team will do the major application upgrade, and I will be restoring the database back. From the SCN, there will be no database changes. I have been searchng for information on databse backups / filegroups.
My questions are:
1. The database has 3 filegroups, besides the primary. If I do a complete backup, will it also do the filegroups?
2. If not, how do I backup the filegroups?
3. Whne I resote the databse, how do I bring in the filegroups from the backup?
Any info or pointers to links is greatly appreciated. Have a good day.
Carl
i am not new to sql server but never think of how it work from the inside, but now i do :)
what i need to know and discuss her is how sql server work with filegroups and file inside the filegroup.
what i need to know exactly us that if i have multiple files within a file group how sql work and how object saved to this filegroup?
will sql server save all object data at the same file in the file group or the object data will be saved in multiple files in the file group?
Hi,
I backup db into several .bak files to reduce backup time. but how can I restore them back? Do I have to do it by scripts? Can I restore it by using management studio?
cheers
I have troubles with my computer, in the pc I have some databases but the only thing that they can recover were the .mdb and .ldb files.
How can I restore a database from these files?
i accidentally restored a db to a backup from last year and i don't have a recent backup - is there anyway to restore from log files?
View 2 Replies View RelatedI have a 24gig DB that has 1 data file.
Is it possible to RESTORE the backup to a DB that has 2 files
(1 = 10gig 2 = 14gig).
Does one of the files on the the DB have to be at least 24gig to RESTORE?
I'm the DBA in an environment with approximately 240 identical (structurally) MSSQL 2000 databases in an environment. Each one of these databases are different business units around the country. For each of these DBs, I have my backup schedule set up to do a full backup every friday night, along w/ doing a transactional every 4 hours in between. Every once in a while (about once every 3 weeks), I get the call to do a restore to pull some piece of data that some end user accidentally deleted or broke. Now most of the time it isn't a big deal, because I can use enterprise mgr to restore to a new DB from the live backup set of the database in question.But, several of the databases seem to have an issue where (for some reason) my predecessor renamed the logical file names of the data and log files to various goofy things. It doesn't affect their operation, but when I try to do a 'restore from database,' it gives me a bunch of big fat errors. This is also the case when the backup I want to restore something that has been written to tape and has been rolled out of the backup history.SO... basically for these databases, now I go into Enterprise mgr and first restore the base .bak file to a new database, leaving it marked 'read only' and 'able to restore additional transactions.' I then have to do the same thing with restoring each additional TRN file, all the while making sure I don't forget to set the 'able to restore additional transactions.' Whenever I forget to set that I have to start all over. And honestly, it happens more often than not when I have to restore 5 days out from the .bak file, which means that I have restore 30 trn files on top of the .bak. Not hard, but incredibly tedious.So here's my idea. I know I can script restores thru QA. I've been looking quite a bit at this MS doc (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E2AA) and have come up with some good ideas. What I want to eventually do is create a script that you can just paste the .bak file and the list of .trn files. The script basically creates a temp table w/ those file names and cursors thru the restore process. The cursor part is easy for me, I'm just not sure about scripting the trn restore. The MS doc is a bit vague and really only shows the Enterprise Mgr method.Here's what I've been playing w/ so far for restoring the base .bak file:restore database restore_dbname --NAME OF NEW DBfrom disk = 's:mssqlackupuserdbnamedbname_db_20060120184 1.bak' --PATH TO BAK FILEwithmove 'pm65mr1_default_data_OldDBName_Data' --LOGICAL FILE NAME OF ORIGINAL MDFto 'I:MSSQL$MSSQLSERVER2Data
estore_dbname.mdf' --PATH TO NEW MDFmove 'pm65mr1_default_data_OldDBName_Log' to --LOGICAL FILE NAME OF ORIGINAL LDF'U:MSSQL$MSSQLSERVER2Log
estore_dbname.ldf' --PATH TO NEW LDFpartial, recoveryI think I'm pretty close with this, but I'm not really sure how I can stack the trn restores on top of that. Any ideas? I've been putzing around the web and so far haven't been able to come up w/ anything really useful.
Hi,
I have a database that over time has become spread over different files, file groups all of various sizes.
I want to restore this database to a different set of files/filegroups and evenly spread.
It appears that I can only resotore a database to number/of and size of files from which it was backed up..
I want to redistribute a 40GB file, using EMPTY is taking for ever and then eventually fails.
What can I do?
Thanks for your help
I had a development server that had all the raid drives fail at once.
This included most of the data/log files and all of the backups.
I need to get DTS packages from the MSDB database, I have the ldf/mdf's for that database, is there a way to restore it?
Thanks
Susan
First, let me say that I have already reviewed information posted by experts within the chat area.
Someone completely deleted my SQL Server 7 database. I retrieved the .LDF and .MDF files from a network backup. Now I am trying to attach the database. I have tried this without creating an instance of the database by using the attach stored procedure to attache the .LDF and .MDF files I retrieved from the network backup. Also, I have tried it by creating an instance of the database and doing a detach of the newly created db .LDF and .MDF files and an attach of the .LDF and .MDF files I retrieved from the network backup. Neither of these approaches have worked.
Here is what I have tried in the Query Analyzer, but to no avail:
For the following example, I created a database instance called 'qarun_diamond_48_brett' with brand new .ldf and .mdf files and then tried to detach and then attach the .ldf and .mdf files from the network backup:
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett_log.ldf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
use master
go
sp_detach_db
'qarun_diamond_48_brett', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett.mdf'
Message: Usage: sp_detachdb <dbname>, [TRUE|FALSE]
I tried the following attaches of the retrieved/recoverd .ldf and .mdf from the network to the newly created db instance. That didn't work, so I tried attaching to a db that had not yet been created.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2_log.ldf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
If I use a different db name I get the following error:
Server: Msg 5105, Level 16, State 13, Line 1
Device activation error. The physical file name 'qarun_diamond_48_brett' may be incorrect.
use master
go
sp_attach_db
'qarun_diamond_48_brett2', 'F:APPSSQL 7.0DatabaseDataqarun_diamond_48_brett2.mdf'
Message: Server: Msg 1801, Level 16, State 3, Line 1
Database 'qarun_diamond_48_brett' already exists.
I have also tried these statements wtih the EXEC sp_attach_db and EXEC sp_detach_db commands from within the Master db in Query Analyzer.
Any help would greatly be appreciated.
Thanks,
-Brett
Hello,
I have a SQL 2000 DB. Current tables in sql 2000 DB are in single file. I am planningto migrate the DB to sql 2005. I am going to partition the tables in sql 2005 and have multiple files. What is the best way to do this? Would backup/restore work? If I restore onto sql 2005 from sql 2000 backup, will the tables spread over different files automatically or not? Any ideas will be appreciated...
Thanks........
Hi everyone,
already tried this in other SQL forums, but maybe i have some luck here.
I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step.
I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those.
Anybody any idea or even the same problem maybe with solution?
Thx.
I have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
View 6 Replies View RelatedI have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com
Their old POS computer had a hardware failure, but I was able to attach the hard-drive to another computer and recover the data. RPE uses a MSSQL database system. However, my client doesn't seem to make backups very often - the last one is dated January 5, 2015.
I was able to copy the C:Program FilesMicrosoft SQL Server folder over which contained the instance as well as all the data files - and has up-to-date information. The instance in the recovered Microsoft SQL Server folder was called MSSQL.1. I installed the RPE software on their new computer, and it too now has an instance called MSSQL10_50.PCAMERICA. The new computer is using MSSQL 2008 R2, while I believe the old computer would have been using MSSQL 2005.
I have an automation project to review the software. The software is using MSVS2012 and uses DBs from MSSQL2012. Is it possible to restore the DB files on my local laptop to review the code completed thus far? If so what other software would I need besides MSQLserver 2012 on my local laptop. I'm able to use MSSQL server 2012 on my local laptop, but the DBs are on servers.
View 4 Replies View Related1. Created a database with a couple of tables with no data.
2. Taken a full database backup - db.bak.
3. Deleted the database.
4. Restored the database db.bak and filled the tables with some data.
5. Taken log backup - dblog.trn
BACKUP LOG <dbname> to DISK='D:Demodblog.trn' WITH NO_TRUNCATE, INIT
6. Dropped the database again.
7. Restored the database again from db.bak.
8. But when I am trying to restore log file dblog.trn on this database, i keep getting this error :
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2 RESTORE LOG is terminating abnormally.
Hello,
I am attempting to restore the database from within VB.NET application I am making the following 3 calls:
RESTORE FileListOnly FROM DISK = 'C:MyDatabase.dat'
USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataLDFMyDatabase.ldf', REPLACE
RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat'
using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
ExecuteNonQuery failed for Database 'master'.
An exception occurred while executing a Transact-SQL statement or batch.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operator aborted backup or restore. See the error messages returned to the console for more details.
The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks
Eugene
LocalDb cannot restore a backup whose original data and log files are in different folders
[URL]
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies View RelatedI was told to move tables to new filegroups by placing the clustered index on the filegroup and the table would
follow. There are times when I see tables listed on the new filegroup but still listed also on Primary. My goal is
to have only system tables on Primary. How can I get a table to totally leave the PRIMARY group?
There seems to be a system index on the table that was not created by me.
I have databse that was created when I got here and the database was created with 3 file groups. The tables in the database are spread out over the 3 file groups.
How can I find out which table belongs to which file group.
Thank You,
John
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....
On separate disks ?
Thanks in advance..
Any comments welcome ! Peter
If I'm running RAID 5, is it still good practice to split system and user data onto separate filegroups ? .....On separate disks ? Thanks in advance..
Any comments welcome ! Peter
Hi,
I was wondering if there is a downside of placing individual tables into their own filegroup (i.e., 1:1). This would seem to allow me to continue to backup entire databases but give me the flexibility to restore at a table level if necessary. I ask this because the number of filegroups allowed per database has increased from 256 in SS2000 to 32K in SS2005. What kind of headaches could I get into if I follow this type of design ?
Thanks,
Mario
i want to obtain information about all the filegroups for every database. is there a special command to obtain this information like the dbcc sqlper or something similar ??
View 1 Replies View Related