Ad:

MS SQL Discussion Thread
Unsolved
Views: 2185

Sep 11th, 2009

MS SQL Permission on IIS 6

Expand Post »

Hello everyone,

i wrote an application that makes database backups and zips them.

Can anyone please tell me what i need to do in order to get the apllication to work on IIS 6 windows server 2003 SQL 05

1. The application works fine on machines for both XP and VISTA.
2. The application runs fine on Win Server 2003 however :

my problem is : obviously the database is in use by the web site.

How can i give my application permission to make the backup while the website is connected to the database ?

regards,

Similar Threads

Which OS do you suggest? in IT Professionals' Lounge
ASP + Microsoft OLE DB Provider for SQL Server error '80004005' in ASP
Urgent Help With a SQL Where statement in MS SQL
how to show OLAP Reports in Crystal Reports in a ASP Page with Drill down option in ASP

cVz

Reputation Points: 29

Solved Threads: 7

Junior Poster

Offline

139 posts
since Mar 2008

Permalink

Sep 11th, 2009

Re: MS SQL Permission on IIS 6

You need to issue a backup command to the SQL Server and then archive the .bak file. You never want to access the .mdf and .ldf files directly.

Use this query:

(Toggle Plain Text)

BACKUP DATABASE [DBName] TO  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BackUpManual\DBName.bak' WITH  INIT ,  NOUNLOAD ,  NAME = N'DBName backup',  NOSKIP ,  STATS = 10,  NOFORMAT

Note: The file path is relative to the machine running the SQL Server, not the client issuing the command

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Sep 11th, 2009

Re: MS SQL Permission on IIS 6

Hi my friend , i have that .

SQL Syntax (Toggle Plain Text)
 /// <summary>
        /// Backups are done here
        /// </summary>
        #region Backup Methods
        #region Full Backup method
        void Fullbackup()
        {
 
            method = "Full";
            // Creating connection
            Username = txtUsername.TEXT;
            Password = txtPassword.TEXT;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.TEXT + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);
 
            // Getting the items that has to be backed up
            foreach (string lstItem IN listBoxControl2.Items)
            {
                dbName = lstItem;
 
                if (!Directory.EXISTS(textEdit1.TEXT + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.TEXT + "/DataSafe Backups/Temp/");
                    newDir.CREATE();
                }
 
                lblStatus.TEXT = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.SmartGetDataSet("BACKUP DATABASE " + dbName + " TO DISK = '" + textEdit1.TEXT + "/DataSafe Backups/Temp/" + dbName + ".bak' WITH NAME = '" + dbName + ".bak'");
 
                ZipFile();
            }
        }
        #endregion
 
        #region Differential Backup method
        void Diffbackup()
        {
            method = "Diff";
            // Creating connection
            Username = txtUsername.TEXT;
            Password = txtPassword.TEXT;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.TEXT + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);
 
            // Getting the items that has to be backed up
            foreach (string lstItem IN listBoxControl2.Items)
            {
                dbName = lstItem;
 
                if (!Directory.EXISTS(textEdit1.TEXT + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.TEXT + "/DataSafe Backups/Temp/");
                    newDir.CREATE();
                }
 
                lblStatus.TEXT = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.SmartGetDataSet("BACKUP DATABASE " + dbName + " TO DISK = '" + textEdit1.TEXT + "/DataSafe Backups/Temp/" + dbName + "_Differential.bak' WITH DIFFERENTIAL, NAME = '" + dbName + ".bak'");
 
                ZipFile();
            }
        }
        #endregion
 
        #region LOG backup method
        void Logbackup()
        {
            method = "Log";
            // Creating connection
            Username = txtUsername.TEXT;
            Password = txtPassword.TEXT;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.TEXT + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);
 
            // Getting the items that has to be backed up
            foreach (string lstItem IN listBoxControl2.Items)
            {
                dbName = lstItem;
 
                if (!Directory.EXISTS(textEdit1.TEXT + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.TEXT + "/DataSafe Backups/Temp/");
                    newDir.CREATE();
                }
 
                lblStatus.TEXT = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.ExecuteCommand("BACKUP LOG  " + dbName + " TO DISK = '" + textEdit1.TEXT + "/DataSafe Backups/Temp/" + dbName + "_LOG.bak' WITH NAME = '" + dbName + ".bak'");
 
                ZipFile();
            }
        }
        #endregion
        #endregion
 /// <summary>
        /// Backups are done here
        /// </summary>
        #region Backup Methods
        #region Full Backup method
        void Fullbackup()
        {

            method = "Full";
            // Creating connection
            Username = txtUsername.Text;
            Password = txtPassword.Text;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.Text + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);

            // Getting the items that has to be backed up
            foreach (string lstItem in listBoxControl2.Items)
            {
                dbName = lstItem;

                if (!Directory.Exists(textEdit1.Text + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.Text + "/DataSafe Backups/Temp/");
                    newDir.Create();
                }

                lblStatus.Text = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.SmartGetDataSet("BACKUP DATABASE " + dbName + " TO DISK = '" + textEdit1.Text + "/DataSafe Backups/Temp/" + dbName + ".bak' WITH NAME = '" + dbName + ".bak'");

                ZipFile();
            }
        }
        #endregion

        #region Differential Backup method
        void Diffbackup()
        {
            method = "Diff";
            // Creating connection
            Username = txtUsername.Text;
            Password = txtPassword.Text;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.Text + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);

            // Getting the items that has to be backed up
            foreach (string lstItem in listBoxControl2.Items)
            {
                dbName = lstItem;

                if (!Directory.Exists(textEdit1.Text + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.Text + "/DataSafe Backups/Temp/");
                    newDir.Create();
                }

                lblStatus.Text = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.SmartGetDataSet("BACKUP DATABASE " + dbName + " TO DISK = '" + textEdit1.Text + "/DataSafe Backups/Temp/" + dbName + "_Differential.bak' WITH DIFFERENTIAL, NAME = '" + dbName + ".bak'");

                ZipFile();
            }
        }
        #endregion

        #region LOG backup method
        void Logbackup()
        {
            method = "Log";
            // Creating connection
            Username = txtUsername.Text;
            Password = txtPassword.Text;
            // specifying connection string
            DBhandler.DbWinHandler dbHandler = new DBhandler.DbWinHandler("Data Source=" + txtServerName.Text + ";Initial Catalog=master;Persist Security Info=True;User ID=" + Username + ";Password=" + Password);

            // Getting the items that has to be backed up
            foreach (string lstItem in listBoxControl2.Items)
            {
                dbName = lstItem;

                if (!Directory.Exists(textEdit1.Text + "/DataSafe Backups/Temp/"))
                {
                    DirectoryInfo newDir = new DirectoryInfo(textEdit1.Text + "/DataSafe Backups/Temp/");
                    newDir.Create();
                }

                lblStatus.Text = "Backing up " + dbName;
                lblStatus.Refresh();
                dbHandler.ExecuteCommand("BACKUP LOG  " + dbName + " TO DISK = '" + textEdit1.Text + "/DataSafe Backups/Temp/" + dbName + "_LOG.bak' WITH NAME = '" + dbName + ".bak'");

                ZipFile();
            }
        }
        #endregion
        #endregion

PS the DBhandler.DbWinHandler is just a class we use to connect to SQL.

should i add any of the following ?

INIT ,
NOUNLOAD
NOSKIP ,
STATS = 10,
NOFORMAT

????

Last edited by cVz; Sep 11th, 2009 at 7:40 am.

cVz

Reputation Points: 29

Solved Threads: 7

Junior Poster

Offline

139 posts
since Mar 2008

Permalink

Sep 11th, 2009

Re: MS SQL Permission on IIS 6

What is the error you receive?

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Sep 11th, 2009

Re: MS SQL Permission on IIS 6

well it just bounces ...

No error...

however the backup file is 0 bytes

cVz

Reputation Points: 29

Solved Threads: 7

Junior Poster

Offline

139 posts
since Mar 2008

Permalink

Sep 11th, 2009

Re: MS SQL Permission on IIS 6

Well try a full backup (using the query I posted) instead of a differential backup. This will indicate if backups are working at all.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL Query Problem

Next Thread in MS SQL Forum Timeline: Simultaneous Data Update - "Record Locking"

DaniWeb Message

Cancel Changes

User Name
Password