at 9.00am -- full backup
at 9.15am - Transaction log backup
at 9.30am - Transaction log backup
Suppose at 9.20am(say) the user deletes a table and i want to restore that particular table,i will use STOPAT clause when im restoring the transaction backup at 9.30am (WITH RECOVERY).But my doubt is how to find out the exact time the user has deleted the table.I have even checked the SQLServer Error log file but it doesnt give the exact date and time stamp of the deletion activity tht has taken place so that i can use that in STOPAT clause of RESTORE LOG command.
Please tell me where should i look for date and timestamp for all the Deletions/Updations done on a particular database.
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92 Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92 RESTORE DATABASE is terminating abnormally. Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value. ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid This is all done in Page_Load event backend code. string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
I'm trying to create a standby server. Server 1 has all files locacted in the D drive, Server 2 will have the files located on the C drive. I've done a backup on server1 and moved the x.bak file to server 2. I then run the commandrestore filelistonlyfrom disk = 'C:Program FilesMicrosoft SQL ServerMSSQL$SQLSERVER03BACKUPHS_Webcalendar backup.BAK'and get the resultsHS_Webcalendar_dat D:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf D PRIMARY 2097152 35184372080640HS_Webcalendar_log D:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf L NULL 2097152 35184372080640When I run the restore commandrestore DATABASE HS_Webcalendarfrom disk = 'C:Program FilesMicrosoft SQL ServerMSSQL$SQLSERVER03BACKUPHS_Webcalendar backup.BAK'with move 'HS_Webcalendar_dat' to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf',move 'HS_Webcalendar_log' to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf', standby = 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_WebcalendarUndo.ldf'I'm getting the following errorServer: Msg 5105, Level 16, State 2, Line 1Device activation error. The physical file name 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf' may be incorrect.Server: Msg 3156, Level 16, State 1, Line 1File 'HS_Webcalendar_dat' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf'. Use WITH MOVE to identify a valid location for the file.Server: Msg 5105, Level 16, State 1, Line 1Device activation error. The physical file name 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf' may be incorrect.Server: Msg 3156, Level 16, State 1, Line 1File 'HS_Webcalendar_log' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf'. Use WITH MOVE to identify a valid location for the file.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.
I have inherited a project from a co-worker who has had a family tradegy, and I am trying to get up to speed with her project.
We have an SQL server 7 database which is getting backed up every night (by SQL itself) into a .BAK file. I need to know if it is possible to restore this file from the command line (a DOS prompt.) I know (or think I know) how to do it from within SQL Enterprise Manager, however the specific needs of this project require it be done from a command line on the machine.
I really am not an SQL guru...I don't even think I qualify as "knowing what I am doing" at all, so any advice you can offer will be greatly appreciated. This is kind og urgent, so your thoughts are welcome!
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
select [--Restore--]= case when a.Seq = 1 then @cr+ @cr+'restore database '+c.DatabaseName+ @cr+'from disk ='+@cr+@tab+''''+ @backup_path+''''+@cr+'with'+@cr else '' end+ @tab+'move '''+a.LogicalName+ '''to '''+a.PhysicalName+''' ,'+ case when a.Seq = b.Seq then @cr+@tab+'replace, stats = 5 , recovery' else '' end from #filelist a cross join ( select Seq = max(b1.Seq) from #filelist b1 ) b cross join ( select DatabaseName = max(c1.DatabaseName) from #header c1 ) c order by a.Seq go drop table #header drop table #filelist
I'm very new to SSIS and I€™m trying to do the following in a SQL task
RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10
I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?
I have seen this before. A 2000 restore fails, leaving the database thinking it is being restored but the restore job failed and errors when it is restarted. EM is clueless. I believe there is a proc to reset some flag. Can you share it with me???
Trying to keep out sysadmins & sa during/between database RESTORE
Configuration: WINNT Server Enterprise 4.0 w/SP5 SQL Server 7 Enterprise & SP1
2 SQL Servers: Production Server Standby server
I Backup (full backup) databases to disk on primary server (logical backup devices are physicaly located on a Standby server (dedicated gigabit NIC in each server for this process). Transaction logs are applied to the Standby server throughout the day.
Problem: How to keep out "sa" and sysadmins from a database while I'm restoring (or between restores) to a standby server? The database being restored cannot be in use during a restore. If a DBA forgets that this process is happening, the statement fails (RESTORE)for the database they happen to be in at the time of the restore.
Example restore statement: Standby Server - RESTORE DATABASE databasename FROM database_dd WITH DBO_ONLY, REPLACE, STANDBY = 'g:Mssql7FromPrimaryDatabaseName_undo.ldf'
I could restrict Domain sysadmin access and change sa password. I could also put the database in "Single user" mode, however this could become problem if my process disconnects and then someone else connects - then my process is locked out. What I'm really looking for is to lock out all activity for a database that is in "standby mode" except for RESTORE processes.
I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.
why it is bringing the database into single user mode ?
Hello,i am in great trouble. I want to revert back to original state ofdatabase before i performed restore database on my sql server 2KDatabase. Accidently i didn't take backup of my Database and i didrestore, so is there any way to get the original state back of myDatabase?Any suggestion will be highly appriciated.Regards,S. Domadia.
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.
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?
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.
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
I was wondering how I could restore a bkp file into a database with a different name. For example, I made a backup of T1.mdf and i want to restore t1.bkp to T2.mdf.
I have a client that has POS software called Restaurant Pro Express (RPE) from [URL] ...
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 am no DBA expert, especially when it comes to MSSQL. Is is possible to 'restore' the database from the 'raw' .mdf and .ldf files of the old computer to the new computer / database instance? If so, how should I proceed?
Still having a problem with my SQL WHERE clause…
I have three variables on an Excel form that connects to a Teradata database. The first variable is a date format (DateworkedF and DateworkedT) the other two are text fields. (StatusX and ErrorTypeX)
I want to be able to search on any or all of these fields. (If the field is blank return all values)
I have used this query statement with a SQL Server 2005 database and need to use something similar with an Access database:
SELECT products.*, Category AS Expr1 FROM products WHERE (Category = @Category)
When I test this in a table adapter there is no preview due to lack of parameter. I seem to recall that Access uses different syntax in the WHERE filter clause (i.e., not @). Can someone help me out with this?
What does the shrink command exactly? I start the shrink command in the Enterprise Manager of SQL7/2000. Does it include re-indexing of all tables? And is it the same command as auto-shrink can be selected in the db properties?
I'm attempting to alter a database by removing one of two log files. I have truncated both log files successfully and the database has no processes but I get an error stating file cannot be removed because it is not empty. Help
create from dbo.fromExampleasfrom dbo.Customers cjoin dbo.Invoices ion c.CustomerID = i.CustomerIDnow you can use it as follows:select c.CustomerID, i.InvoiceNofrom dbo.fromExampleI find I reuse FROM clauses alotand reusing a "base query" requires writing a very large and cumbersomeselect clause
Is there a way by which we can execute the backing up of database from the command prompt......
Suppose i need to do the below task. I can write a stored procedure and schedule it. But the situation demands to run from the command prompt. Is there a way out.
-- Create the backup device for the full MyNwind backup.
I am in the process of creating a windows installer. I am using SQL Server 2005 for my application. I have generated the script for my database. I want to run that script and create my database on the client machine (client has installed SQL 2005). Do you have any solution for this kind of issue?
When run an installer I can run a bat file. Can I create a bat file and create my database? Does SQL Server2005 provide any tool for this?
Commonly, to upgrade a database numerous scripts need to be applied. These scripts make both schema and data changes. Often, the data changes need to be interleaved with schema changes (for example, it is not possible to create a unique index until the column has been populated with unique values). The scripts will contain numerous GO commands as required for the definition of stored procedures, etc.
I am wondering what is the best way to do error handling with such scripts. I use SET XACT_ABORT on at the beginning, but when one batch fails, it just skips on to the next GO command. And of course, it rolls back my transaction having the side effect of running the subsequent batch without a transaction. Is there any way to avoid this? I would like a solution that would let me splice together scripts that I get from various different developers (and in some cases automated tools) with minimal difficulty even though the coding styles may be a little different.
The possible solution that comes to mind is RAISERROR with a severity greater than or equal to 20. I'm not thrilled that I have to use WITH LOG on this command. I tried the KILL command, but that won't let me terminate my own SPID.
I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.
The same package works fine when the target is on Oracle database or a SQL Server database.
For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.
Any suggestion regarding this would be really helpful.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Label3.Text = command4.Parameters["@UserName"].Value.ToString(); }Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 48: Line 49: conn.Open(); Line 50: command4.ExecuteReader(); Line 51: conn.Close(); and here is my stored procedure ALTER PROCEDURE dbo.myStoredProcedure --
(@ProductID int, @UserName varchar(255) OUTPUT)
AS
SET @UserName = (SELECT UserId FROM Products WHERE ProductID = @ProductID) what is wrong? i never seen that error and i don't know from where could it came from please help me, thanks
