Access 直接复制 放在另一个地方要备份 复制回来叫还原MSSQL 1.backup database es to disk='d:\es1209.bak' 2.use master;restore database es from disk = 'd:\es1209.Bak'; Alter database es Set Offline With rollback immediate Alter database es Set Online With Rollback immediate 分别是备份和还原 语句就当做普通的执行语句就行 表名 文件可为变量
BACKUP DATABASE test TO disk = 'c:\backup.bak' WITH FORMATCREATE proc dbo.Data_Backup @dbname sysname='',@bkpath nvarchar(260)='',@bkfname nvarchar(260)='',@bktype nvarchar(10)='DB',@appendfile bit=1 as declare @sql varchar(8000) if isnull(@dbname,'')='' set @dbname=db_name() if isnull(@bkfname,'')='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK' set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname),'\DATE\',convert(varchar,getdate(),112)),'\TIME\',replace(convert(varchar,getdate(),108),':','')) update XTSZ set FNAME=@bkfname set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname +' to disk='''+@bkpath+@bkfname +''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end+case @appendfile when 1 then 'NOINIT' else 'INIT' end exec(@sql)USE master EXEC sp_addumpdevice 'disk', 'MyNwind', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind.dat'
backup database TestDB to disk='F:\Recove\TestDB.bak'//备份restore database TestDB from disk='F:\Recove\TestDB.bak'//还原 备份没问题 可是还原就出现下面这个问题消息 3159,级别 16,状态 1,第 1 行 尚未备份数据库 "TestDB" 的日志尾部。如果该日志包含您不希望丢失的工作,请使用 BACKUP LOG WITH NORECOVERY 备份该日志。请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。 消息 3013,级别 16,状态 1,第 1 行 RESTORE DATABASE 正在异常终止。这个错误如何解决啊?
if (File.Exists(name) == true) { File.Delete(name); } if (boperate.GetExecute(cmd)) { MessageBox.Show("备份成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
备份我是可以成功的,我就是还原的时候出现错误,哪位给我个还原数据库的脚本吧。 我的备份数据库:backup database TDB to disk='F:\Recove\2010TDB.bak' backup log TDB to disk='F:\Recove\2010TDB_Log.bak' with norecovery我的还原数据库:restore database TDB from disk='F:\Recove\2010TDB.bak' restore log TDB from disk='F:\Recove\2010TDB_Log.bak'可是会出现上面的错误,我要的是完全备份
这个就是俺正在用的~~~~ 支持SQL Server 2005/2008 CREATE PROCEDURE [dbo].[px_sys_DatabaseCopy] ( @DestinationDatabaseName nvarchar(200), @SourceDatabaseBackupFolder nvarchar(2000), @DestinationDataFileFolder nvarchar(2000), @DestinationLogFileFolder nvarchar(2000) ) AS BEGIN/* RETURN 0 = Success -1 = Destination database already exist -2 = Backup Source database failed -3 = Restore Desination header failed -4 = Restore Destination database failed */DECLARE @SourceDatabaseName nvarchar(200) SET @SourceDatabaseName = db_name() -- the backup filename DECLARE @SourceDatabaseBackupFile nvarchar(2000) SET @SourceDatabaseBackupFile = replace(@SourceDatabaseBackupFolder+'\'+@SourceDatabaseName+'_'+replace(convert(varchar,getdate(),112)+convert(varchar,getdate(),108), ':', '')+'.bak', '\\', '\')DECLARE @IsSQL2008 BIT SET @IsSQL2008 = 0 IF(SUBSTRING(@@VERSION,1,25))='Microsoft SQL Server 2008' SET @IsSQL2008 = 1 --DECLARE @query varchar(2000)-- Destination DATA FILE DECLARE @DataFile varchar(2000) SET @DataFile = replace(@DestinationDataFileFolder+'\'+@DestinationDatabaseName+ '.mdf', '\\', '\')-- Destination LOG FILE DECLARE @LogFile varchar(2000) SET @LogFile = replace(@DestinationLogFileFolder+'\'+@DestinationDatabaseName+ '.ldf', '\\', '\') -- Don't restore database if destination already exist IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = @DestinationDatabaseName) BEGIN -- SET @query = 'DROP DATABASE ' + @DestinationDatabaseName -- EXEC (@query) RETURN -1 -- Destination database already exists END-- Backup source database IF @SourceDatabaseName IS NOT NULL BEGIN SET @query = 'BACKUP DATABASE [' + @SourceDatabaseName + '] TO DISK = ' + QUOTENAME(@SourceDatabaseBackupFile, '''') EXEC (@query)
if @@ERROR <> 0 BEGIN RETURN -2 -- Backup database failed END ENDCREATE TABLE #restoreheader ( BackupName nvarchar(128) , BackupDescription nvarchar(255) , BackupType smallint , ExpirationDate datetime , Compressed tinyint , Position smallint , DeviceType tinyint , UserName nvarchar(128) , ServerName nvarchar(128) , DatabaseName nvarchar(128) , DatabaseVersion int , DatabaseCreationDate datetime , BackupSize numeric(20,0) , FirstLSN numeric(25,0) , LastLSN numeric(25,0) , CheckpointLSN numeric(25,0) , DatabaseBackupLSN numeric(25,0) , BackupStartDate datetime , BackupFinishDate datetime , SortOrder smallint , CodePage smallint , UnicodeLocaleId int , UnicodeComparisonStyle int , CompatibilityLevel tinyint , SoftwareVendorId int , SoftwareVersionMajor int , SoftwareVersionMinor int , SoftwareVersionBuild int , MachineName nvarchar(128) , Flags int , BindingID uniqueidentifier , RecoveryForkID uniqueidentifier , Collation nvarchar(128) , FamilyGUID uniqueidentifier , HasBulkLoggedData bit , IsSnapshot bit , IsReadOnly bit , IsSingleUser bit , HasBackupChecksums bit , IsDamaged bit , BeginsLogChain bit , HasIncompleteMetaData bit , IsForceOffline bit , IsCopyOnly bit , FirstRecoveryForkID uniqueidentifier , ForkPointLSN numeric(25,0) NULL , RecoveryModel nvarchar(60) , DifferentialBaseLSN numeric(25,0) NULL , DifferentialBaseGUID uniqueidentifier , BackupTypeDescription nvarchar(60) , BackupSetGUID uniqueidentifier NULL ) -- Restore Destination database headers SET @query = 'RESTORE HEADERONLY FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile , '''')IF(@IsSQL2008=1) ALTER TABLE #restoreheader ADD [CompressedBackupSize] bigintINSERT #restoreheader EXEC (@query)IF @@ERROR <> 0 BEGIN GOTO BR_DeleteBackupFile RETURN -3 -- Restore header only failed ENDDECLARE @File int select @File = COUNT(1) from #restoreheaderTRUNCATE TABLE #restoreheader DROP TABLE #restoreheaderDECLARE @Data varchar(500) DECLARE @Log varchar(500)SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile , '''')CREATE TABLE #restoretemp ( LogicalName nvarchar(128) , PhysicalName nvarchar(260) , Type char(1) , FileGroupName nvarchar(128) , Size numeric(20,0) , MaxSize numeric(20,0) , FileID bigint , CreateLSN numeric(25,0) , DropLSN numeric(25,0) NULL , UniqueID uniqueidentifier , ReadOnlyLSN numeric(25,0) NULL , ReadWriteLSN numeric(25,0) NULL , BackupSizeInBytes bigint , SourceBlockSize int , FileGroupID int , LogGroupGUID uniqueidentifier NULL , DifferentialBaseLSN numeric(25,0) NULL , DifferentialBaseGUID uniqueidentifier , IsReadOnly bit , IsPresent bit )IF(@IsSQL2008=1) ALTER TABLE #restoretemp ADD [TDEThumbprint] varbinary(32)INSERT #restoretemp EXEC (@query)SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D' SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'PRINT @Data PRINT @LogTRUNCATE TABLE #restoretemp DROP TABLE #restoretempIF @File > 0 BEGIN SET @query = 'RESTORE DATABASE [' + @DestinationDatabaseName + '] FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile, '''') + ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' + QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File) EXEC (@query) GOTO BR_DeleteBackupFile IF @@ERROR <> 0 BEGIN RETURN -4 -- Restore Destination database failed END ENDBR_DeleteBackupFile: BEGIN DECLARE @deleteStatement nvarchar(2000) BEGIN TRY SET @deleteStatement = 'MASTER.sys.xp_delete_file 0, '+ QUOTENAME(@SourceDatabaseBackupFile) EXEC (@deleteStatement) END TRY BEGIN CATCH -- Delete bak file should not throw any exception, so do nothing here. END CATCH ENDEND
哇,能不能简洁一点啊。 我看备份就是:backup database 名称 to disk='地址' backup log 名称 to disk='地址' 还原就是:restore database 名称 from disk='地址' restore log 名称 from disk='地址' 只是现在出现了上面的问题。我就想解决上面的问题,看看问题出在哪里
BACKUP DATABASE [TDB] TO DISK = N'F:\Recove\2010TDB.bak'里面的"N"是什么意思啊,有什么作用啊
1.backup database es to disk='d:\es1209.bak'
2.use master;restore database es from disk = 'd:\es1209.Bak';
Alter database es Set Offline With rollback immediate
Alter database es Set Online With Rollback immediate
分别是备份和还原 语句就当做普通的执行语句就行 表名 文件可为变量
Alter database es Set Offline With rollback immediate
这句话是起什么作用的啊
希望这个能帮到你
as declare @sql varchar(8000) if isnull(@dbname,'')='' set @dbname=db_name() if isnull(@bkfname,'')=''
set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK' set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname),'\DATE\',convert(varchar,getdate(),112)),'\TIME\',replace(convert(varchar,getdate(),108),':',''))
update XTSZ set FNAME=@bkfname
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname +' to disk='''+@bkpath+@bkfname +''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end+case @appendfile when 1 then 'NOINIT' else 'INIT' end
exec(@sql)USE master
EXEC sp_addumpdevice 'disk', 'MyNwind',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind.dat'
备份没问题
可是还原就出现下面这个问题消息 3159,级别 16,状态 1,第 1 行
尚未备份数据库 "TestDB" 的日志尾部。如果该日志包含您不希望丢失的工作,请使用 BACKUP LOG WITH NORECOVERY 备份该日志。请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。
消息 3013,级别 16,状态 1,第 1 行
RESTORE DATABASE 正在异常终止。这个错误如何解决啊?
消息 3159,级别 16,状态 1,第 1 行
尚未备份数据库 "TestDB" 的日志尾部。如果该日志包含您不希望丢失的工作,请使用 BACKUP LOG WITH NORECOVERY 备份该日志。请使用 RESTORE 语句的 WITH REPLACE 或 WITH STOPAT 子句来只覆盖该日志的内容。
消息 3013,级别 16,状态 1,第 1 行
RESTORE DATABASE 正在异常终止。
这个问题,不知如何解决
{
SqlConnection conn = this.getcon();
SqlCommand cmd = new SqlCommand(cmdtxt, conn);
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show("错误:" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();//关闭连接
conn.Dispose();//释放占用资源
}
}
}
string name = this.txtPlace.Text.Trim() + "\\" + this.txtBakName.Text.Trim() + ".bak";
string cmd = "backup database ranqi to disk='"+name+"' // File.Delete(this.txtPlace.Text.Trim()+"\\"+this.txtBakName.Text.Trim()+".bak");
if (File.Exists(name) == true)
{
File.Delete(name);
}
if (boperate.GetExecute(cmd))
{
MessageBox.Show("备份成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
我的备份数据库:backup database TDB to disk='F:\Recove\2010TDB.bak'
backup log TDB to disk='F:\Recove\2010TDB_Log.bak' with norecovery我的还原数据库:restore database TDB from disk='F:\Recove\2010TDB.bak'
restore log TDB from disk='F:\Recove\2010TDB_Log.bak'可是会出现上面的错误,我要的是完全备份
支持SQL Server 2005/2008
CREATE PROCEDURE [dbo].[px_sys_DatabaseCopy]
(
@DestinationDatabaseName nvarchar(200),
@SourceDatabaseBackupFolder nvarchar(2000),
@DestinationDataFileFolder nvarchar(2000),
@DestinationLogFileFolder nvarchar(2000)
)
AS
BEGIN/*
RETURN
0 = Success
-1 = Destination database already exist
-2 = Backup Source database failed
-3 = Restore Desination header failed
-4 = Restore Destination database failed
*/DECLARE @SourceDatabaseName nvarchar(200)
SET @SourceDatabaseName = db_name()
-- the backup filename
DECLARE @SourceDatabaseBackupFile nvarchar(2000)
SET @SourceDatabaseBackupFile = replace(@SourceDatabaseBackupFolder+'\'+@SourceDatabaseName+'_'+replace(convert(varchar,getdate(),112)+convert(varchar,getdate(),108), ':', '')+'.bak', '\\', '\')DECLARE @IsSQL2008 BIT
SET @IsSQL2008 = 0
IF(SUBSTRING(@@VERSION,1,25))='Microsoft SQL Server 2008'
SET @IsSQL2008 = 1
--DECLARE @query varchar(2000)-- Destination DATA FILE
DECLARE @DataFile varchar(2000)
SET @DataFile = replace(@DestinationDataFileFolder+'\'+@DestinationDatabaseName+ '.mdf', '\\', '\')-- Destination LOG FILE
DECLARE @LogFile varchar(2000)
SET @LogFile = replace(@DestinationLogFileFolder+'\'+@DestinationDatabaseName+ '.ldf', '\\', '\')
-- Don't restore database if destination already exist
IF EXISTS(SELECT * FROM master..sysdatabases WHERE name = @DestinationDatabaseName)
BEGIN
-- SET @query = 'DROP DATABASE ' + @DestinationDatabaseName
-- EXEC (@query)
RETURN -1 -- Destination database already exists
END-- Backup source database
IF @SourceDatabaseName IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE [' + @SourceDatabaseName + '] TO DISK = ' + QUOTENAME(@SourceDatabaseBackupFile, '''')
EXEC (@query)
if @@ERROR <> 0
BEGIN
RETURN -2 -- Backup database failed
END
ENDCREATE TABLE #restoreheader (
BackupName nvarchar(128) ,
BackupDescription nvarchar(255) ,
BackupType smallint ,
ExpirationDate datetime ,
Compressed tinyint ,
Position smallint ,
DeviceType tinyint ,
UserName nvarchar(128) ,
ServerName nvarchar(128) ,
DatabaseName nvarchar(128) ,
DatabaseVersion int ,
DatabaseCreationDate datetime ,
BackupSize numeric(20,0) ,
FirstLSN numeric(25,0) ,
LastLSN numeric(25,0) ,
CheckpointLSN numeric(25,0) ,
DatabaseBackupLSN numeric(25,0) ,
BackupStartDate datetime ,
BackupFinishDate datetime ,
SortOrder smallint ,
CodePage smallint ,
UnicodeLocaleId int ,
UnicodeComparisonStyle int ,
CompatibilityLevel tinyint ,
SoftwareVendorId int ,
SoftwareVersionMajor int ,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(128) ,
Flags int ,
BindingID uniqueidentifier ,
RecoveryForkID uniqueidentifier ,
Collation nvarchar(128) ,
FamilyGUID uniqueidentifier ,
HasBulkLoggedData bit ,
IsSnapshot bit ,
IsReadOnly bit ,
IsSingleUser bit ,
HasBackupChecksums bit ,
IsDamaged bit ,
BeginsLogChain bit ,
HasIncompleteMetaData bit ,
IsForceOffline bit ,
IsCopyOnly bit ,
FirstRecoveryForkID uniqueidentifier ,
ForkPointLSN numeric(25,0) NULL ,
RecoveryModel nvarchar(60) ,
DifferentialBaseLSN numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
BackupTypeDescription nvarchar(60) ,
BackupSetGUID uniqueidentifier NULL
)
-- Restore Destination database headers
SET @query = 'RESTORE HEADERONLY FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile , '''')IF(@IsSQL2008=1)
ALTER TABLE #restoreheader ADD [CompressedBackupSize] bigintINSERT #restoreheader EXEC (@query)IF @@ERROR <> 0
BEGIN
GOTO BR_DeleteBackupFile
RETURN -3 -- Restore header only failed
ENDDECLARE @File int
select @File = COUNT(1) from #restoreheaderTRUNCATE TABLE #restoreheader
DROP TABLE #restoreheaderDECLARE @Data varchar(500)
DECLARE @Log varchar(500)SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile , '''')CREATE TABLE #restoretemp (
LogicalName nvarchar(128) ,
PhysicalName nvarchar(260) ,
Type char(1) ,
FileGroupName nvarchar(128) ,
Size numeric(20,0) ,
MaxSize numeric(20,0) ,
FileID bigint ,
CreateLSN numeric(25,0) ,
DropLSN numeric(25,0) NULL ,
UniqueID uniqueidentifier ,
ReadOnlyLSN numeric(25,0) NULL ,
ReadWriteLSN numeric(25,0) NULL ,
BackupSizeInBytes bigint ,
SourceBlockSize int ,
FileGroupID int ,
LogGroupGUID uniqueidentifier NULL ,
DifferentialBaseLSN numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
IsReadOnly bit ,
IsPresent bit
)IF(@IsSQL2008=1)
ALTER TABLE #restoretemp ADD [TDEThumbprint] varbinary(32)INSERT #restoretemp EXEC (@query)SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'PRINT @Data
PRINT @LogTRUNCATE TABLE #restoretemp
DROP TABLE #restoretempIF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE [' + @DestinationDatabaseName + '] FROM DISK = ' + QUOTENAME(@SourceDatabaseBackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
GOTO BR_DeleteBackupFile
IF @@ERROR <> 0
BEGIN
RETURN -4 -- Restore Destination database failed
END
ENDBR_DeleteBackupFile:
BEGIN
DECLARE @deleteStatement nvarchar(2000)
BEGIN TRY
SET @deleteStatement = 'MASTER.sys.xp_delete_file 0, '+ QUOTENAME(@SourceDatabaseBackupFile)
EXEC (@deleteStatement)
END TRY
BEGIN CATCH
-- Delete bak file should not throw any exception, so do nothing here.
END CATCH
ENDEND
我看备份就是:backup database 名称 to disk='地址'
backup log 名称 to disk='地址'
还原就是:restore database 名称 from disk='地址'
restore log 名称 from disk='地址'
只是现在出现了上面的问题。我就想解决上面的问题,看看问题出在哪里
BACKUP DATABASE [TDB] TO DISK = N'F:\Recove\2010TDB.bak'里面的"N"是什么意思啊,有什么作用啊