Bloated Database Wont Shrink After Backup/restore SQL 2000
Jul 20, 2005
Help,
I have a database that has a data file of 2GB and a log file of 31GB.
In enterprise manager, when I choose shrink it says there is 30GB of
unused space. When I shrink the database, it does not shrink,
(however it says it has completed).
I've done a complete backup, tried shrink again, no dice. I then
backed up the database (which the backup was 1.9GB), deleted the
database and made a new database with 2,048MB for the data and same
for the log file.
When I restore, the log file jumps up to 31GB again. When I check the
space when I use the shrink, it again says I have 30GB of unused
space.
How on earth do I get this file to shrink?
I've been able to shrink other databases, but not this one.
TIA
Rob
View 1 Replies
ADVERTISEMENT
Jul 15, 2006
I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
Peter
View 2 Replies
View Related
Sep 11, 2007
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
View 6 Replies
View Related
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Oct 17, 2006
Hi All
This is a very urgent requirement of my client having 200 stores and one Head office. the HO has just installed SQL-Server 2005, The HO sends small backups of the data related to stock transfer which needs to be restored at stores. It was working find when both the HO and stores were SQL-Server 2000. but the process has come to a dead halt as the HO has SQL-Server 2005. It is not possible to convert the stores immediately. please help me out.
Thanks
Raoshan
View 3 Replies
View Related
Apr 30, 2015
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
View 9 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Oct 17, 2006
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
View 3 Replies
View Related
Mar 28, 2008
Hai friends.....
I am doing project in ASP.NET(vb) my backend is sql server 2000...in my PC..
I have another pc....where sql server 2000 is installed.....
i have nearly 50 tables...in sql server 2000. in my PC..
I want to backup these tables from my pc and another pc...
can u tell me the steps...
I will try immediately....
Ambrose......
View 31 Replies
View Related
Nov 24, 2005
Hello!
I have a bug in SQL Server 2000 that I would like to discuss.
The bug is described briefly on http://support.microsoft.com/kb/821334.
"The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."
According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.
Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.
The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.
Here is the information that I have got from Microsoft about this trace flag.
"Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."
I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.
The error happened on my server a few times every 24 hours. I believe it's a general problem.
Best regards
Ola Hallengren
View 9 Replies
View Related
Jun 10, 2008
Friends -
Is it possible to restore user databases in SQL Server 2005 with user database backup copy of SQL Server 2000.
Please advise on this.
Cheers:)
VSH
View 11 Replies
View Related
Feb 15, 2007
Hi All
I have a problem in transferring a 2005 backed up database to SQL Server 2000 SP3.
i got an error message, crux of it is
"too many backup devices specified only 64 allowed"
the i browsed MICROSOFT help, downloaded SQL 2000 SP4 - mentioned as a fix for the above error.
Now get a message,crux of it is
"current version of Database in 611 and destination version is 536"
Is there a solution????
should i download any other patches or
Should i tweak the SQL 2005 backup process???
ASAP Plz
View 1 Replies
View Related
May 26, 2006
Does anyone know if this works to where the data is viewable/usable?
I have an application that takes datasets out of the SQL database. We decommisioned our old SQL 2000 SP4 machine and brought in a SQL 2005 Server. What we did was backed up the old database and then restored it on the new Server.
Now the application does not see the old datasets but will collect new ones that are viewable. We can import older datasets but we have nearly 27 GB of data, which our DBA can see the old tables in Enterprise Manager but our applciation can't see it.
I'm thinking it might be a schema issue or the way that 2005 translate the odler 2000 SP4 database. Does anyone know, or have they run a back up on a older 2000 Server and then restored it to a 2005?
Thank you.
David
View 4 Replies
View Related
Jul 9, 2007
I need to restore a backup of database taken from SQL 2005 in SQLServer 2000.
I tried to restore from backup file; It says "Error 3205: Too many backup devices specified for backup or restore; Only 64 are allowed" .
I tried to attach mdf file, it too failed.
Can anyone help me in the procedure to restore 2005 backup in 2000?
View 4 Replies
View Related
Feb 9, 2007
hi there
i've made a backup from a MS SQL Server 2005 database and i tried to restore this database to a MS SQL Server 2000. Unfortunately, I got following error
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrad this database.
what can i do ? is it possible to restore the MS SQL Server 2005 DB on a MS SQL Server 2000 ?
Philipp
View 4 Replies
View Related
Aug 3, 2007
Hi,
Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.
Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.
However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon.
The temporary solution is to turn the index rebuilds off.
I have been working on a script that will:
Cycle through each database and within that database:
Go through each table
Run a DBCC DBREINDEX on the table
Move on to the next table
Once the reindexing of one database is complete
IF the database is not in simple mode
Backup the transaction log
Run a DBCC SHRINKDATABASE with the required amount of free space
Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits.
Now the script works fine excluding the shrinkdatabase, I always get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000]
Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000]
Where I'm indexing the INF_Prod database.
A DBCC LOGINFO shows something along the lines of:
Code Snippet
BACKUP LOG successfully processed 45162 pages in 152.607 seconds (2.424 MB/sec). [SQLSTATE 01000]
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 253952 8192 29618 0 128 0
2 253952 262144 29621 0 64 0
2 253952 516096 29620 0 64 0
2 278528 770048 29619 0 64 0
<cut cut>
2 9043968 370737152 0 0 0 29800000001528500007
2 9043968 379781120 29802 2 64 29800000001528500007
2 9043968 388825088 29801 0 64 29800000001528500007
Clearly there is something in the log file towards the end.
However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on.
I've tried doing Full backup + Transaction log + Shrink, it fails.
I've tried waiting 10minutes in the script + shrink, it also fails.
However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time.
However in the script it always fails no matter what I do.
Where am I going wrong here?
Regards,
Gareth
View 3 Replies
View Related
Oct 14, 2007
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
View 16 Replies
View Related
Sep 29, 2005
Of course I'll try this before I go live, but I figured I'd askhere first. I've read the docs, and the answer is unclear.My current backup strategy is:Take a full database backup occasionally (like, every two weeks).Back up logs every five minutes.This works, but restoring the database all the way through cantake a while, as I've sometimes spun through 800+ log restores.I'd like to change toTake a full database backup occasionally (like, every two weeks).Take a differential backup every day.Back up logs every five minutes.So, I'd have this set of backups after day twoFULLLOGS 1-100DIFFERENTIALLOGS 101-200When I go to restore, do I always have to doFULLDIFFERENTIALLOGS 101-200or can I doFULLLOGS 1-100LOGS 101-200?I guess what I'm really asking is: is there any reason to keepthe "old" logs around (in this case 1-100), if I assume I alwayshave the latest differential?I suppose I *might* need them to do a "point in time" restore,i.e.FULLLOGS 1-53.but assuming I never want to do that, can I discard the logsthat are earlier than my latest differential?
View 1 Replies
View Related
Aug 1, 2007
We are attempting to restore one of our databases from a backup that went to a local drive on the server.
We see the backupset in the list but receive an error that it is not available when we try to use it. When we try to restore from device and select the files the drive letter is not available. When we attempt to enter the path to the file it can't locate it. We don't have space on our SAN to copy the backup there and we can't add the local drive to the cluster resources.
Is there a way to get this backup done?
View 1 Replies
View Related
Jan 24, 2002
My DBA does a backup of the database and then it gets encrypted and compressed using PGP. This file is sent via ftp to an outside vendor. When they receive it, decrypt it and try to restore, they get the following error:
The media family on device c:dynaxoto1927131153 is incorrectly formed. Sql Server cannot process this media family”
We have tried the process in reverse where we have the vendor send us a file and it works fine.
Please provide some insight.
Thanks,
Robin Uffer
Technical Project Manager
robin.uffer@bankofamerica.com
View 1 Replies
View Related
Apr 8, 2004
Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
I can ftp to the webserver in question to download the backup file and upload a file which needs restoring but i need a web tool to carry out the operations.
Or if there is another method that would work please let me know.
Thanks
View 4 Replies
View Related
Apr 28, 2005
Hi all,
I want to restore My database from backup
restore database HKData from disk ='H:HKData.DAT'
go
i got error at
Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'HKData' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 8 Replies
View Related
Oct 28, 2004
hi all,
i just learned mssql server and need help form u guys the experts. i made a backup of database but i couldnt restore it into another machine.
the original database name is "employee" and running in mssql 7. the target database is named "employee" as well and running in mssql 2000.
it gave me this message:
Microsoft SQL-DMO (ODBC SQLState: 42000)
Cannot find file ID 2 on the device 'C:db_bakemployee.dat'.
RESTORE DATABASE is terminating abnormally.
i tried to restore it to another mssql 7 but it gave me the same error message. i dont know how to fix it. in the sql server log, nothing refers to the restore activity. please help....
thanx in advance,
tonjaw
View 1 Replies
View Related
Sep 21, 2005
I mean, how can I do that in code. I know how to do it in EM but this not what I need.
Thank you!
View 1 Replies
View Related
Apr 21, 2008
Hi Gurus,
I want to know(get ) more information regarding DATABASE BACKUPS & RESTORE in sqlserver 2005.
Can you pls post few sites regarding this.
Thanks in Advance.
Thanks,
ServerTeam
View 2 Replies
View Related
Oct 2, 2007
i want to create application for backup and restore my database.
example
yesterday i backup a database into disk name = fileA
Today i have something wrong with my currently database. I need to use fileA. what should i do?
Now i try 2 ways
1. use sp_detach_db + sp_attach_db but they still have problem cause fileA and currently database it's a same data because they update all file that use same database name
2. use backup and restore sql. i think, it should be better way but i don't know how to use it.
please give me some suggestions thank you for coming
View 4 Replies
View Related
Mar 21, 2007
Hi,
I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.
I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.
Database files become accessible for reading after I close the whole application.
I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.
I will be grateful for any help.
Tomasz
View 6 Replies
View Related
May 7, 1999
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
View 1 Replies
View Related
Jul 3, 2007
I am fairly new to VS 2005 and SQL Server CE. I have developed a Desktop Windows application using VB 2005 and the SQL Compact Edition Database. This application will sell to users via web download.
When they download a service patch, or updated version, I wish for them to retain the data in their present database. In other words, fill the new database with the old data. The new table structures will remain intact except for added columns at the end.
Question is how to save the old data and update the new database with it. Is there an easy way to do this, or do I need to write a module to save a database copy, and update the new database with content at install time?
View 6 Replies
View Related
May 19, 2000
I need to restore a database (that was backed up thru enterprise manager
and then burned to a CD) to a new computer that I just installed SQL
server 7.0 desktop version (NT workstation 4.0). I created a backup set
of a bogus database on the new computer, then copied the real database
from CD to that location. It won't let me restore it that way, does anyone
know how else to approach it? Thanks.
View 2 Replies
View Related
Feb 2, 2000
Hi all,
I want to transfer a database from one MS SQL Server 7.0 to another.
When I create a new database (SQL Server 7.0) and then try to load a backup, I always end up with a message that the backup database is not identical with the destination database. That is correct and I know it, so what!? Is there any other possibility to transfer a database other than Export/Import. Using this method took me about 48 hours for 6 GByte database!
All suggestions are welcome
Thank you
Karsten
View 3 Replies
View Related
Jun 9, 2004
I want to backup an SQL Server database through a client application written in VB 6.0, and not through the Enterprize Manager.
I use the SQLDMO Objects Library to backup the database to the SQL Server machine through my application,
but there are the following problems:
a. The backup can be done only locally to the SQL Server machine. Is there any way to bypass this restriction?
b. To get the Directories Structure of this machine i use the xp_FixedDrives, xp_Subdirs stored procedures.
That's ok for the Backup operation but i do not have a way to view the FILES to perform Restore operation.
Of cource VB function Dir() cannot be used to view files on the server because of access restrictions.
Is there any workaround for that?
View 2 Replies
View Related
Apr 22, 2006
Hi,
I took the backup of a database (called students) from one of the machine on our LAN and then tried to restore it on another machine but am unable to restore the database. Below are some details of the problem I am facing:
- The machine on which I am trying to restore the database (i.e. target box) previously had a database by the same name (no it was not a replica). I had deleted this database from the target box before trying to restore the database (of the same
name and which was backed-up from the source box)
- When trying to restore (via EM) I get a VERY LONG dialog box with lots of details. Below are some details of what this dialog box says:
The title of the database is MS SQL-DMO (ODBC SQL State: 42000) and It says "The physical name 'C:Program Files.....datastudents.mdf' may be incorrect. File 'students_Data' cannot be restored to 'C:Program Files.....datastudents.mdf'. Use with MOVE to identify a valid location for this file.
And the dialog box provides similar details related to 'students.ldf'
Can u please help me with this.
View 3 Replies
View Related
