I don't know where to post this kind of stuff so here goes...
I have maintenance plans which sometimes fail because the delete step reports that the old backup file is "in use." I have no idea how to determine what Windows thinks is holding the file. HOw do you determine who is holding a file hostage??
Thanks.
Here is my code
ALTER PROCEDURE Test
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @From varchar(10)
DECLARE @To varchar(10)
DECLARE @DBName varchar
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo
WHERE TicketNum =
(SELECT TOP 1 TicketNum FROM CustomerInfo
WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP)
ORDER BY CreateDate DESC)
SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp'
CREATE DATABASE @DBName + ' ' + @From + ' ' + @To
END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
Thanks
Ganesh
I've done some basic database design in the past, but am a relative newbie to design. I have recently come across a situation I'm not sure how to handle. Here's the situation...
Assume we've got a contacts table which holds information about our contacts. All contacts have the same basic information - name, address, telephone, etc. Each contact is of a certain type - let's just say a, b, and c, for ease. This contact type is stored in the contacts table. Now, on contacts of type b, I also have to store some additional data. What it is doesn't really matter. I found a way to set this up, but I'm not sure that I'm going about it the right way, and would love some advice on the proper way to do this. Basically, what I did is create my contacts table:
Contact_id, contactName, ContactAddress, ContactPhone, ContactType.
Created a contacttype table
ContactType, ContactTypeDescription, ContactAddInfo
What I've done is left contactaddInfo as a nullable field. When it has a value, that value is the name of a table which holds the additional information that I need for the contact... So when I'm running a query, I can tell if I need to reference another table by checking the value of ContactAddInfo.
I can't imagine that this is the best solution, but it was the first thing that popped into my head, and it's a really small database that's using it. However, I'm now being faced with the same situation in a much more important, larger database, and I'd love to know the 'right' way.
Thanks for any guidance you can provide!
Erin
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.
Hi all,
My question is:
If I need to restore a database when all I have is the backup file, do I need to recreate all the tables, or is it possible to restore into a new database ?
Is it possible to restore a sql server 2000 database into a sql server 2005 database ?
Thanks in advance.
This is a pretty basic question, but I've generated a backup file for one of my SQL 2000 databases, and I need to copy the backup file to another computer so I can burn it on CD. Whenever I try to copy the file, I get an error message saying that the file is in use by another process and can't be copied. I have tried detaching the database, shutting down Enterprise Manager, stopping the MSSQL service, but all to no avail. There must be a way to do this, but I can't get it to work. Does anyone have any ideas?
View 3 Replies View Relatedhello All !!
i have taken my backup(back file ) of database and i stored in folder , now i want to know
1)if i format my Hard Disk . and reinstall Sql server 2000 ?
2) if i delete that Database ?
how can i REGENERATE my database ?? again
thnk u all
How does backup database command works? I don't see size of database backup file increasing while backup is in progress OR is it locked till the backup is finished.
Thanks.
Hi all,
I have a ploblem...
i have ful backup file, diffrential backup file, transaction log backup file.
i want to create a new database form backup file.
i do it follow :
Using Enterprise manager
1. Rrestore database from full backup file. it is ok.
2. affter that i restore different database differential backup file. it does not work, the error is :' Theproceding operationdid not specify WITH NORECOVERY or WITH STANDBY. Resatr the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. RESTORE DATABASE is terminating abnormally'
please show me,
nest regard.
Hi
I have copied a sql server backup file. I would like to make it a live database. How would I do this??
Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.
View 2 Replies View RelatedHello!
I am trying to find out name of the backup file database was restored from (I am restoring from DISK), something similar to physical_device_name in backupmediafamily. restorehistory doesn't have this information.
Any advice is greatly appreciated.
Thanks,
Igor
hi
is it possible to create a database using backup in sql server DE??????????
like we can do in full edition using restore option........
Hello,
i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.
Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?
I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.
This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..
Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?
Is this enough to have a valid backup? Backup would be an attach of the .mdf file.
Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?
Regards,
Markus
Hello,All:
I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.
Now I want to import the backup data(generated by production server),I don't know how to do it.
Is it possible to do it ? anyone can give me a soluation ?
thanks in advanced!
Hello,
I have a user database which has 1 data file and 1 logfile.
I did a complete backup of the database to a file on the disk drive, using Enterprise manager. Due to some reason, I had to drop the database. The only way I could restore it was by,
1. Create the database with the same name.
2. Restore the complete backup from the file device.
While doing so,
I get the message "Backup set holds the backup of daatabase other than the existing 'userdbname' database. BAckup terminating."
Is it because the database was dropped and recreated.
If I choose the option to overwrite, it restores successfully.
Is this normal and right way to do it? Is there any thing else that I need to take care of, while backup or restore?
Any suggestions welcome.
Thanks,
MMS
finding the database size from the backup file.I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?
One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?
I know this is easy but I am having trouble creating a database from a backup file.
I created a database (Test).
Then I selected 'Test', went to Tasks and selected 'Restore Database'.
I selected 'Device' browsed to the backup file.
I choose the destination database to be 'Test'.
There is no backup sets listed to restore. The 'OK' button is disabled.
How do you get the backup list to display? This is a new database. I don't need a backup.
I need to create a new db from a backup file of an allready existing database (vehicledb) on this server.So i created a new database with name "vehicledb2" .But when i try this, the backup says that this file is used...The backup set holds a backup of a database other than the existing 'Vehicles2' database. Restore of database 'Vehicledb' failed.
View 9 Replies View RelatedI have accidentally taken the backup of a database twice into same .bak file. Now the file is twice the size. Will it be fine if I restore this backup? Or will I screw up any data?
View 1 Replies View Relatedhi
can you posible one tabel restore in database using full backfile
thanks
hi
i have restore database use backup file in another machine of same name of database there following error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'database name'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'dbname' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Hi
I'm migrating from access to sql server 2005.
With access, I did full backups every 6 hours, by simply copying the .mdb file to a folder on another hard disk on the server, and then ftp it to a distant server.
I guess i just can't simply do the same with the SQL server .mdf file.
What is the best practice to achieve the same goal ?
Thank you
I am confused regarding why the default behavior of this option is to use file no 1 if the option is not specified? If I take two backups on different days, weeks, months, etc. and write them to the same Backup set, the second and most recent backup by the way gets the higher file number (position) as one would expect. What is incomprehensible to me is why the decision was made to restore the oldest backup from the backup set if you do not specify with file no? Seems like common sense that I would NEVER want to restore the oldest backup by default and would most-likely always want to restore the most recent backup by default!
If anyone has suggestions on how I could suggest that the behavior of this option be changed, I would appreciate it, as this has caused me much pain.
Tim
One of our clients gets the following error when trying to backup a database:
View 6 Replies View RelatedHi everybody,
On executing the RESTORE command of SQL Server to restore from a backup of 78.3 MB, the "Server Application Unavailable" error message comes up.The error message in the Application log is as follows:aspnet_wp.exe (PID: 2184) was recycled because memory consumption exceeded the 152 MB (60 percent of available RAM).
However using Query Analyser of SQL Server I am able to restore the database.
What is the solution to this problem?
I can run this example from SQL Book Online from sql query analyzer. I can build the TestDB database.
BACKUP DATABASE Northwind
TO DISK = 'c:Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:Northwind.bak'
WITH MOVE 'Northwind' TO 'c: est estdb.mdf',
MOVE 'Northwind_log' TO 'c: est estdb.ldf'
GO
But... When I build stored procedure and call it through VB6. I've got the gray database symbol along with message TestDB (Loading/Suspecting)
Why I cannot run these commands through VB6
I created a dummy database (TomsTest) and then I try to restore my other database "DEV" to it.Normally, you have to use the Move for both the mdf and ldf files to tell it to restore to another database.But the message I am getting is: The file 'D:SQLDataTomsTest.mdf' cannot be overwritten. It is being used by database 'TomsTest'.
USE [master]
RESTORE DATABASE [DEV] FROM DISK = N'D:SQLBackupDEV_backup_2014_05_28.bak' WITH FILE = 1,
MOVE N'Dev' TO N'D:SQLDataTomsTest.mdf',
MOVE N'DEV_log' TO N'D:SQLDataTomsTest_log.ldf', NOUNLOAD, REPLACE, STATS = 5
Of course it is in use, that's always the case. Even if I put it in single use mode, it still doesn't work.I tried it on another machine and had the same problem. Not sure why. If I call it the same name, it works fine but I don't want to overwrite the original database, I just need to get some data from the backup.
I €˜d like to discus with you the following REAL enough disaster scenario:
1. The TEST database is in the FULL backup mode.
2. WE have a full TEST DB backup and all tr. log backups.
3. DBA moved TEST database OFFLINE for maintenance operations.
4. MDF file for TEST DB was removed by mistake.
5. TRANSACTION LOG (LDF) file is OK.
6. DBA want to recover database to the point of failure.
According with MS SQL Server 2000 documentation it is possible.
We need to backup the transaction log , BUT I CANNOT DO THIS.
PLEASE, HELP.
In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
I want to read certain data from the backup file of MS SQL 2000 without restoring the database backup file. Based on the data selected I will manage the further functionality such as restoration of the backup file.
Is it possible to read some data from MS SQL 2000 backup file without restoring? If yes then please let me know how?
the idea is to put together a folder that is in some place in the computer, like "c:excel_fles" and the database full backup in a single .bak file, the database can be recovered with sql server management studio(but no the folder), the restoration of the program can be done with a program, I think it can be done with msdb, but I'm a little lost in the road.
View 4 Replies View Related
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
hi
i have a database file backup which is having no extension (eg saims) . Can i create a database using this backup in sql server expression edition.
Or else is there any way to get the .mdf file from sql server 2005 full edition??????????????
Thanx in advance