Restore MSSQL BAK File By TSQL
Jun 16, 2007Hi,
I need an application with asp.net that let me restore database by getting .bak file and database name and database user and user password, my database is SQL Server 2005.
ADVERTISEMENT
Restore MSSQL Database, Lost LDF-File
Oct 4, 2005Hi.
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?
PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:DEVLOG1DEVLOG1.ldf' may be incorrect.
Thanks in advance for assistance.
Regards,
Carsten Loeffler
Is There A Build In FTP In MSSQL 2005 That Can Be Invoked With Tsql?
Jan 16, 2008Hi. I was wondering if there is such thing in MSSQL 2005? Not the one in SSIS.
Any feedback will be greatly appreciated.
Thanks,
Rick..
TSQL Restore Script Help
Mar 6, 2002Hello all,
I have want to restore a database from an other server to an other with SQL 7
I was trying a small test script sofar but I got this error.
Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013)
Can somebody help me with this.
Or is somebody owner of a very handy TSQL restore script.
Thanx.
Casper.
Back And Restore MSSQL
Mar 14, 2008How could i back and restore my MSSQL using a programmatic way throw asp.net. Also Can i use do an incremental back. Regards,IseL
View 7 Replies View RelatedRestore Dabatase MSSQL
Sep 15, 2004Dear all I need your help in order to restore a database.
I did a backup of one SQL database hosted on my web service, and after I copy the file on my C:Wutemp disk.
I'd like to import this database on my MSSQL installed on my local pc in order to have the same web site in local.
I try to restore it with enterprise manager (..using Restore) but I receive a lot of error message:
(I translate from Italian so..be patient..)
The filename 'C:Program FilesMicrosoft SQL ServerMSSQLDatamioDB_DATA.mdf' could be not correct.
Impossible to restore the file 'miodb_Data' in 'C:Program FilesMicorosft SQL ServerMSSQLDatamioDB_DATA.mdf'. Use WITH MOVE in oreder to identify a valid location for the file
The filename 'C:Program FilesMicrosoft SQL ServerMSSQLDatamioDB_Log.LDF'could be not correct
Impossible to restore the file 'miodb_Log' in 'C:Program FilesMicorosft SQL ServerMSSQLDatamioDB_Log.ldf'.
Any suggestions?
How Do I Clear Out Available Restore Points For MSSQL Db's?
Aug 14, 2006I was messing around with backup strategies today at work while contemplating my other db issues.
In doing so, I've made a list of backups that don't truly exist anymore (I deleted the 'backup device' (bleh.bak).
Here's an image, stolen from technet's articles.
http://www.microsoft.com/library/media/1033/technet/images/prodtechnol/sql/2000/maintain/ppc1106_big.gif
See that list of restore points? Mine is full of restore points that have absolutely no meaning in them for a DB I made to test out (testDB).
Is there a way to clean those out?
TSQL Help; Need A Header File In The Top Of A Flat File
Aug 16, 2007I have a tsql script that gets the data I need, into the format I need, and saves it in a format (.output) I needI also have a script that creates a header for the report, basically its just a name and rowcount() that also works fine.PROBLEM: If I combine them using UNION, I have to pad out the header report with NULL columns, and it messes up the layout of the report.Anyone have a simple way to do this?here's my code:SELECT 'A71310000'+ltrim(Str(count(UserName))) + 'HRBATCH' AS header, NULL as col2, NULL as col3, NULL as col4, NULL as col5, NULL as col6, NULL as col7, NULL as col8FROM db_owner.PS_HR_HrsWHERE Reported is NULLUNION ALLSELECT EmplID, Convert(VarChar,DateWorked,111),'STSSH', CAST(REPLACE(STR(HoursWorked,9, 5), SPACE(1), '0') AS nchar(9)), HRAccountCode, CAST(REPLACE(STR(EmployeePayRate,18, 6), SPACE(1), '0') AS nchar(18)), 'A_STUDSUM', HRAccountCodeOverrideFROM db_owner.PS_HR_HrsWHERE reported is NULL What I need it to look like is: A713100007HRBATCH
10068800
2007/06/04STSSH012.00000 A108145 00000000007.500000 A_STUDSUM
10068800
2007/06/05STSSH002.00000 A108145 00000000007.500000 A_STUDSUM ...(this is a ragid right with spaces padding out fixed width columns) THANKS for ANY light ANYONE can shed on this.
Mssql 2000 Kill All User Connection For Restore
Apr 22, 2004i need a sql statment or procedure to kill all user connection to one database.
And Deny new connection for a little time.
So i can a restore this database from a dump.
I can this do only in exclusiv modus. without any other connection.
Can anybody help me??
thanks and best regards from Berlin, Germany
TSQL Text File Output
Aug 24, 2006How do I output a table as a txt file using tsql?
View 9 Replies View RelatedTSQL Checks File Existence?
Sep 3, 2007Hi,
Does TSQL provide methods to check if a file exists?
For example, a TSQL script will read data in a .dbf file into SQL Server. It will check if the file exists before read. How to do this check?
Thank you.
Is There A TSQL Statement To Delete File Like Xp_delete_file For SQL2000
Jun 29, 2006I know there is a command xp_delete_file for SQL 2005 but if I want to delete a file from a 2000 database is there a command to do the same.
View 4 Replies View RelatedDB Engine :: How To Restore From Current LDF Log File And Old BAK Backup File
Jun 11, 2015My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deleted
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command
BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak'
WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
MSSQL To Text File?? How?? Thanks...
Dec 26, 2006As we know, MySQL have function to output its data into Text File using "Select * into outfile 'C:/mytext.txt".
Does MSSQL has "Select into Outfile" function??? If yes, what is the function??
Thanks in advance :)
Anderson
Retrieve XML File From MSSQL
Oct 13, 2007Hi Everyone,How to retrieve a XML file from MSSQL using a stored procedure in asp.net ?Thanks,may
View 1 Replies View RelatedExport MSSQL Db To Csv File?
Oct 19, 2007Hello, I am using Visual Studio Web Developer 2005 Express Edition with MSSQL 2005. I need to import my db into the db in Godaddy.com web server. How do you know export MSSQL db in VSWD to csv file? I was trying Teratrax. But I am not sure how to browse for the database on my local machine.My local machine runs on XP Home Edition and I write my ASP.NET app with Visual Studio Web Developer Express Edition. How do I find out what my server name is so that I can connect to the database of my application? I don't think it is localhost because I don't have IIS running on my machine. From my understand VSWD has its own web server. But I am not sure how to connect to my data baswe so that I can export the database to CSV file using Teratrax.ThanksJacthemanus
View 4 Replies View RelatedHow MSSQL DB Export To *.csv File?
May 15, 2008Hi
How can I export a MSSQL DB (table, SP, View, data) to a single csv file? I can export them to a sql file through Publishing Wizard, but how can I export it to a csv file?
Thanks for help
Scheduling A .vbs File Under MSSQL 7.0
Oct 12, 2001Hi there,
I wanted to know if there is a possiblity of running a scheduled VBScript file under MSSQL 7.0 using the - Management->Sql Server Agent->Jobs.
When I create a Step, there are these given options:
1. Active Script:
I understand that the configuration would allow VBScript or Java Script or Others.... but the command could only be as long as 3200 characters. My script is longer than the mentioned limit hence I will have to use an external .vbs file.
I don't know how the third option of others to be used.
2.Operating System Command(CmdExec):
I can only use .BAT, .CMD, .EXE, .COM extentions in this. Again, I would have to convert my .VBS file to a .EXE and then schedule it. It has a lot of overheads to it, which I consider is not apt.
Could anyone suggest me how to go about scheduling a .vbs file on MSSQL 7.0 with set frequency.
Regards,
Raj
MSSQL 7 Log File Resizing
Jul 14, 2000Here's my question, hopefully one of you can help me:
When you allow your log file to grow on its own, eventually it uses up all your diskspace . . so you truncate it. However, when you truncate it, it doesn't *resize* the log file, so your still out of disk space, it simply clears up room within the allocated "block" or what-not so that the db can continue to make log file entries.
So, does anyone know of a way to resize that log file without losing it's data? For example, we were thinking about truncating it, backing it up, dropping the original, and then restore the log file from the "truncated backup." Anyways, there must be an easier solution (if that one even works!) . . so please, if you have any hints, let me know.
Thanks,
David P.
Import .sql File Into Mssql
Aug 25, 2004I have backup.sql created by mysql. How can I let mssql server to import such file?
View 3 Replies View RelatedMSSQL Database File Question
Apr 29, 2005Hi.. When I go to my MSSQL 2000 database-->
properties-->Databse size is 1500MG and the space
available is 305MB
But I don't understand how space available come from.
As I had put my Database file and Transaction log file
as unrestricted file grow. And put automatically file
grow by 10% for both file and log
Why is the limit come from ?
Besides, I have a database which indicate space
available as 0M in the database properties but it is
still functioning. why?
MSSQL Log File Is 210GB And Growing
Oct 11, 2007
Hello, we are running Microsoft SQL 2005 Express edition (9.0.32).
Recently I just noticed that the database log file of our main database is HUGE. The database data file is only 50MB and the log file is 210GB.
Any idea what is causing this? Seems to be getting bigger with time, in the last 7 days seems to have grown by 100GB. I noticed the following settings under the database:
Autogrowth: By 15 percent, unrestricted growth
Does that seem right? Thanks.
Import Text File Into Mssql
Dec 23, 2005the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in text
no space, no symbol, no line break between each record
i must use the length of char to define it.
Please help me how to import into mssql
FILE DSN On UNIX To Connect To MSSQL
Sep 19, 2007Hello experts,
I need to create a File DSN on AIX Unix server which can connect to MS SQL server 2005 on a windows machine.Can anyone please give me step by step procedure how to do it.Its an urgent requirement. Please help me.
Regards
Arvind L
How To Display File/image From Mssql Server?
Feb 4, 2005i've some records in the mssql server which are actually files or images. They're stored in either binary fields or image fields.
thus how do i display these files out? or let people download them???how do i write such aspx? thanks
[mssql] I/O Error While Reading BCP Format File
Feb 29, 2000Hi All,
I had a problem today where BCP would not read the format file I created giving the following error:
I/O error while reading BCP format file.
I searched the archives here at swynk.com and found a thread titled exactly the same as the one I am now creating. There were numerous responses to this thread but none of them seemed to solve the problem for me (short of the one that suggested using BCP to create the format file, which I didn't want to do).
I used BCP to create a format file for me (which looked identical to the format file that I created) and it worked fine. With my suspicions aroused, I used a binary file viewer to look at the contents of each file and highlight the differences. Apart from the usual variations in whitespace I noticed that the my file did not have any carriage returnline feed at the end of the last row definition.
My format file ended at the end of the last row definition line. It did not have any carriage returns and line feeds. I matched the one generated by BCP and added the following "
" to the end of the file, tested my format file and it worked perfectly.
The moral of the story is, make sure when you create a format file, you have an empty line at the end of the file (ie: after your last "Server Column Name", make sure you have "
" sans quotes).
Hope this saves someone a few hours :-)
Cheers,
Dan.
MSSQL DB File Getting Bigger, Can Split Or Reduce?
Aug 30, 2006Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation?
i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?
Can MSSQL Load A Tab Delimited Text File?
Jul 23, 2005Hi all...I would like to know if SQL SERVER can load a tab delimited text file.If yes, how?A search on the web did not return me the "load data" command as mysqlor other.Thank you all.
View 5 Replies View RelatedAttaching A .mdf File To The MSSQL 2000 Using Commandline
Mar 30, 2007Hi ,
we have a requirement to create a database of our application in client's system. and we assume that already MS Sql server is installed.
we have a .mdf file of our database. Is there any way I can attach .mdf file to the database using command line. so that I can write a batch file and run that batch to create the database in our client's system.
-regards
GRK
Can MsSQL Data File Be Archived And Made Smaller ?
Dec 6, 2006Hi... We have application that connected to MSSQL 2000 database. The database file is getting bigger and bigger over the years. Recently the performance of the database and application is getting slower and slower, my senior oracle DBA told me that I should archive the MSSQL 2000 data file and export the old record to the archive DB. So that the number of record will be less and it will be faster.
I would like to know whether MSSQL 2000 support archiving of the database file ? If yes, what is the way to do it ? I could not find it in Enterprise manager option at all.
How To Import Text File(no Space, No Symbol, No Column) Into Mssql
Dec 24, 2005 the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
Restore File
Sep 20, 2000Database file or filegroup method was used to perform the backup and now i tried to restore that into different server by using SQL Server Enterprise Manager.
I got this error message:
"The database you are attempting to restore was backed up under a diffrent sort order ID (54) than the one currently running on this server (52), and at least one of them is a non-binary sort order. Backup or restore operation terminating abnormally."
Why i got this error message? And what is "backed up under a diffrent sort order ID (54)" ?
Please anyone help me
Restore File
Sep 27, 2000Hi
My backup file is BAK FILE and it's in D:MSSQL7/Backup/Sales.BAK.
I want to restore that into a different server in D Drive. For that i created a database called "RestoreB". (RestoreD_Data.MDF and RestoreB_Log.LDF). I tried to restore using Enterprise manager, but i got error message.
"File 'Sales_BAK' is not a database file for database 'RestoreB'. Backup or restore operation terminating abnormally'
How do write the statement to restore that file?
Anyone can help me to restore that file...
Thanks in advance