本文目录
sql2008数据库备份命令，和截断命令，能解释更清楚的有加分
sqlserver备份两种方式1. 通过维护计划2. 通过job运行命令--差异备份EXECUTE master.dbo.xp_create_subdir N’D:\pic\RE\TEST’GOBACKUP DATABASE TO DISK = N’D:\pic\RE\TEST\TEST_backup_201101051801.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N’TEST_backup_20110105180152’, SKIP, REWIND, NOUNLOAD, STATS = 10go--完整备份BACKUP DATABASE TO DISK = N’D:\pic\RE\TEST_backup_201101051802.bak’ WITH NOFORMAT, NOINIT, NAME = N’TEST_backup_20110105180256’, SKIP, REWIND, NOUNLOAD, STATS = 10go--强制还原--REPLACE覆盖已存在数据库RESTORE DATABASE TEST FROM DISK = ’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\TEST.BAK’ WITH MOVE ’TEST’ TO ’D:\pic\TEST.mdf’, MOVE ’TEST_log’ TO ’D:\pic\TEST_log.ldf’, STATS = 10, REPLACEGO--显示备份列表RESTORE FILELISTONLY FROM DISK = ’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\TEST.BAK’go生成年月日的备份文件declare @databasename nvarchar(50)set @databasename = ’ExtDB’DECLARE @strPath NVARCHAR(200)set @strPath = convert(NVARCHAR(19),getdate(),120)set @strPath = REPLACE(@strPath, ’:’ , ’.’)set @strPath = ’E:\工作目录\bk\’ + @databasename+@strPath + ’.bak’BACKUP DATABASE @databasename TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
如何远程备份SQL SERVER数据库
手头上只有C#的代码，不知道对你有没有帮助远程备份要注意权限的问题，普通的访问帐户没有备份和还原的权限，最好用sa的帐户，并且程序访问的帐户必须添加到远程机器的系统内你说的远程备份到本地是不可能的，你只有开放远程某个文件夹加入你的特殊访问帐户权限最好直接用远程桌面，这样最方便方法一（不使用SQLDMO）：//////备份方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;");SqlCommand cmdBK = new SqlCommand();cmdBK.CommandType = CommandType.Text;cmdBK.Connection = conn;cmdBK.CommandText = @"backup database test to disk=’C:\ba’ with init";try{conn.Open();cmdBK.ExecuteNonQuery();MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();conn.Dispose();}//////还原方法///SqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");conn.Open();//KILL DataBase ProcessSqlCommand cmd = new SqlCommand("SELECTspidFROMsysprocesses ,sysdatabases WHEREsysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name=’test’", conn);SqlDataReader dr;dr = cmd.ExecuteReader();ArrayList list = new ArrayList();while(dr.Read()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i 《 list.Count; i++){cmd = new SqlCommand(string.Format("KILL {0}", list), conn);cmd.ExecuteNonQuery();}SqlCommand cmdRT = new SqlCommand();cmdRT.CommandType = CommandType.Text;cmdRT.Connection = conn;cmdRT.CommandText = @"restore database test fromdisk=’C:\ba’";try{cmdRT.ExecuteNonQuery();MessageBox.Show("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{conn.Close();}方法二（使用SQLDMO）：//////备份方法///SQLDMO.Backup backup = new SQLDMO.BackupClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.BackupSink_PercentCompleteEventHandler progress = new SQLDMO.BackupSink_PercentCompleteEventHandler(Step);backup.PercentComplete += progress;try{server.LoginSecure = false;server.Connect(".", "sa", "sa");backup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;backup.Database = "test";backup.Files = @"D:\test\myProg\backupTest";backup.BackupSetName = "test";backup.BackupSetDescription = "Backup the database of test";backup.Initialize = true;backup.SQLBackup(server);MessageBox.Show("Backup successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0;//////还原方法///SQLDMO.Restore restore = new SQLDMO.RestoreClass();SQLDMO.SQLServer server = new SQLDMO.SQLServerClass();//显示进度条SQLDMO.RestoreSink_PercentCompleteEventHandler progress = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);restore.PercentComplete += progress;//KILL DataBase ProcessSqlConnection conn = new SqlConnection("Server=.;Database=master;User ID=sa;Password=sa;Trusted_Connection=False");conn.Open();SqlCommand cmd = new SqlCommand("SELECTspid FROMsysprocesses ,sysdatabases WHEREsysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name=’test’", conn);SqlDataReader dr;dr = cmd.ExecuteReader();ArrayList list = new ArrayList();while(dr.Read()){list.Add(dr.GetInt16(0));}dr.Close();for(int i = 0; i 《 list.Count; i++){cmd = new SqlCommand(string.Format("KILL {0}", list), conn);cmd.ExecuteNonQuery();}conn.Close();try{server.LoginSecure = false;server.Connect(".", "sa", "sa");restore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;restore.Database = "test";restore.Files = @"D:\test\myProg\backupTest";restore.FileNumber = 1;restore.ReplaceDatabase = true;restore.SQLRestore(server);MessageBox.Show("Restore successed.");}catch(Exception ex){MessageBox.Show(ex.Message);}finally{server.DisConnect();}this.pbDB.Value = 0;
Mssql 数据库 命令怎么备份
SQL Server 整库备份E:\》sqlcmd -S "localhost\SQLEXPRESS"1》 use master2》 go已将数据库上下文更改为 ’master’。备份1》 backup database test to disk=’e:\test.dat’2》 go恢复1》 restore database test from disk=’e:\test.dat’2》 go已为数据库 ’test’，文件 ’Test’ (位于文件 1 上)处理了 232 页。已为数据库 ’test’，文件 ’Test_log’ (位于文件 1 上)处理了 2 页。RESTORE DATABASE 成功处理了 234 页，花费 0.184 秒(9.898 MB/秒)。
sql 数据库备份与还原命令时什么怎么用急!!!
1.备份数据库 BAKUP DATABASE db_name TO devicewith BLockSize= Description=’db_name数据库备份’ Differential, ExpireDate= RetainDays= Password= Format|Noformat MediaName= MediaDescription= MediaPassword= Name= Init|Noinit Skip|NoSkip Unload|NoUnload NoRewind|Rewind Restart Stats=2.备份数据事务日志 BAKUP LOG db_name TO devicewith BLockSize= Description=’db_name数据库事务日志备份’ ExpireDate= RetainDays= Password= Format|Noformat MediaName= MediaDescription= MediaPassword= Name= Init|Noinit Skip|NoSkip Unload|NoUnload NoRewind|Rewind NoRecovery|Standby= Notruncate(清除事务日志的非活动部分，但SQL Server记录Bakuo Log命令)|NO_LOG(清除事务日志的非活动部分，不记录Bakup Log命令)|TruncatePOnly(备份事务日志) Restart Stats=3.恢复数据库 Restore Database db_name From device With Restricted_User File=恢复具有多个备份子集的备份介质中的那个备份子集 Pssword= MediaName= MediaPassword= Keep_Replication Move file to file Norecovery|Recovery|Standby=数据库恢复操作的UNDO文件(恢复数据库文件或文件组使用Norecovery选项) Norewind|Rewind Unload|NoUnload Replace (若恢复的数据库名称与已存在的数据库重名，则首先删除原数据库，然后重建) Restart Stats=4.恢复事务日志 Restore Log db_name From device With Restricted_User File=恢复具有多个备份子集的备份介质中的那个备份子集 Pssword= MediaName= MediaPassword= Move file to file Norecovery|Recovery|Standby=数据库恢复操作的UNDO文件 Norewind|Rewind Unload|NoUnload Restart Stats= Stopat=（将数据库恢复到该设定时间以前的状态，只有在该时间以前的事物日志才会被恢复） Stopatmark=’mark_name’ Stopbeforemark=’mark_name’DOB_ONLY:表示新恢复的数据库的访问权限只授予数据库所有者Norecovery:表示恢复操作不回滚任何未提交的事务。若恢复某一数据库备份后又恢复多个事务日志，或在恢复过程中执行多个Restore命令，则要求除最后一条Restore命令外其他的必须使用该选项Recovery:表示回滚所有未提交事务5.部分恢复数据库---进行部分数据库恢复操作事要使用数据库文件组，而且总是要恢复主要的文件和文件组，此外就是恢复所指定的文件和文件组 Restore Database db_name 《file or filegroup》 From device 《备份设备名》 With Partial Restricted_User File=恢复具有多个备份子集的备份介质中的那个备份子集 Pssword= MediaName= MediaPassword= Keep_Replication Move file to file Norecovery|Recovery|Standby=数据库恢复操作的UNDO文件(恢复数据库文件或文件组使用Norecovery选项) Norewind|Rewind Unload|NoUnload Restart Stats=
