仅备份SQL数据，以便在对数据库进行更改时仍可导入数据。 [英] Backup SQL data only so that when alterations are made to database, data can still be imported.

问题描述

大家好，



这可能是一个愚蠢的问题，但我找不到答案。我想知道如何仅备份SQL数据，以便当我向我的数据库添加列，表等时，我可以简单地备份数据，添加新数据库并导入旧数据而不会出现问题。我知道旧数据不会有所有的值，比方说我有以下数据库：



table = clients

columns =姓名，号码



这些都有价值。



现在我添加了column = surname。



我想将名称和编号导入新表=具有列=名称，姓氏，编号的客户端。留下姓氏空白。



到目前为止，每次我备份我的数据库时，整个数据库都会被备份，所以当恢复它时，复制我的新数据库会导致我丢失我的新桌子和柱子。



请有人向我解释这是如何工作的？



这个是我如何备份和恢复我的数据库：

Hi All,

This may be a dumb question, but I just cannot find an answer. I would like to know how to backup SQL data only so that when I add columns, tables, etc. to my database I can simply backup the data, add the new database and import the old data without problem. I know the old data will not have all the values like, lets say I have the following database:

table = clients
columns = name, number

These have values.

Now I add column = surname.

I want to import name and number to the new table = Clients with columns = name, surname, number. Leaving surname blank ofcourse.

So far, every time I backup my database, the entire database is backed up, so when restoring it copies over my new database causing me to lose my new tables and columns.

Please can someone explain to me how this works?

This is how I backup and restore my database:

public void BackupEntireSqlDatabaseToDisk(string fileName)
        {
            using (SqlConnection Conn = new SqlConnection(Connect.sConnStr))
            {
                Conn.Open();

                string database = Conn.Database.ToString();
                string sSql = string.Format("BACKUP DATABASE [" + database + "] TO DISK='{0}' WITH FORMAT", fileName);

                using (SqlCommand cmd = new SqlCommand(sSql, Conn))
                {
                    cmd.ExecuteNonQuery();
                }

                Conn.Close();
                Conn.Dispose();
            }
        }

        public void RestoreEntireSqlDatabaseToEzyGym(string fileName)
        {
            using (SqlConnection Conn = new SqlConnection(Connect.sConnStr))
            {
                Conn.Open();

                string database = Conn.Database.ToString();
                string UseMaster = "USE master";
                SqlCommand UseMasterCommand = new SqlCommand(UseMaster, Conn);
                UseMasterCommand.ExecuteNonQuery();

                string Alter1 = @"ALTER DATABASE [" + database + "] SET Single_User WITH Rollback Immediate";
                SqlCommand Alter1Cmd = new SqlCommand(Alter1, Conn);
                Alter1Cmd.ExecuteNonQuery();

                string Restore = string.Format("Restore database [" + database + "] from disk='{0}'", fileName);

                SqlCommand RestoreCmd = new SqlCommand(Restore, Conn);
                RestoreCmd.ExecuteNonQuery();

                string Alter2 = @"ALTER DATABASE [" + database + "] SET Multi_User";
                SqlCommand Alter2Cmd = new SqlCommand(Alter2, Conn);
                Alter2Cmd.ExecuteNonQuery();

                Conn.Close();
                Conn.Dispose();
            }
        }

问候，

克里斯



我尝试了什么：



我附上了我的代码供你查看我试过的内容。

Regards,
Chris

What I have tried:

I have attached my code for you to see what I tried.

推荐答案

简短的回答，你不能对只包含数据的数据库进行备份。



稍微长一点的答案，您可以使用SQL Server Management Studio（SSMS）将数据库数据编写脚本。

打开SSMS

右键单击数据库&选择任务>>生成脚本

在选择对象页面上选择选项选择特定数据库对象并勾选表 b>选项

单击下一步，直至进入设置脚本选项页面

单击高级按钮

在常规选项部分中，选择仅限数据选项要编写脚本的数据类型



这将生成所有数据库数据的脚本，但它是数据的哑本脚，因为它包含每个数据行的Insert语句。



希望这有帮助

Short answer, you cannot perform a backup of the database that contains just the data.

Slightly longer answer, you can script the database data to a file using SQL Server Management Studio (SSMS).
Open SSMS
Right-click the database & select Tasks >> Generate Scripts
On the Choose Objects page select the option Select specific database objects and the tick the Tables option
Click Next until you get to the Set Scripting Options page
Click the Advanced button
In the General Options section select Data only for the option Types of data to script

This will generate a script of all of the database data but it is a dumb script of the data in that it contains an Insert Statement for each data row.

Hope this helps

这篇关于仅备份SQL数据，以便在对数据库进行更改时仍可导入数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！