Backup and restore SQL database
-
hi all, i am new to SQL Server i want to Create backup of my database and restore it programmatically. i search many example on Google and also on Code project(USING SMO object) but no link fulfill my requirement.Each example give error "Backup failed for Server 'MachineName\InstanceName'. Then i use following stored procedure to create backup
Create PROCEDURE CreateBackupFile @BackUpPath varchar(200) AS BEGIN Backup Database Test to disk = @BackupPath ENDabove procedure works well when i run via VB.net but now the problem with Restore.I use following statementCreate PROCEDURE RestoreBackupFile @RestoreFilePath varchar(200) AS BEGIN RESTORE DATABASE Vehicle FROM DISK = @RestoreFilePath ENDbut when i run this procedure then it give error "Database is use for this session. Use Master' after that i modify above procedure as followingCreate PROCEDURE RestoreBackupFile @RestoreFilePath varchar(200) AS BEGIN Use master Go RESTORE DATABASE Vehicle FROM DISK = @RestoreFilePath ENDbut now one more limitation appear that we can not use the 'Use database' statement within Stored procedure,function and trigger Pleast suggest how can i resolve this problem.Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
hi all, i am new to SQL Server i want to Create backup of my database and restore it programmatically. i search many example on Google and also on Code project(USING SMO object) but no link fulfill my requirement.Each example give error "Backup failed for Server 'MachineName\InstanceName'. Then i use following stored procedure to create backup
Create PROCEDURE CreateBackupFile @BackUpPath varchar(200) AS BEGIN Backup Database Test to disk = @BackupPath ENDabove procedure works well when i run via VB.net but now the problem with Restore.I use following statementCreate PROCEDURE RestoreBackupFile @RestoreFilePath varchar(200) AS BEGIN RESTORE DATABASE Vehicle FROM DISK = @RestoreFilePath ENDbut when i run this procedure then it give error "Database is use for this session. Use Master' after that i modify above procedure as followingCreate PROCEDURE RestoreBackupFile @RestoreFilePath varchar(200) AS BEGIN Use master Go RESTORE DATABASE Vehicle FROM DISK = @RestoreFilePath ENDbut now one more limitation appear that we can not use the 'Use database' statement within Stored procedure,function and trigger Pleast suggest how can i resolve this problem.Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
RestoreBackupFile-procedure cannot be in the database you are restoring (restore command restores the database completely including stored procedures tables etc). You must either create the procedure into another database or call the restore from the program.The need to optimize rises from a bad design
-
RestoreBackupFile-procedure cannot be in the database you are restoring (restore command restores the database completely including stored procedures tables etc). You must either create the procedure into another database or call the restore from the program.The need to optimize rises from a bad design
i tried but now this give the error "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally." i already close all connection to my database. Also i close my IDE. Please suggest.
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
i tried but now this give the error "Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally." i already close all connection to my database. Also i close my IDE. Please suggest.
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
The reason for the error is that some connection is still using your database (you can use Management Studio to find out the connection). If you want to remove the connections from your database, you can try adding
ALTER DATABASE Vehicle SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That closes all connections from the database and rolls back all active transactions.
The need to optimize rises from a bad design
-
The reason for the error is that some connection is still using your database (you can use Management Studio to find out the connection). If you want to remove the connections from your database, you can try adding
ALTER DATABASE Vehicle SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That closes all connections from the database and rolls back all active transactions.
The need to optimize rises from a bad design
Thanks mika for your quick response i resolve the problem by using
ClearAllPoolsprocedure provided by .net. one more thing ,Whether there are any other method for restore. Since this method require one more databse Thanks a LotRupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
Thanks mika for your quick response i resolve the problem by using
ClearAllPoolsprocedure provided by .net. one more thing ,Whether there are any other method for restore. Since this method require one more databse Thanks a LotRupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
For actual restore there is no other method. You need two databases if you want to have a stored procedure which does the restore. If you place the restore command into an application, you won't need another database. Depending what you want to achieve, you can search for alternatives for restoring a database, such as detach/attach database, loading data using SSIS or bcp etc.
The need to optimize rises from a bad design
-
For actual restore there is no other method. You need two databases if you want to have a stored procedure which does the restore. If you place the restore command into an application, you won't need another database. Depending what you want to achieve, you can search for alternatives for restoring a database, such as detach/attach database, loading data using SSIS or bcp etc.
The need to optimize rises from a bad design
Mika Wendelius wrote:
If you place the restore command into an application, you won't need another database.
how can i achieve this thing ?I tried but 'Database is in use' error occured
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
Mika Wendelius wrote:
If you place the restore command into an application, you won't need another database.
how can i achieve this thing ?I tried but 'Database is in use' error occured
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
Using .Net (SqlConnection and SqlCommand classes): - connect to SQL Server - use master database (or connect directly to it) - optionally disconnect other users from your database if there are any using the command from my previous post - execute the restore statement - release the database to multi user state So basically the logic is the same, but it isn't placed inside a stored proc but a calling aplication which doesn't connect to your database (Vehicle) but to master database.
The need to optimize rises from a bad design
-
Using .Net (SqlConnection and SqlCommand classes): - connect to SQL Server - use master database (or connect directly to it) - optionally disconnect other users from your database if there are any using the command from my previous post - execute the restore statement - release the database to multi user state So basically the logic is the same, but it isn't placed inside a stored proc but a calling aplication which doesn't connect to your database (Vehicle) but to master database.
The need to optimize rises from a bad design
hi Mika, Thanks a lot. i am stupid so i create temp database(and not use the master database). Previously i use restore statement in code but with connection object i use the main database. Now i use master database. One more question what is the life time of this type of backup mean 1 day,2 day... (Please see my first post of this Thread for backup procedure) once again Thanks you very much
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
hi Mika, Thanks a lot. i am stupid so i create temp database(and not use the master database). Previously i use restore statement in code but with connection object i use the main database. Now i use master database. One more question what is the life time of this type of backup mean 1 day,2 day... (Please see my first post of this Thread for backup procedure) once again Thanks you very much
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
I don't quite understand what you mean with lifetime? You can take a backup today and restore it back one year later if you want. However you will loose then all changes that have taken place within a year. So a backup file is valid as long as you keep it somewhere safe.
The need to optimize rises from a bad design