STOPAT Clause In Restore Database Command
Aug 30, 2007
Hi,
I have my backup scheduled as shown below
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.
Thanks in advance.
Regards
Arvind L
View 5 Replies
ADVERTISEMENT
Jul 22, 2014
What the BUFFERCOUNT option does in a RESTORE DATABASE command?
View 5 Replies
View Related
Jun 17, 2015
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?
View 8 Replies
View Related
Apr 19, 2007
All,
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";
View 2 Replies
View Related
Jul 23, 2005
I want to restore a backup copy of a database from the command line. Canthis be done?Thanks, Tom.
View 2 Replies
View Related
Sep 2, 2005
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.
View 3 Replies
View Related
Jul 10, 2002
Hey Folks,
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!
Thanks!
Mike
View 1 Replies
View Related
Sep 7, 2006
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
set nocount on
go
declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
create table #header (
BackupNamenvarchar(128)null,
BackupDescriptionnvarchar(128)null,
BackupTypeintnot null,
ExpirationDatedatetimenull,
Compressedintnot null,
Positionintnot null,
DeviceTypeintnot null,
UserNamenvarchar(128)not null,
ServerNamenvarchar(128)not null,
DatabaseNamenvarchar(128)not null,
DatabaseVersionintnot null,
DatabaseCreationDatedatetimenot null,
BackupSizedecimal(28,0)not null,
FirstLsndecimal(28,0)not null,
LastLsndecimal(28,0)not null,
CheckpointLsndecimal(28,0)not null,
DatabaseBackupLsndecimal(28,0)not null,
BackupStartDatedatetimenot null,
BackupFinishDatedatetimenot null,
SortOrderintnot null,
CodePageintnot null,
UnicodeLocaleIdintnot null,
UnicodeComparisonStyleintnot null,
CompatibilityLevelintnot null,
SoftwareVendorIdintnull,
SoftwareVersionMajorintnull,
SoftwareVersionMinorintnull,
SoftwareVersionBuildintnull,
MachineNamenvarchar(128)not null,
Flagsintnull,
BindingIDuniqueidentifier null,
RecoveryForkIDuniqueidentifier null,
Collationnvarchar(128)null,
Seqintnot null
identity(1,1),
)
create table #filelist (
LogicalNamenvarchar(128)not null,
PhysicalNamenvarchar(128)not null,
Typenvarchar(10)not null,
FileGroupNamenvarchar(128)null,
Sizedecimal(28,0)not null,
MaxSizedecimal(28,0)not null,
Seqintnot null
identity(1,1),
)
insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')
insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')
declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)
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
Results, modify as needed:
--Restore--
--------------------------------------------------------------------
restore database MY_DB
from disk =
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
with
move 'PRIMARY'to 'D:MSSQLDATAMY_DB_PRIMARY.MDF' ,
move 'DATA1'to 'D:MSSQLDATAMY_DB_DATA1.NDF' ,
move 'DATA2'to 'D:MSSQLDATAMY_DB_DATA2.NDF' ,
move 'LOG'to 'D:MSSQLDATAMY_DB_LOG.LDF' ,
replace, stats = 5 , recovery
CODO ERGO SUM
View 4 Replies
View Related
Mar 22, 2007
Hi,
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?
Many thanks
Martin
View 4 Replies
View Related
Apr 27, 2007
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???
Thanks!
View 4 Replies
View Related
Nov 19, 1999
11/19
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.
Any ideas??
Wade
wadej@vailresorts.com
View 1 Replies
View Related
May 9, 2012
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 ?
View 1 Replies
View Related
Nov 15, 2006
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.
View 2 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
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
Jul 29, 2014
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.
View 8 Replies
View Related
Apr 26, 2007
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.
View 3 Replies
View Related
Aug 10, 2007
Hi,
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 am using VB.Net to do the backup and restore.
Is there any way of doing it?
Thanks!
View 21 Replies
View Related
Aug 19, 2015
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?
View 3 Replies
View Related
Jul 29, 2013
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)
Query = "SEL SRN_ACCT_NUM, QUEUE_NAME, ERROR_TYPE, SUB_ERROR_TYPE, DATE_WORKED, MONTH_WORKED, DATE_APPLICATION_RECEIVED, ASSOC_WORKED, ACCT_ID, STATUS, COMMENTS, REVIEWED_IND, REVIEWED_AGENT, LOAD_DT " & _
"FROM UD402.JD_MCP_MASTER WHERE " & _
"(DATE_WORKED >= #" & DateworkedF & "# Or #" & DateworkedF & "# IS NULL)" & _
"AND (DATE_WORKED <= #" & DateworkedT & "# Or #" & DateworkedT & "# IS NULL)" & _
"AND (STATUS = '" & StatusX & "' OR '" & StatusX & "' IS NULL)" & _
"AND (ERROR_TYPE = '" & ErrorTypeX & "' or '" & ErrorTypeX & "' IS NULL);"
View 5 Replies
View Related
Oct 17, 2006
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?
View 3 Replies
View Related
Mar 13, 2003
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?
Thanks for your info
mipo
View 7 Replies
View Related
Apr 4, 2001
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
View 2 Replies
View Related
Jan 7, 2007
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
View 6 Replies
View Related
Sep 13, 2000
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.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:mssql7ackupMyNwind_2.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2
-Rajesh
View 2 Replies
View Related
Jan 7, 2001
How to import database in command prompt?
View 1 Replies
View Related
Aug 22, 2007
sqlplus for Oracle has a CREATE DATABASE LINK command. What is the equivalent functionality for MSSQL using sqlcmd or other script mechanism.
View 1 Replies
View Related
Nov 14, 2007
Hi,
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?
Thanks!
CJ
View 3 Replies
View Related
May 19, 2008
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.
What are the preferred ways of handling this?
View 5 Replies
View Related
May 16, 2007
Hi,
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.
Thanks,
Manish
View 2 Replies
View Related
Nov 4, 2015
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?
View 13 Replies
View Related
Dec 28, 2007
hello
here is my code int a = Convert.ToInt32(Request.QueryString["ProductID"]);
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand command4 = new SqlCommand("myStoredProcedure", conn);
command4.CommandType = CommandType.StoredProcedure;
DbParameter param2 = command4.CreateParameter();
param2.ParameterName = "@ProductID";
param2.DbType = DbType.Int32;
param2.Value = a;
command4.Parameters.Add(param2);
param2 = command4.CreateParameter();
param2.Direction = ParameterDirection.Output;
param2.ParameterName = "@UserName";
param2.DbType = DbType.String;
command4.Parameters.Add(param2);
conn.Open();
command4.ExecuteReader();
conn.Close();
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
View 2 Replies
View Related
