And the created backup files will be automatically named as database name plus date.ĭECLARE VARCHAR(256) - path for backup filesĭECLARE VARCHAR(256) - filename for backupĭECLARE VARCHAR(20) - used for file name With it, you don’t need to specify the database you want to backup, just exclude those ones you don’t want to backup (such as system databases). Script to backup all databases of an instanceĪn appropriate T-SQL script can be a great relief when you need to backup all databases of SQL Server. SELECT = (SELECT 'filepath\databasename' + convert(varchar(500), GetDate(),112) + '.bak')īACKUP DATABASE databasename TO DISK = in your own backup path and database name, you will get a bak file with date. Here’s an example of SQL Server backup script with date: Sometimes you may want to back up a database frequently and keep different versions for recovery needs, and it would be very convenient if the bak file name can be composed of database name and backup date. Script to backup SQL Server database with date Make a transaction-log backup of database:įor database recovery, you could refer to restore SQL database from bak file. *Creating a differential backup requires a previous full backup.
Basic database backup script in SQL Serverīefore moving on to more complex scripts, let's take a glance at the basic DATABASE BACKUP commands, which is the foundation to backup SQL Server with script.Ĭonnect to your server, click New Query to open the SQLQuery window, and enter the command to Execute it.
In the following article, I will share the SQL Server backup scripts I use to perform basic database backup, and some more advanced tasks. including date/time in file names), then T-SQL can be a life saver to process repetitive tasks. But if you have tons of databases in SQL Server, or have specific requirements for backup results (e.g. It may not be a bad idea to back up one or two databases with SSMS GUI. Advantages of backing up SQL Server with T-SQL script
0 kommentar(er)
