Category Archives: Backup/Restore
Basically restore process is completed successfully and you can see database name in list and its files on required location but in inaccessible mode.
To avoid this problem, first, one must verify original logical names of files by using following RESTORE FILELISTONLY .
RESTORE DATABASE [TraceDB2]
FROM DISK = N’D:\temp.bak’ WITH
MOVE ‘TraceDB’ TO ‘d:\TraceDB2’,
MOVE ‘TraceDB_logw’ TO ‘d:\TraceDB2_log’,
FILE = 4, NOUNLOAD, REPLACE, STATS = 10
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\ MSSQLServer\BackupDirectory”
- Restore dialog box is now divided into three tabs instead of two. General tab is almost same to existing versions but options tab is further divided into “options” and “files” tabs.
- A good thing about new restore page is “Backup Timeline”. Backup Timeline dialog box is useful to graphically locate and specify backups to restore a database to a point-in-time.For detail
There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table I found following code by SQL Expert Fred.
DECLARE @table VARCHAR(128),
— If i need to create CSV file Product table then
SET @table = ‘Production.Product’
SET @file = ‘D:\BCP_OUTPUT\’ + @table + ‘_’ + CONVERT(CHAR(8), GETDATE(), 112)
SET @cmd = ‘bcp “AdventureWorks.’ + @table + ‘” out “‘ + @file + ‘” -S. -T -c -t,’
EXEC master..xp_cmdshell @cmd
Code basically uses BCP to create a CSV file for a given table. I can create a template of above code, and then just load, change values and execute. So simple but still it has a drawback. It creates a CSV file for all rows but WITHOUT column header row. Now how can I import this table later on, without column header row?
Then I found quickest and simplest way to achieve this goal. Simply type select * query but before execution
• Click on Tools — > options and change values for Query Result Output Format and Custom Delimiter (I preferred pipe sign “|”)
• Press Ctrl+Shift+F buttons, so it can save result to file.
SELECT * FROM Production.Product
• On execution, provide file name and your desired path and it’s done
Don’t worry about newly created file extension. When need to import just select it from All Files (*,*).
- How to Create Different Type of Tables
- Table Variables Are Created In Memory Or In Tempdb
- Quickest Way to Create Tables Relationship
- How to Determine All Indexes Fragmentation
- Simple Method to Resolve All Indexes Fragmentation
- 4 Possible Methods to Resolve Index Fragmentation
- Why We Should Avoid COUNT (*)
- 9 Most Forbidden Things
- Which is More Important for Query Optimization, CPU or Logical Reads?
- Performance: Filtered Index a beauty of Sql Server 2008
- Outdated Statistics Can Mislead Query Optimizer
- Bookmark Lookups, a Query Performance Killer
- UNION ALL or UNION
- How to Get Physical Path of Tables and Indexes
- How to Insert Stored Procedure Result Set in a Table
- Function Based Check Constraint
- 3 Methods to Handle NULL for String Concatenation
- Which is the Best Way to Generate Random Number
- ROW_NUMBER () vs. DENSE_RANK ()
If you already have transaction log backup/s taken between your point of problem and Full Back. Restore your full back and then restore rest of your intermediate backups (Differential or Log) BUT all with “RESTORE WITH NORECROVERY” option. At the end restore your transaction log backup which you have created after PROBLEM OCCURRED, with “WITH RECOVERY” option.